Finding last match

[darlbiln]

I came up with this formula to find matches in another file which I cannot edit:

=MATCH("*"&A2&"*",'[F4 2006_ECO Log.xls]ECO Log'!$D:$D,0)

It works fine, except I want to find the last match in column D (there are something like 3 total matches). Match seems to return only the first match. I looked at inverting column D, but could not find an easy way to do this within the match formula.

Constraints: I cannot edit the other file.

 

[kevatarvind]

hi and welcome to the board

Try like below ( Confirm With CTRL + SHIFT + ENTER )

=MAX(('[F4 2006_ECO Log.xls]ECO Log'!$D:$D="*&A2&*")*ROW('[F4 2006_ECO Log.xls]ECO Log'!$D:$D))-ROW('[F4 2006_ECO Log.xls]ECO Log'!$D$1)+1

 

[Aladin Akyurek]

I came up with this formula to find matches in another file which I cannot edit:

=MATCH("*"&A2&"*",'[F4 2006_ECO Log.xls]ECO Log'!$D:$D,0)

It works fine, except I want to find the last match in column D (there are something like 3 total matches). Match seems to return only the first match. I looked at inverting column D, but could not find an easy way to do this within the match formula.

Constraints: I cannot edit the other file.

Looks like you want the native row number of the row at which the last match occurs. if so:

=LOOKUP(9.99999999999999E+307,SEARCH(A2,'[F4 2006_ECO Log.xls]ECO Log'!$D:$D),ROW('[F4 2006_ECO Log.xls]ECO Log'!$D:$D)

 

[darlbiln]

Thank you both for responding.

Kevatarvind I was not able to get your formula to work. I am not sure why. Can you use $D:$D="*&A2&*" in place of match?

Aladin Akyurek, your formula worked. At first I was wondering why you entered such a large number for the lookup value, but then I found in another post:

• If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

This must be what this formula is exploiting. Very clever, thanks!

 

[Aladin Akyurek]

Thank you both for responding.

Kevatarvind I was not able to get your formula to work. I am not sure why. Can you use $D:$D="*&A2&*" in place of match?

Aladin Akyurek, your formula worked. At first I was wondering why you entered such a large number for the lookup value, but then I found in another post:

• If the LOOKUP function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

This must be what this formula is exploiting. Very clever, thanks!

You are welcome. Yes, it exploits the undelying algorithm. Here is some additional info:

http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html