How do I lookup and return a most recent value?


I am working on a spreadsheet in which I need a formula to return the most recent value in a row from a lookup.

I have had the formula working whilst I have been using the lookup function using Microsoft Excel but when i have transferred my file over to Libre the formula’s no longer work.

The excel formula I have been using is LOOKUP(2,1/($A$1:A5=A6),$B$1:B5)).

Sheet as follows:

A1 = A B1 = 10
A2 = B B2 = 20
A3 = A B3 = 15
A4 = C B4 = 12
A5 = D B5 = 30
A6 = ???

Cell B6 should be able to return a value of 15 and does so using the above formula in excel but using Libre I get #value. I can only seem to get the Libre LOOKUP function to return a value of 10 or #value.

Can you please tell me how I get Libre to perform the same function as excel can.



I think you can find useful information in this link of Convert Excel Array Formula to ods

But a formula like:
seems to work fine.