Lookup last value

[Madiya]

I have a list which gets updated on daily basis.
It means that the name will appear on the sheet daily and it keeps
repeating.

When I use standerd lookup, it retrives the value of the first match.

I want to lookup the name and retrive the latest value.

Regards,
Madiya

 

[JP]

Check out this page:
http://www.cpearson.com/excel/lookups.htm


HTH,
JP

 

[Guest]

Hi,

From your description it's not clear what your trying to do but this may help

=INDEX(A1:B20,SMALL(IF(A1:B20=C1,ROW(A1:B20)-ROW(A1)+1,ROW(B20)+1),COUNTIF(A1:A20,C1)),2)

Takes the value in C1 and finds the last instance of that value in the range
A1:A20 and returns the corresponding value from column B.

It's an array so enter with Ctrl+Shift+Enter

Mike

Mike

 

[Don Guillett]

Try this ARRAY formula which must be entered using ctrl+shift+enter
=MAX(IF(A1:A21="aa",B1:B21))

 

[Madiya]

Try this ARRAY formula which must be entered using ctrl+shift+enter
=MAX(IF(A1:A21="aa",B1:B21))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software








- Show quoted text -

Thank you all for your help.

JP and Mike,
Your solution works for fixed no of rows.
My data keeps updating and no of rows also will go on increasing.
Hance I need to adjust your formula for dynamic rows.
will need your help on this as still I am not proficient in such
complex formulas.

Don,
Your formula return the old value and not the latest values.
I want exactly opposit. I will try MIN instead of MAX in your formula
and post back.

Regards,
Madiya

 

[Don Guillett]

IF? you "latest value" is at the bottom (or after earlier entries), my
formula, IF ARRAY ENTERED, should work.

 

[Bernd P]

Hello,

If your names are in column A and the values in B:
=LOOKUP(2,1/("lookupname"=A1:A999),B1:B999)

Regards,
Bernd

 

[Don Guillett]

My mistake. I misunderstood that you wanted the maximum in B if A ="aa"

Use this ARRAY instead
=INDEX(B1:B20,MAX(IF(A1:A20="aa",ROW(A1:A20))))

 

[Guest]

Hi,

Extend the range like this. It doesn't matter if it's querying empty rows.

=INDEX(A1:B65535,SMALL(IF(A1:B65535=C1,ROW(A1:B65535)-ROW(A1)+1,ROW(B65535)+1),COUNTIF(A1:A65535,C1)),2)

Mike