Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Pantryman
 
Posts: n/a
Default 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

  #2   Report Post  
Domenic
 
Posts: n/a
Default


=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

  #3   Report Post  
Harlan Grove
 
Posts: n/a
Default

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


  #4   Report Post  
Domenic
 
Posts: n/a
Default


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

  #5   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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



  #6   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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



  #7   Report Post  
Adam
 
Posts: n/a
Default

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.

  #8   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find rows for unique data in 1 column and different data in other. Dot Majewski Excel Discussion (Misc queries) 1 January 21st 05 01:23 AM
How do I find the first value in a column less than a number? redeucer Excel Worksheet Functions 6 November 4th 04 10:59 PM
Find AVG/MIN of a Column, excluding 0's and NULL's? JT Excel Worksheet Functions 3 November 4th 04 07:15 PM
find and match the max buffyslay Excel Worksheet Functions 1 November 2nd 04 01:20 PM
Use MATCH to find position of max in 2D range? Peter B Excel Worksheet Functions 4 October 28th 04 05:23 PM


All times are GMT +1. The time now is 08:34 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"