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.

See:

http://groups.google.com/group/micro...f31ed02fc7b157
--
Gary''s Student - gsnu200761


"Trainer_00" wrote:

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.

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.

"Gary''s Student" wrote:

See:

http://groups.google.com/group/micro...f31ed02fc7b157
--
Gary''s Student - gsnu200761


"Trainer_00" wrote:

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.

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.

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.

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