search array

[jchick0909]

Hi,

I have two columns containing names. there are some spelling and other
errors. I am trying to use a search function combined with an array and
Left(cell,4) functions to determine if the first four characters in the
column B match any of the names in column C.

Example:
B C
johnnathan, doe johnathen, doe
Smith, Jane Jane smith


if the first four characters match, i would like the cell containing the
formula to result with the name in column C that matches.

my attempt:
=if(isnumber(search((left,B3,4)),$C$3:$C$141),$C$3:$C$141,"") CSE

Thanks!

 

[Dave Peterson]

How about this in D1:
=COUNTIF(C:C,"*"&LEFT(B1,4)&"*")>0
and drag down.

 

[jchick0909]

That seems to be working somewhat, how can i alter the formula so that
instead of a true and fasle reslut, the formula results with the cell in
column C that matches the Left(cell,4) function?

side note: can you explain what the "*" does?

Thanks!

 

[Dave Peterson]

The asterisk is a wild card--it represents any number of characters. The
question mark is another wildcard--it represents a single character.

I don't think you could tweak that formula. But you could use this one instead:
=INDEX(C:C,MATCH("*"&LEFT(B1,4)&"*",C:C,0))
(In D1 and drag down)

It'll show the value of the cell (in column C) that matches.