Compare sheets for matches

[DavidH56]

Hello,

I have a problem as I'm attempting to use a formula previously provided by
Bob Phillips on 10/23/07. This is the formula:

=IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),IF(ISNUMBER(MATCH('Sheet
A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found")

I tried using this formula but it would not work correctly. I believe my
problem has to do with my column A data has a combination of numbers and
text, for example 45RAC60098V23. My column B data has only one text letter
such as R in which case it also uses the color red as the cell's fill color.
It may be either R(red), G (green), Y(yellow) as well. I believe the formula
would work if I only had numbers in my two columns. I appreciate any
assistance in resolving this issue.

 

[Dave Peterson]

The text/number property won't make any difference.

Your formula looks a little weird:

=IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),
IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found")

Did you really mean to look at 'sheet a' for B2? Maybe it's a simple typo.

My next guess is that your data isn't what you expect.

Debra Dalgleish has some more notes on trouble shooting these kinds of formulas:
http://www.contextures.com/xlFunctions02.html#Trouble

=====
ps. Your formula isn't looking for a match on the same row--it's just looking
for a match in those columns. Is that what you really wanted?

 

[DavidH56]

Thank you Dave for your quick response. I got it to work okay. I looked
closely at what I had. I had initially used columns F and B. When I
readjusted to use A and B, it worked beautifully. Thanks again for your
response.
--
By persisting in your path, though you forfeit the little, you gain the
great.

The text/number property won't make any difference.

Your formula looks a little weird:

=IF(ISNUMBER(MATCH(A2,'Sheet B'!A:A,0)),
IF(ISNUMBER(MATCH('Sheet A'!B2,'Sheet B'!B:B,0)),"Yes","No"),"Not Found")

Did you really mean to look at 'sheet a' for B2? Maybe it's a simple typo.

My next guess is that your data isn't what you expect.

Debra Dalgleish has some more notes on trouble shooting these kinds of formulas:
http://www.contextures.com/xlFunctions02.html#Trouble

=====
ps. Your formula isn't looking for a match on the same row--it's just looking
for a match in those columns. Is that what you really wanted?