Multiple Match

[markoakes]

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

 

[markoakes]

Book5
	A	B	C	D	E	F
1	2003	CovingtonVA		2003	CovingtonVA
2	2003	CovingtonVA		2003	CovingtonVA
3	5008	DanvilleVA		5008	DanvilleVA
4	2003	DanvilleVA		5008	DanvilleVA	X
5	5008	DanvilleVA		5008	DanvilleVA
6	3028	JohnsonCityTN		3028	JohnsonCityTN
7	3028	JohnsonCityTN		3028	JohnsonCityTN
8	3028	JohnsonCityTN		3028	JohnsonCityTN
9	5008	LynchburgVA		5008	LynchburgVA
10	5008	LynchburgVA		5008	LynchburgVA
11	5008	LynchburgVA		5008	LynchburgVA
12	3028	LynchburgVA		5008	LynchburgVA	X
13	3028	LynchburgVA		5008	LynchburgVA	X
Sheet1

 

[Ron Coderre]

Try this:

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]

Ron,

This is a learner question.

What would be wrong with

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

 

[markoakes]

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

 

[Dryver14]

Mark try the formula above

 

[markoakes]

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]

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]

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]

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


Cheers Ron