CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookup
Tony
Tony

Reputation: 115

vlookup most recent entry/last row where it finds value

I'm trying to vlookup data from a sheet with two tabs. I know the vlookup will fetch the results from the first search. But desired output, it should look up the value and get the last result.

current vlookup results: Current vlookup results

Expected output

Here is the trix for reference of sample data

https://docs.google.com/spreadsheets/d/1WkokpBX59qz33oSbKIKNQ7kNkv4QbNlKUKeBYlK4vy8/edit#gid=0

Any inputs on this is much appreciated.

Thanks!

Upvotes: 0

Views: 1178

Answers (2)

z..
z..

Reputation: 12903

You could also use XLOOKUP with search_mode set to -1

=ArrayFormula(xlookup(A12:A14,Sheet2!A2:A,Sheet2!B2:B,,,-1))

Upvotes: 2

doubleunary
doubleunary

Reputation: 18784

Use sort() and row() to order the data into reverse order, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      A2:A10, 
      sort(Sheet2!A1:B, row(Sheet2!A1:B), false), 
      columns(Sheet2!A1:B), 
      false 
    ) 
  ) 
)

Upvotes: 1

Related Questions