Multiple Match

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325
I have 2 list each has 2 columns and I need to compare the 2 columns in the 1st list with the 2 columns in the 2nd list. I need a formula in column F that if the combination in the 1st list is not in the 2nd list will return an “X”.

A B C D E F
2003 Covington VA 2003 Covington VA
2003 Covington VA 2003 Covington VA
5008 Danville VA 5008 Danville VA
2003 Danville VA 5008 Danville VA X
5008 Danville VA 5008 Danville VA
3028 Johnson City TN 3028 Johnson City TN
3028 Johnson City TN 3028 Johnson City TN
3028 Johnson City TN 3028 Johnson City TN
5008 Lynchburg VA 5008 Lynchburg VA
5008 Lynchburg VA 5008 Lynchburg VA
5008 Lynchburg VA 5008 Lynchburg VA
3028 Lynchburg VA 5008 Lynchburg VA X
3028 Lynchburg VA 5008 Lynchburg VA X
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325
Book5
ABCDEF
12003CovingtonVA2003CovingtonVA
22003CovingtonVA2003CovingtonVA
35008DanvilleVA5008DanvilleVA
42003DanvilleVA5008DanvilleVAX
55008DanvilleVA5008DanvilleVA
63028JohnsonCityTN3028JohnsonCityTN
73028JohnsonCityTN3028JohnsonCityTN
83028JohnsonCityTN3028JohnsonCityTN
95008LynchburgVA5008LynchburgVA
105008LynchburgVA5008LynchburgVA
115008LynchburgVA5008LynchburgVA
123028LynchburgVA5008LynchburgVAX
133028LynchburgVA5008LynchburgVAX
Sheet1
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Try this:
Code:
F1: =IF(ISNA(MATCH(A1&"_"&B1,INDEX($D$1:$D$13&"_"&$E$1:$E$13,0),0)),"X","")
Copy that formula down as far as you need.

Can you work with that?
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Ron,

This is a learner question.

What would be wrong with

=IF(A1&B1=D1&E1,"","X")
 

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325

ADVERTISEMENT

Not sure why but I am not getting any "X"s in column F
 

markoakes

Active Member
Joined
Jan 5, 2004
Messages
325

ADVERTISEMENT

What I need to compare is if the single Row combination in column A & B matches the same single Row combination any where in column D & E. Hope this helps explain.
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
Ron,
This is a learner question.
What would be wrong with
=IF(A1&B1=D1&E1,"","X")
Without a separator of some kind...
A1: 200
B1: 3Alpha
and
D1: 2003
E1: Alpha
Would look like a match, when it is not.

2003Alpha does match 2003Alpha
But
200_3Alpha does NOT match 2003_Alpha.

Does that help?
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
What I need to compare is if the single Row combination in column A & B matches the same single Row combination any where in column D & E. Hope this helps explain.
That's what the formula I posted does.

Here are some potential issues to look for:
• Typo in the formula?
• Referenced ranges need to be adjusted

If that doesn't help, can you post the F1 formula you're using?
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
yes it helps greatly thanks I keep falling into that trap with a few formulas


Cheers Ron
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,441
Members
430,548
Latest member
hh_dh2001

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