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

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

Change FIND to SEARCH for a case-insensitive match.

3. ## Thanks Shg!

Excellent! Works like a charm. Thanks.

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

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.

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.

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.

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

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

10. Perhaps because both intrinsically expect a vector or array.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1