Finding the last match in a list

R

rowett

Board Regular
Joined
Jun 10, 2002
Messages
116
I want to be able to find the last match in a list. To find the first match I use the MATCH worksheet function. For example MATCH("Chris", A1:A5, 0) finds the index of the first cell in the range A1:A5 containing the text "Chris".

How do I find the LAST cell in the range that contains "Chris"?

Many thanks,
Chris
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
On 2002-06-11 09:56, rowett wrote:
I want to be able to find the last match in a list. To find the first match I use the MATCH worksheet function. For example MATCH("Chris", A1:A5, 0) finds the index of the first cell in the range A1:A5 containing the text "Chris".

How do I find the LAST cell in the range that contains "Chris"?

Many thanks,
Chris
Click to expand...

=SUMPRODUCT(MAX((A1:A5="Chris")*(ROW(A1:A5))))
 
Upvote 0
I found a straightforward solution as an alternative that may work for you.

MY OLD WAY: MATCH(DATE(2011, 5, 5), <myDataRange>, 0)
Well, I usually default the last parameter as 0, but this MEANS to find the first one... a 1 will cause it to find "the largest value that is less than or equal to lookup_value" (see http://office.microsoft.com/en-us/excel-help/match-HP005209168.aspx)

MY NEW SOLUTION: Change the last parameter to 1, like this:
MATCH(DATE(2011, 5, 5), <myDataRange>, 1).


Mark
 
Upvote 0
NOTE: This will ALSO "successfully" find things BETWEEN your data. So in my example, a non-existent date that is greater than 2011,5,5 (e.g. 2011,5,6) would return the greatest PRESENT data (the last 2011,5,5 in my list).

This IS what I want, but in some cases, you may not want that.
 
Upvote 0
markrice said:
I found a straightforward solution as an alternative that may work for you.

MY OLD WAY: MATCH(DATE(2011, 5, 5), <MYDATARANGE>, 0)
Well, I usually default the last parameter as 0, but this MEANS to find the first one... a 1 will cause it to find "the largest value that is less than or equal to lookup_value" (see http://office.microsoft.com/en-us/excel-help/match-HP005209168.aspx)

MY NEW SOLUTION: Change the last parameter to 1, like this:
MATCH(DATE(2011, 5, 5), <MYDATARANGE>, 1).


Mark
Click to expand...
If you want to find the row number of the last instance of the lookup value...

Book1
ABCD
211/6/2006_5/5/201111
35/5/2011___
43/14/2008___
58/16/2008___
64/20/2014___
72/12/2005___
811/18/2003___
910/15/2010___
108/26/2012___
115/5/2011___
125/4/2011___
137/4/2010___
141/4/2001___
157/11/2006___
Sheet2

This formula entered in D2:

=LOOKUP(2,1/(A2:A15=C2),ROW(A2:A15))
 
Upvote 0
I've been trying this for over an hour with MAX(IF()), which worked on one worksheet but not another, INDEX(MATCH()), LOOKUP()....you name it. Nothing worked but INDEX(SUMPRODUCT()) is perfect!
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Jeffrey Mahoney
  • Question
Row of Last non-blank cell in column Dynamic Named Range
Replies
7
Views
136
Cubist
Cubist
C
  • Solved
Match using fixed columns but dynamic row
Replies
4
Views
126
Fluff
Fluff
3
  • Question
Find the last occurrence
Replies
4
Views
124
Peter_SSs
Peter_SSs
N
  • Question
find data containing key words
2
Replies
11
Views
289
NdNoviceHlp
NdNoviceHlp
W
  • Question
SCHEDULE DASHBOARD VBA CODE (AUTOMATCICALLY TOGGLE TO MATCHING COLUMN)
Replies
0
Views
84
Willkim
W

Forum statistics

Threads
1,215,004
Messages
6,122,659
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top