find LAST match in column
 

Hi Domenic and Aladin,

the lookop solution worked but indeed not completely.
The

=INDEX(B1:B1000,MAX(IF(A1:A1000="Jack",ROW(A1:A100 0))))

gives a parameter list error.

Both column A and B contain empty cells. Could that be the problem?

Any other ideas maybe?

Aladin Akyurek Wrote:
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



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

Pantryman Wrote:
Hi Domenic and Aladin,

the lookop solution worked but indeed not completely.
The

=INDEX(B1:B1000,MAX(IF(A1:A1000="Jack",ROW(A1:A100 0))))

gives a parameter list error.

Both column A and B contain empty cells. Could that be the problem?

Any other ideas maybe?

I'm not sure why you're getting that error, but make sure that you
enter the formula using CONTROL+SHIFT+ENTER and not just ENTER...

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

Having said that, I would use the following formula instead that needs
to be entered using just ENTER...

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

As Harlan has already pointed out, the formula is more efficient.


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