Matching criteria from data in two columns with data in two separate columns

[Detectiveclem]

Can anyone assist?

I have First names entered in Column B [B2:B5000] and their Surname in Column C [C2:C5000]. Let’s call this data set A

I have copied from another spreadsheet a list of First names in Column D [D2:D5000] and their Surname in Column E [E2:E5000]. Let’s call this set B.

I need to find where the data held in Set A exactly matches the data held in Set B and highlight the match (possibly using Conditional Formatting, but happy to be advised).

For the sake of clarity this is the type of data I am trying to match


SET A SET B

​	A​	B​	C​	D​
1	FIRST NAME​	SURNAME​	FIRST NAME​	SURNAME​
2	John	White	Kim	White
3	Alice	White	Giacinta	Brown
4	Tom	Brown	John	White
5	Giacinta	Green	Masamo	Green

Any help really appreciated.

Thank you

 

[steve the fish]

Based on your example use this CF formula:

=COUNTIFS($A:$A,$C2,$B:$B,$D2)

applied to =$C$2:$D$5

 

[JamesCanale]

This uses conditional formatting, or another column with the answer

MrExcelPlayground.xlsx
	A	B	C	D	E
1	FIRST NAME	SURNAME	FIRST NAME	SURNAME	Test
2	John	White	Kim	White	Not found
3	Alice	White	Giacinta	Brown	Not found
4	Tom	Brown	John	White	Found
5	Giacinta	Green	Masamo	Green	Not found
Sheet14

Cell Formulas
Range	Formula
E2:E5	E2	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),"Not found","Found")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO
D2:D5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO

 

[Detectiveclem]

Based on your example use this CF formula:

=COUNTIFS($A:$A,$C2,$B:$B,$D2)

applied to =$C$2:$D$5

Thank you Steve the Fish, I will try this tomorrow and let you know how I get on.

 

[Detectiveclem]

This uses conditional formatting, or another column with the answer

MrExcelPlayground.xlsx
	A	B	C	D	E
1	FIRST NAME	SURNAME	FIRST NAME	SURNAME	Test
2	John	White	Kim	White	Not found
3	Alice	White	Giacinta	Brown	Not found
4	Tom	Brown	John	White	Found
5	Giacinta	Green	Masamo	Green	Not found
Sheet14

Cell Formulas
Range	Formula
E2:E5	E2	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),"Not found","Found")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO
D2:D5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO

Thank you James, I will try this tomorrow and let you know how I get on.

 

[Detectiveclem]

Based on your example use this CF formula:

=COUNTIFS($A:$A,$C2,$B:$B,$D2)

applied to =$C$2:$D$5

Thank you Steve the Fish, works perfectly. Much appreciated. Have a great day.

 

[Detectiveclem]

This uses conditional formatting, or another column with the answer

MrExcelPlayground.xlsx
	A	B	C	D	E
1	FIRST NAME	SURNAME	FIRST NAME	SURNAME	Test
2	John	White	Kim	White	Not found
3	Alice	White	Giacinta	Brown	Not found
4	Tom	Brown	John	White	Found
5	Giacinta	Green	Masamo	Green	Not found
Sheet14

Cell Formulas
Range	Formula
E2:E5	E2	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),"Not found","Found")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO
D2:D5	Expression	=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())	text	NO

Thank you James, I have used Steve the Fish's solution. But can certainly see where I can also use your solution, so thank you. Have a great day.