Finding the last match in a list

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...

On 2002-06-11 10:17, rowett wrote:
Many thanks!

It also appears to work without the SUMPRODUCT.

Regards,
Chris

Click to expand...

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...