search an array in reverse (bottom to top) order
 

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.

search an array in reverse (bottom to top) order
 

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.

search an array in reverse (bottom to top) order
 

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.

search an array in reverse (bottom to top) order
 

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.

search an array in reverse (bottom to top) order
 

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.

search an array in reverse (bottom to top) order
 

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