=LOOKUP(2,1/SEARCH("sub_string",B1:B20),A1:A20)

Better to use a cell reference:

D1 = sub_string

=LOOKUP(2,1/SEARCH(D1,B1:B20),A1:A20)

--
Biff
Microsoft Excel MVP


"Trainer_00" wrote in message
...
Thank you, that formula worked very well except for the instances where
the
text string was actually a sub-text string within a larger string in a
single
cell. Any suggestions of how I can use your formula and match a sub-text
string?

"T. Valko" wrote:

Try this:

=LOOKUP(2,1/(B1:B20="text_string"),A1:A20)

--
Biff
Microsoft Excel MVP


"Trainer_00" wrote in message
...
I am using an INDEX & MATCH formula to search for the occurrence of a
specific text string within an array. I used the following formula:
=INDEX($A$1:$A$20,MATCH("*Specific Text String*",$B$1:$B$20,0)) But
this
formula works its way down the array giving me the first occurrence of
the
"Specific Text". What I want is the last occurrence (or the one
closest
to
the end of the array). Please help.