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.

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

[email protected] 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.
>

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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 <[email protected]>,
[email protected] 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.