Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Comment get VLOOKUP to return the last match?

ayi

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 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!

ayi

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.