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

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

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

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