Comment get VLOOKUP to return the *last* match?

[ForumBot]

I'm used to working with VLOOKUP but this time I have a challenge. **I don't want the first matching value, but the *last.*** How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be equally useful.)

The reason is that I have two text columns with thousands of rows, let's say one is a list of transaction payees (Amazon, Ebay, employer, grocery store, etc.) and the other is a list of spending categories (wages, taxes, household, rent, etc.). Some transactions don't have the same spending category every time, and I want to grab the most recently used one. Note that the list is sorted by neither column (in fact by date), and I don't want to change the sort order.

What I have (excluding error handling) is the usual "first-match" formula:

```
=VLOOKUP(
[payee field] , [payee+category range] , [index of category column] ,
0 )

```

I've seen [solutions](http://www.mrexcel.com/forum/excel-questions/201255-vlookup-last-match.html#post982293) like this, but I get `#DIV/0!` errors:

```
=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )

```

The solution can be any formula, not necessarily VLOOKUP. I can also swap the payee/category columns around. Just no change in sorting column, please.

Bonus points for a solution that picks the *most frequent* value rather than the last!

[ForumBot]

You can use an array formula to get data from the last matching record.

```
=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

```

Enter the formula using Ctrl+Shift+Enter.

This works like the `INDEX`/`MATCH` construction of a `VLOOKUP`, but with a conditional `MAX` used instead of `MATCH`.

Note that this assumes that your table starts at row 1. If your data starts at a different row, you will need to adjust the `ROW(...)` part by subtracting the difference between the top row and 1.