Checking values for multiple columns

Figmint

New Member
Joined
Dec 19, 2016
Messages
6
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
Why is this a "Match"

Bolt Driver Match

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

Figmint

New Member
Joined
Dec 19, 2016
Messages
6

ADVERTISEMENT

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 1Col 2
BoltNut
ScrewDriver
NailHammer
Variables (Sheet 1)
Col 1Col 2Col 3
BoltHammerNon-Match
BoltNutMatch
NailDriverNon-Match
SCREWDriveRNon-Match

<tbody>
</tbody>
 

austin397

New Member
Joined
Dec 16, 2016
Messages
36
=IF('Variables'!A1='Data'!A1,"Match","No-Match")

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

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421

ADVERTISEMENT

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

New Member
Joined
Dec 19, 2016
Messages
6
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 1Col 2Col 3Col 3
BoltHammerNon-MatchNon-Match
BoltNutMatchMatch
NailDriverNon-MatchNon-Match
SCREWDriveRMatchMatch
Bubble MatchNon-Match
BUBBLEGumMatchMatch
SetNon-MatchMatch
SwingSetMatchMatch

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


Data:
Col 1Col 2
BoltNut
ScrewDriver
NailHammer
BubbleGum
SwingSet

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

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,421
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

New Member
Joined
Dec 19, 2016
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top