# Partial Match in an Array

1. ## Partial Match in an Array

column A
MARY
JOHN
MARK
SUE

cell B1
zzzJOHNxx

I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)

Thanks.  Register To Reply

2. Array formula: =INDEX(A1:A4, MATCH(TRUE, ISNUMBER(FIND(A1:A4, B1)), 0) )

Change FIND to SEARCH for a case-insensitive match.  Register To Reply

3. ## Thanks Shg!

Excellent! Works like a charm. Thanks.  Register To Reply

4. You could also use this non-array version

=LOOKUP(2^15,FIND(A1:A4,B1),A1:A4)

although, if you had more than one match it would find the last not the first  Register To Reply

5. ## Also works great!

I can understand that the 2^15 is just any large number but cannot understand how this can be used as a lookup_value, and how the FIND parameter can be used as a lookup_vector.

Also the FIND(A1:A4,B1) by itself seems to always return a #VALUE error, so can the lookup function make use of it.

Thanks.  Register To Reply

6. Do these two things and it will become clear:

1. See Help for the vector form of the LOOKUP function.

2. Open the Formula Auditing toolbar, and watch DLL's formula evaluate using the Evaluate button.

I'd use DLL's version.  Register To Reply

7. Originally Posted by matrex I can understand that the 2^15 is just any large number......
It's a little more specific than that. The maximum number of characters in a cell (and therefore the largest number that the FIND function can return) is 32767 (see Excel Help "specfications and limits"). 2^15 is 1 more than that.  Register To Reply

8. It's not clear to me why FIND returns an array, sans array formula, DLL. Can you generalize?  Register To Reply

9. LOOKUP, like SUMPRODUCT, can handle generated arrays without CSE. So while

=LOOKUP(2^15,FIND(A1:A4,B1))

doesn't require CSE, replace LOOKUP with MATCH and you do. Don't know why, though.....  Register To Reply

10. Perhaps because both intrinsically expect a vector or array.  Register To Reply