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:B655 35)-ROW(A1)+1,ROW(B65535)+1),COUNTIF(A1:A65535,C1)),2)

Mike

"Madiya" wrote:

On Nov 2, 5:45 pm, "Don Guillett" wrote:
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
"Madiya" wrote in message

oups.com...



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- Hide quoted text -

- 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