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

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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
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.
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
Back
Top