look up with duplicates

[sabilabry]

hi everyone,

i need to look up for the last data that match with my look up value but the problem is it has duplicates.. it goes something like this.

Lets say that A1:B7 houses the sample data:

A B
400 HE
23 MS
98 PS
450 HE
89 HE
300 PS

my look up value lets say is "HE",.. i want the result to be "89"..

Pls help me for the solution.. thanks alot..Godbless..

Attention Mr. Aladin,
would you mind giving me your email address or if you can allow me to ask you directly? thanks.

 

[T. Valko]

hi everyone,

i need to look up for the last data that match with my look up value but the problem is it has duplicates.. it goes something like this.

Lets say that A1:B7 houses the sample data:

A B
400 HE
23 MS
98 PS
450 HE
89 HE
300 PS

my look up value lets say is "HE",.. i want the result to be "89"..

Pls help me for the solution.. thanks alot..Godbless..

Try one of these...

=LOOKUP(2,1/(B1:B6="HE"),A1:A6)

This one is an array formula**:

=INDEX(A1:A6,MATCH(2,IF(B1:B6="HE",1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Marcelo Branco]

Maybe an array-formula like

=INDEX($A$1:$A$100,MAX(IF($B$1:$B$100="HE",ROW($B$1:$B$100)-ROW($B$1)+1)))

confirmed with Ctrl+Shift+Enter

M.

 

[sabilabry]

Thanks alot Biff!

works perfectly..

 

[T. Valko]

Thanks alot Biff!

works perfectly..

You're welcome. Thanks for the feedback!