search an array in reverse (bottom to top) order

[Trainer_00]

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.

 

[Gary''s Student]

See:

http://groups.google.com/group/micr...last+occurance"+group:*Excel#4bf31ed02fc7b157

 

[Trainer_00]

I appreciate the quick response, and I looked at the link you provided. I
attempted to use the formula on the link, but it did not work. I have a
strange feeling that I'm just missing a small part for my original formula
that I just can't figure out. Is there any way I can reverse the order of
the array within the formula? Thank you though, I really do appreciate the
effort.

 

[T. Valko]

Try this:

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

 

[Trainer_00]

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]

=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)