Partial Match in an Array

**matrex** · 12-13-2008, 01:59 AM

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.

**shg** · 12-13-2008, 02:18 AM

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

Change FIND to SEARCH for a case-insensitive match.

**matrex** · 12-13-2008, 01:42 PM

Excellent! Works like a charm. Thanks.

**daddylonglegs** · 12-13-2008, 03:58 PM

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

**matrex** · 12-13-2008, 07:28 PM

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.

**shg** · 12-13-2008, 07:36 PM

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.

**daddylonglegs** · 12-13-2008, 07:58 PM

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.

**shg** · 12-13-2008, 08:02 PM

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

**daddylonglegs** · 12-13-2008, 08:49 PM

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