| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match function
I basicaly have 2 worksheets and I would like to see if a match is found, if
the name is found in Sheet2 display Found otherwise Display Not Found. For the most part it seems to work but for some reasons some should be found but are not.... See below example Sheet1.Name Results Sheet2.name ABC'S Found AAA CS A & B Shop Not Found ABC's CS Curt Sporting Goods NOt Found ANDY's Place CS CHUCK'S GUN AND PAWN Found BBB Shop CS Duke's Not Found CCC's Goods Dave's Shop Found CHAD's Shop Don's Not Found Cuck's Gun and Pawn CS Curt Sporting Goods CS Dave's Shop CS Don's As you can see Don's is an Exact match (as far as I can tell) and it is not found, Curt Sporting goods is real close and it displays not found. (CS exists on Sheet2) The formula that I am using in the results column is as follows: =IF(SUM(IF(ISNUMBER(MATCH("*"&$B$2:$B$206&"*",Shee t2!$J$2:Sheet2!$J$553,0)),1,0))0,"Found","Not Found") Any suggestions will be greatly appreciated Thanks TSharp |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match function
Maybe something like this:
On Sheet1...This formula returns "Found" if the value in B2 is located in Sheet2, cells J2:J553....otherwise, "Not Found": C2: =IF(COUNTIF(Sheet2!$J$2:$J$553,"*"&B2&"*"),,"Not ")&"Found" Copy that formula down as far as you need. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "ToniS" wrote in message ... I basicaly have 2 worksheets and I would like to see if a match is found, if the name is found in Sheet2 display Found otherwise Display Not Found. For the most part it seems to work but for some reasons some should be found but are not.... See below example Sheet1.Name Results Sheet2.name ABC'S Found AAA CS A & B Shop Not Found ABC's CS Curt Sporting Goods NOt Found ANDY's Place CS CHUCK'S GUN AND PAWN Found BBB Shop CS Duke's Not Found CCC's Goods Dave's Shop Found CHAD's Shop Don's Not Found Cuck's Gun and Pawn CS Curt Sporting Goods CS Dave's Shop CS Don's As you can see Don's is an Exact match (as far as I can tell) and it is not found, Curt Sporting goods is real close and it displays not found. (CS exists on Sheet2) The formula that I am using in the results column is as follows: =IF(SUM(IF(ISNUMBER(MATCH("*"&$B$2:$B$206&"*",Shee t2!$J$2:Sheet2!$J$553,0)),1,0))0,"Found","Not Found") Any suggestions will be greatly appreciated Thanks TSharp |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match function
Thanks for responding back so quickly, I tried the below formula C2: =IF(COUNTIF(Sheet2!$J$2:$J$553,"*"&B2&"*"),,"Not ")&"Found" and I ended up with the same results. Another example of one that failed is Sheet1 ACE SPORTING GOODS, INC. sheet2 ACE * SPORTING GOODS INC. CS To me this should return 'Found' instead of 'Not Found' Thanks Tsharp "Ron Coderre" wrote: Maybe something like this: On Sheet1...This formula returns "Found" if the value in B2 is located in Sheet2, cells J2:J553....otherwise, "Not Found": C2: =IF(COUNTIF(Sheet2!$J$2:$J$553,"*"&B2&"*"),,"Not ")&"Found" Copy that formula down as far as you need. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "ToniS" wrote in message ... I basicaly have 2 worksheets and I would like to see if a match is found, if the name is found in Sheet2 display Found otherwise Display Not Found. For the most part it seems to work but for some reasons some should be found but are not.... See below example Sheet1.Name Results Sheet2.name ABC'S Found AAA CS A & B Shop Not Found ABC's CS Curt Sporting Goods NOt Found ANDY's Place CS CHUCK'S GUN AND PAWN Found BBB Shop CS Duke's Not Found CCC's Goods Dave's Shop Found CHAD's Shop Don's Not Found Cuck's Gun and Pawn CS Curt Sporting Goods CS Dave's Shop CS Don's As you can see Don's is an Exact match (as far as I can tell) and it is not found, Curt Sporting goods is real close and it displays not found. (CS exists on Sheet2) The formula that I am using in the results column is as follows: =IF(SUM(IF(ISNUMBER(MATCH("*"&$B$2:$B$206&"*",Shee t2!$J$2:Sheet2!$J$553,0)),1,0))0,"Found","Not Found") Any suggestions will be greatly appreciated Thanks TSharp |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match function
The way you described your situation, the list is on Sheet2
and the items to find are on Sheet1. If that's true, then the wildcard would need to be in the Sheet1 list, not the Sheet2 list. Example: If the Sheet2 list includes this item: "ACE's REALLY GOOD SPORTING GOODS, INC. CS" then the formula I posted will find it using this criteria: "Sheet1!C2: ACE* SPORTING GOODS INC" Does that help?. Regards, Ron Microsoft MVP - Excel "ToniS" wrote in message ... Thanks for responding back so quickly, I tried the below formula C2: =IF(COUNTIF(Sheet2!$J$2:$J$553,"*"&B2&"*"),,"Not ")&"Found" and I ended up with the same results. Another example of one that failed is Sheet1 ACE SPORTING GOODS, INC. sheet2 ACE * SPORTING GOODS INC. CS To me this should return 'Found' instead of 'Not Found' Thanks Tsharp "Ron Coderre" wrote: Maybe something like this: On Sheet1...This formula returns "Found" if the value in B2 is located in Sheet2, cells J2:J553....otherwise, "Not Found": C2: =IF(COUNTIF(Sheet2!$J$2:$J$553,"*"&B2&"*"),,"Not ")&"Found" Copy that formula down as far as you need. Is that something you can work with? Post back if you have more questions. Regards, Ron Microsoft MVP - Excel "ToniS" wrote in message ... I basicaly have 2 worksheets and I would like to see if a match is found, if the name is found in Sheet2 display Found otherwise Display Not Found. For the most part it seems to work but for some reasons some should be found but are not.... See below example Sheet1.Name Results Sheet2.name ABC'S Found AAA CS A & B Shop Not Found ABC's CS Curt Sporting Goods NOt Found ANDY's Place CS CHUCK'S GUN AND PAWN Found BBB Shop CS Duke's Not Found CCC's Goods Dave's Shop Found CHAD's Shop Don's Not Found Cuck's Gun and Pawn CS Curt Sporting Goods CS Dave's Shop CS Don's As you can see Don's is an Exact match (as far as I can tell) and it is not found, Curt Sporting goods is real close and it displays not found. (CS exists on Sheet2) The formula that I am using in the results column is as follows: =IF(SUM(IF(ISNUMBER(MATCH("*"&$B$2:$B$206&"*",Shee t2!$J$2:Sheet2!$J$553,0)),1,0))0,"Found","Not Found") Any suggestions will be greatly appreciated Thanks TSharp |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Match as well as does not match array function | Excel Discussion (Misc queries) | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Hybrid Mode
