HLOOKUP last match in a table
 

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.

HLOOKUP last match in a table
 

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

wrote:
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.

HLOOKUP last match in a table
 

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!

In article .com,
wrote:

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.

HLOOKUP last match in a table
 

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)))
--

Regards,

Peo Sjoblom

wrote in message
oups.com...
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.