vlookup most recent entry/last row where it finds value-CodePudding

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:

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!

CodePudding user response：

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

CodePudding user response：

You could also use XLOOKUP with search_mode set to -1

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