Highlight duplicates - two sheets - based on two cell values

I have an excel which has 2 sheets. Need to highlight (the whole row) on the Target Group-sheet, information (criteria:street address, firsts & last name match) that is on "contacted-sheet".
As there is no key identifier, I thought if Street Address and Names could be criterias to be used for matching?

As a user I want to see on my marketing campaign target group, which of these persons have contacted.

I tired conditional formatting but for some reason it does not work (=Countif). Would appreciate any help.
Sample file enclosed.
Thanks!

The whole trick is to make it evaluate to a TRUE scenario... in this case you are asking IF there is at least 1 record on contacted.

We can do this by doing a count where the values are a match

Conditional formatting would be this as the FORMULA RULE where you apply it to A2:G8, if you start with row 1 in the applied to range you need to make sure the references are also row 1... My example I am starting row 2 so be aware
=COUNTIFS('Have contacted'!$C:$C,$D2,'Have contacted'!$A:$A,$B2,'Have contacted'!$B:$B,$C2)>0

You can test the formula by removing the logic evaluation >0 and simply get the count of records that are a match.

IN I2 on your Target Group put the formula in

=COUNTIFS('Have contacted'!$C:$C,$D2,'Have contacted'!$A:$A,$B2,'Have contacted'!$B:$B,$C2)

and drag it down, you will find where there is 1 or more... in this example you only ever have 1 instance of an exact match thus row 4 becomes a 1 or is highlighted if rule is applied

Using Power Query and an exact match for First Name, Last Name and Street Address and a Left Join

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

G2=A2&" "&B2

Same for the second sheet.

H2 =IFERROR(IF(MATCH(G2,'Target group'!$H$1:$H$8,0)>0,"contacted"),"not contacted")

See the attached file.

You got answers from several forummembers.

Please add in the reply to whom you are replying.

Glad I could help.

Thanks for marking the question solved.

You can add reputation by clicking on the star * add reputation.