Checking values for multiple columns

[Figmint]

Hello,

Ive done a quick search here and been unable to find an exact answer for my question.
I may just be searching for the wrong keywords, I dont know.

What I am looking to do is check 2 columns of variables against 2 columns of correct data and then flag the incorrect data. This would be case sensitive as well.

Example:
Data (sheet 2)

Col 1 Col 2
Bolt Nut
Screw Driver
Nail Hammer
​

Variables (sheet 1)

Col 1 Col 2 Col 3
Bolt Hammer Non-Match
Bolt Driver Match
Nail Driver Non-Match
SCREW DriveR Non-Match​

I have tried with nested If/AND statements and I keep getting errors.
For my document I am checking about 25 constant data sets against a document of over 5000 rows, so doing it manualy is slow and anoying.

Any assistance would be appreciated.

 

[BarryL]

Bolt Hammer Non-Match
Bolt Driver Match
Nail Driver Non-Match
SCREW DriveR Non-Match

why are these matches/non-matches? your logic isn't that clear to me.

 

[My Aswer Is This]

Why is this a "Match"

Bolt Driver Match

Looks like to me this would be "Non-Match"

 

[Figmint]

Sorry, should have been Bolt Nut Match

 

[Figmint]

Also sorry again, I had pasted in the info from excel, but guess it didnt copy the table
Hope this helps (also corrected earlier mistake)

Data (sheet 2)
	Col 1	Col 2
	Bolt	Nut
	Screw	Driver
	Nail	Hammer
Variables (Sheet 1)
	Col 1	Col 2	Col 3
	Bolt	Hammer	Non-Match
	Bolt	Nut	Match
	Nail	Driver	Non-Match
	SCREW	DriveR	Non-Match

<tbody>
</tbody>

 

[austin397]

=IF('Variables'!A1='Data'!A1,"Match","No-Match")

I think this should work for you if I understand you question correctly.

 

[BarryL]

the below might work? its not case sensitive

=IF(SUMPRODUCT((Data!A:A=Variables!A1)*(Data!B:B=Variables!B1))>0,"Match","Non-Match")

 

[Figmint]

Thanks to austin397 and BarryL for the responces, but they dont seem to work right

austin397
=IF('Variables'!A1='Data'!A1,"Match","No-Match")

BarryL
=IF(SUMPRODUCT((Data!A:A=Variables!A1)*(Data!B:B=Variables!B1))>0,"Match","Non-Match")

The match/non-match seems flipped, which is easy to fix.

austin397's doesn't flag empty cells for non-match for the second cell
BarryL's doesn't seem to flag empty cells for first cell
But neither one seems to do case sensitivity or leading spaces
How would I correct those aspects ?

I am also using Excel Pro 2010 if that helps.

Variables:

Col 1	Col 2	Col 3	Col 3
Bolt	Hammer	Non-Match	Non-Match
Bolt	Nut	Match	Match
Nail	Driver	Non-Match	Non-Match
SCREW	DriveR	Match	Match
Bubble		Match	Non-Match
BUBBLE	Gum	Match	Match
	Set	Non-Match	Match
Swing	Set	Match	Match

<tbody>
</tbody><colgroup><col span="2"><col><col></colgroup>

Data:

Col 1	Col 2
Bolt	Nut
Screw	Driver
Nail	Hammer
Bubble	Gum
Swing	Set

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[BarryL]

try

=IF(SUMPRODUCT((EXACT(A2,Data!$A$1:$A$5))*(EXACT(Variables!B2,Data!$B$1:$B$5)))>0,"Match","Non-Match")

change range as needed

 

[Figmint]

Thanks BarryL

It works so far, but last issue is when trying to sort the whole document the second set of variables point to diffrent lines:

Before sort
=IF(SUMPRODUCT((EXACT(F18,Data!$A$1:$A$68))*(EXACT(Variable!H18,Data!$B$1:$B$68)))>0,"match","Non-Match")

After sort
=IF(SUMPRODUCT((EXACT(F18,Rank!$A$1:$A$68))*(EXACT(Variable!H432,Data!$B$1:$B$68)))>0,"match","Non-Match")

Some reason sort isnt sorting by the value generated properly and then the formula gets messed up.