# Multiple Match

#### markoakes

##### Active Member
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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Book5
ABCDEF
12003CovingtonVA2003CovingtonVA
22003CovingtonVA2003CovingtonVA
35008DanvilleVA5008DanvilleVA
42003DanvilleVA5008DanvilleVAX
55008DanvilleVA5008DanvilleVA
63028JohnsonCityTN3028JohnsonCityTN
73028JohnsonCityTN3028JohnsonCityTN
83028JohnsonCityTN3028JohnsonCityTN
95008LynchburgVA5008LynchburgVA
105008LynchburgVA5008LynchburgVA
115008LynchburgVA5008LynchburgVA
123028LynchburgVA5008LynchburgVAX
133028LynchburgVA5008LynchburgVAX
Sheet1

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?

Ron,

This is a learner question.

What would be wrong with

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

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

Mark try the formula above

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,
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?

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?

yes it helps greatly thanks I keep falling into that trap with a few formulas

Cheers Ron

Replies
2
Views
393
Replies
3
Views
284
Replies
1
Views
257
Replies
3
Views
351
Replies
8
Views
1K

1,218,516
Messages
6,142,921
Members
450,455
Latest member
Handienak

### 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.

### Which adblocker are you using?

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

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