One way to return the last incident in the list using Search, assuming A1:A5
is the table to be searched, D1 contains the substring to find in A1:A5.

=INDEX(A1:A5,MAX(IF(--(ISNUMBER(SEARCH(D1,A1:A7,1)))=0,"",((ISNUMBER(SEA RCH(D1,A1:A7,1)))*ROW(INDIRECT("1:"&ROWS(A1:A7)))) )))

Must confirm with Control+Shift+Enter after keying it in.

"Michael at Thin Air" wrote:

Is there a way to use LOOKUP with wildcards?

Or is there something that does the similar operation on strings that can
use wild cards but also return the last incident in the list?

I am doing a lookup on columns that have similar grouped named items in it
and would like the last found item to be placed in another row.

=IF($M13=1,LOOKUP("A Upgrade of Service - 2
Year",O$6:O12),IF(ISNUMBER(SEARCH("Upgrade",$L13)) ,$L13,0))

This is my current search which if M13 = 1 indicating this is the service
plan, I want the type of service to be placed in o13 which is the last item
found in the lookup. So similar to SEARCH is what I'm looking for but because
I'm trying to ultimately make one row of compiled data from about 20 rows of
information, I run out of "IF's" in a big hurry.

It would be awesome if:

=IF($M13=1,LOOKUP("*Service*",O$6:O12),IF(ISNUMBER (SEARCH("Upgrade",$L13)),$L13,0))

Worked but it doesn't.

My next thought was:

=IF($M13=1,LOOKUP("Service",O$6:O12),IF(ISNUMBER(S EARCH("Upgrade",$L13)),$L13,0))

But since I can't change LOOKUP to find anything close or greater than, it's
not working for me without an exact match.