find LAST match in column
 

Who knows how I can lookup the LAST match in a column?

i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names
repeated.
If I want to find the last mention of 'Bob' and then get the value of
the cell next to it, how can I do that?
The first match is no problem, but I can't figure out the last.

Thanks muchly,

Marinus.


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

=INDEX(B:B,MAX(IF(A1:A1000="Jack",ROW(A1:A1000))))

...entered using CONTROL+SHIFT+ENTER.

Hope this helps!


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

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

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

Assuming that A1:A1000 is a formula-free range...

=LOOKUP(REPT("z",255),A1:A1000,B1:B1000)

Pantryman Wrote:
Who knows how I can lookup the LAST match in a column?

i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names
repeated.
If I want to find the last mention of 'Bob' and then get the value of
the cell next to it, how can I do that?
The first match is no problem, but I can't figure out the last.

Thanks muchly,

Marinus.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275479

Disregard this for it's not a conditional lookup as you require.

"Aladin Akyurek" wrote in
message ...

Assuming that A1:A1000 is a formula-free range...

=LOOKUP(REPT("z",255),A1:A1000,B1:B1000)

Pantryman Wrote:
Who knows how I can lookup the LAST match in a column?

i.e., in A1:A1000 there's 1000 names, unsorted, with multiple names
repeated.
If I want to find the last mention of 'Bob' and then get the value of
the cell next to it, how can I do that?
The first match is no problem, but I can't figure out the last.

Thanks muchly,

Marinus.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=275479

Hi Domenic,

I just saw this correspondence and I wonder if you could explain the formula
because i do not understand how it can return the right value (even though it
does!)
For instance, how do the formula know that the input 1/A1:A1000<"" refers
to the last cell?

Thanks
Adam

"Domenic" skrev:

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.

The formula extends a formula for finding the (position of) last numeric
value in a range. The extension is due to Harlan Grove. How it works is
explained he

http://tinyurl.com/7ysq5

Adam wrote:
Hi Domenic,

I just saw this correspondence and I wonder if you could explain the formula
because i do not understand how it can return the right value (even though it
does!)
For instance, how do the formula know that the input 1/A1:A1000<"" refers
to the last cell?

Thanks
Adam

"Domenic" skrev:

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.