How do I lookup and return a most recent value?

[mark.simpson.3572]

Hello,

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.

Regards,

Mark

[mariosv]

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

But a formula like:
B6: =IFERROR(OFFSET(B1;SUMPRODUCT(MAX(ROW(A1:A5)*(A1:A5=A6)))-1;0);0)
seems to work fine.