PDA

View Full Version : Find & highlight multiple values from a different sheet?



Faugheen
2016-09-02, 12:41 PM
Hi everyone, really hope you can help.

So I have 2 sheets:

Sheet 1 - information including - Customer ID, Postcode, Email address, Product order history, email opt in
Sheet 2 - shorter list which just lists email address for those who are opted in to email.

What I need to do is cross-match the people in the sheet 2 by searching by their email address, to then find and ideally highlight their CUSTOMER ID in sheet 1.

How could I achieve this please?

Thank you so much for any help.

NBVC
2016-09-02, 12:53 PM
In Sheet1, select the range of Customer ID's then go to Home|Conditional Formatting|New Rule.
Select "use a formula to determine which cells to format"
enter formula like:

=MATCH($B2,Sheet2!A:A,0)

where B2 contains first Email in first row you selected to affect and to match with "Sheet2" column A. Change sheet name and ranges to suit your data.

Then click Format and from the Fill tab choose your colour.

Click OK.

Then OK again to finish.

Faugheen
2016-09-02, 02:19 PM
In Sheet1, select the range of Customer ID's then go to Home|Conditional Formatting|New Rule.
Select "use a formula to determine which cells to format"
enter formula like:

=MATCH($B2,Sheet2!A:A,0)

where B2 contains first Email in first row you selected to affect and to match with "Sheet2" column A. Change sheet name and ranges to suit your data.

Then click Format and from the Fill tab choose your colour.

Click OK.

Then OK again to finish.

Thank you for your reply. However, I can't get it to work though.

Just to confirm:

Customer ID = A2:A342000 in Sheet 1
Email address of ALL customers = F2:F342000 in Sheet 1
Email address of those mailed = A2:A15000 in Sheet 2

Header for email addresses in Sheet 2 = customersemailed

Do I have to highlight all of the Customer IDs as that is what I need to highlight once emails have been cross-matched? Am I doing something wrong?

Many thanks.

NBVC
2016-09-02, 02:49 PM
Yes, select A2:A342000 in Sheet 1, then do the Conditional formatting with formula:

=MATCH($F2,'Sheet2'!$A:$A,0)

Pecoflyer
2016-09-02, 02:52 PM
Does CF work when referencing another sheet ? Only starting with XL2010 I think...

Faugheen
2016-09-02, 03:07 PM
Yes, select A2:A342000 in Sheet 1, then do the Conditional formatting with formula:

=MATCH($F2,'Sheet2'!$A:$A,0)

Worked a treat - thank you so much!