HLOOKUP last match in a table

[bodhi2.71828]

In Excel, let's say I have the following in cells A1:B5:

ABC, ABC, ABC, XYZ, XYZ
12, 34, 56, 78, 90

I'm trying to create a formula that will find the last "ABC" in the
table, then return the number beneath it (in this case, 56). If I use
HLOOKUP("ABC", A1:B5, 2, TRUE), it seems to work, while if I change the
last paramter to FALSE it finds the first match. But the final
parameter for HLOOKUP -- boolean Range_Lookup -- is supposed to be
whether it's looking for an exact match or closest match. It doesn't
seem to have anything to do with finding the first or last match, so
while using it seems to work, it makes me nervous because I don't
understand why it works. Is there a better way to do this or can
someone tell me why using TRUE instead of FALSE for the final parameter
finds the last match? I'm using Excel 2000. Thanks.

 

[Aladin Akyurek]

=LOOKUP(2,1/(A1:A5="ABC"),B1:B5)

 

[Domenic]

Assuming that meant...

A1:E1 contain ABC, ABC, ABC, XYZ, XYZ

A2:E2 contain 12, 34, 56, 78, 90

....try the following...

=LOOKUP(2,1/(A1:E1="ABC"),A2:E2)

Hope this helps!

 

[Peo Sjoblom]

This will work entered with ctrl + shift & enter

=INDEX(A1:E5,2,MAX((INDEX(A1:E5,1,)="ABC")*COLUMN(A1:E5)))

of course if you know you always will get the result from secon row there is
no need for INDEX

=INDEX(A2:E2,MAX((A1:E1="ABC")*COLUMN(A1:E1)))