search array
 

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!

search array
 

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

jchick0909 wrote:

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

search array
 

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" wrote:

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

jchick0909 wrote:

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

search array
 

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.

jchick0909 wrote:

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" wrote:

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

jchick0909 wrote:

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


--

Dave Peterson