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

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
2JohnWhiteKimWhite
3AliceWhiteGiacintaBrown
4TomBrownJohnWhite
5GiacintaGreenMasamoGreen

Any help really appreciated.

Thank you
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
Based on your example use this CF formula:

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

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

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
161
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This uses conditional formatting, or another column with the answer
MrExcelPlayground.xlsx
ABCDE
1FIRST NAMESURNAMEFIRST NAMESURNAMETest
2JohnWhiteKimWhiteNot found
3AliceWhiteGiacintaBrownNot found
4TomBrownJohnWhiteFound
5GiacintaGreenMasamoGreenNot found
Sheet14
Cell Formulas
RangeFormula
E2:E5E2=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
CellConditionCell FormatStop If True
C2:C5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
D2:D5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

This uses conditional formatting, or another column with the answer
MrExcelPlayground.xlsx
ABCDE
1FIRST NAMESURNAMEFIRST NAMESURNAMETest
2JohnWhiteKimWhiteNot found
3AliceWhiteGiacintaBrownNot found
4TomBrownJohnWhiteFound
5GiacintaGreenMasamoGreenNot found
Sheet14
Cell Formulas
RangeFormula
E2:E5E2=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
CellConditionCell FormatStop If True
C2:C5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
D2:D5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
Thank you James, I will try this tomorrow and let you know how I get on.
 

Detectiveclem

Active Member
Joined
May 31, 2014
Messages
277
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
This uses conditional formatting, or another column with the answer
MrExcelPlayground.xlsx
ABCDE
1FIRST NAMESURNAMEFIRST NAMESURNAMETest
2JohnWhiteKimWhiteNot found
3AliceWhiteGiacintaBrownNot found
4TomBrownJohnWhiteFound
5GiacintaGreenMasamoGreenNot found
Sheet14
Cell Formulas
RangeFormula
E2:E5E2=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
CellConditionCell FormatStop If True
C2:C5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
D2:D5Expression=IF(ISNA(VLOOKUP(C2&D2,IF(1=1,$A$2:$A$5&$B$2:$B$5,""),1,FALSE)),FALSE(),TRUE())textNO
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

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
Top