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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,309
Members
409,862
Latest member
lbisacca
Top