Hi All

I am trying to use the match function in order to find a look up value
(text) in an array of strings.
If there is an exact match, I need to return the index of the match - no
problem with match(lookup,array,0)
Now, if there is no exact match I need to return the index of the string in
the array that has the same first 3 characters as the look-up value.


EX.

array1:
abc123
abb123
abd123
abf123

lookup value
abb59595

i need the match function to return 2

Any help is greatly appreciated, and thanks for your time in advance!!

Stan

One way

=IF(ISNA(MATCH(C1,A2:A20,0)),MATCH(LEFT(C1,3),LEFT (A2:A20,3),0),MATCH(C1,A2:
A20,0))

entered with ctrl + shift & enter

where C1 = abb123


--

Regards,

Peo Sjoblom


"Stan Altshuller" wrote in message
...
Hi All

I am trying to use the match function in order to find a look up value
(text) in an array of strings.
If there is an exact match, I need to return the index of the match - no
problem with match(lookup,array,0)
Now, if there is no exact match I need to return the index of the string
in
the array that has the same first 3 characters as the look-up value.


EX.

array1:
abc123
abb123
abd123
abf123

lookup value
abb59595

i need the match function to return 2

Any help is greatly appreciated, and thanks for your time in advance!!

Stan

Stan,

Try this

=MATCH(LEFT("abb5656",3),LEFT(A1:A10,3),0)

which is an array, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Stan Altshuller" wrote in message
...
Hi All

I am trying to use the match function in order to find a look up value
(text) in an array of strings.
If there is an exact match, I need to return the index of the match - no
problem with match(lookup,array,0)
Now, if there is no exact match I need to return the index of the string
in
the array that has the same first 3 characters as the look-up value.


EX.

array1:
abc123
abb123
abd123
abf123

lookup value
abb59595

i need the match function to return 2

Any help is greatly appreciated, and thanks for your time in advance!!

Stan

=MATCH(LEFT(LookupValue,3)&"*",MatchRange,0)

Stan Altshuller wrote:
Hi All

I am trying to use the match function in order to find a look up value
(text) in an array of strings.
If there is an exact match, I need to return the index of the match - no
problem with match(lookup,array,0)
Now, if there is no exact match I need to return the index of the string in
the array that has the same first 3 characters as the look-up value.


EX.

array1:
abc123
abb123
abd123
abf123

lookup value
abb59595

i need the match function to return 2

Any help is greatly appreciated, and thanks for your time in advance!!

Stan