Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which Function to Use? Search an Array, Return a Row Value | Excel Discussion (Misc queries) | |||
Defining an array to search by | Excel Discussion (Misc queries) | |||
Text Search in an Array | Excel Worksheet Functions | |||
Text Search in an Array | Excel Discussion (Misc queries) | |||
Search array and return element No | Excel Worksheet Functions |