Harlan Grove Wrote:
"Domenic" wrote...
=INDEX(B:B,MAX(IF(A1:A1000="Jack",ROW(A1:A1000))) )

..entered using CONTROL+SHIFT+ENTER.

Be more efficient to use

=LOOKUP(1,1/(A1:A100="Bob"),B1:B100)

Yes, definitely! Thanks for the reminder, Harlan!

Although, wouldn't 2 serve better as the lookup value?

For example, take the following table...

Bob a
Jack b
Jane c
Bob d
Jill e
Phil f
Bob g
Jason h

If you use...

=LOOKUP(1,1/(A1:A100="Bob"),B1:B100)

...the correct result is returned, that being "g".

However, if you use...

=LOOKUP(1,1/(A1:A8="Bob"),B1:B8)

...an incorrect result is returned, that being "d".

Using 2 as the lookup value returns the correct result regardless of
the range one uses.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=275479