Match Function arguments

S

Stan Altshuller

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
 
P

Peo Sjoblom

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

With F1 value, look at A, if match or partial match return B 5
Reverse a function to return the array? 4
Vlookup and match part of text in a cell 3
Using VLookup when text isn't an exact match 3
INDEX & MATCH find first column value in an array? 3
return proper match from three columns 2
Index and Match 8
vlookup - find an alternative approximate match 4

Top