Ask Your Question
0

How do I lookup and return a most recent value?

asked 2015-03-20 00:35:13 +0100

mark.simpson.3572 gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-03-20 02:59:02 +0100

m.a.riosv gravatar image

updated 2015-03-20 03:23:49 +0100

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-20 00:35:13 +0100

Seen: 605 times

Last updated: Mar 20 '15