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

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


HTH,
JP

On Nov 2, 7:02 am, Madiya wrote:
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

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

"Madiya" wrote:

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

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Madiya" wrote in message
ups.com...
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

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))))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Madiya" wrote in message
ups.com...
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

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

Hello,

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

Regards,
Bernd