Partial Match in an Array

[matrex]

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]

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

Change FIND to SEARCH for a case-insensitive match.

[matrex]

Excellent! Works like a charm. Thanks.

[daddylonglegs]

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]

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]

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]

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.