EXCEL 2016. Highlight row if multiple cells in Sheet1 match multiple cells in Sheet2

Question

Amazing Excel Users,

I have a spreadsheet that is as follows

Sheet 1
Column A: Exhibit
Column B: Animal
Column C: Food

Sheet 2
Column A: Animal
Column B: Food

I would like to create a formula that does this

1. Check row1 in sheet1 to make sure that the combination of column A and B, matches a possible combination outlined in sheet2.

2. If the combination exists, highlight row

3. If the combination does not exist, do not highlight row.

For example, if sheet1 is

A      | B        | C
Star   | Unicorn  | Apples 
Star   | Unicorn  | Toffee
Circle | Elephant | Apples

And sheet2 is

A        | B      
Unicorn  | Apples
Elephant | Toffee

The formula will highlight the first row (Star | Unicorn | Apples) of sheet1.

So I'm basically looking for a formula that checks sheet1 column-B/C-combo to see if column-A/B-combo of sheet 2 exist. Any help would be truly appreciated.

Answer 1

Create a column in sheet2 with the following formula (in my example I put it in column D):

 =TRIM(CONCATENATE(A2,B2))

Go back to the sheet1 and select your data range. Go into the custom formatting tool and enter this formula as the rule with your desired highlighting choice:

=COUNTIF(Sheet2!$D:$D, TRIM(CONCATENATE($B2,$C2)))>0

---

CONCATENATE() combines the animal and food names into a single string to make it easy to search for various combinations. TRIM() is probably overkill, but it gets rid of any extra spaces that might have been typed after each word that could potentially mess up the combination.

COUNTIF() then takes the combination in each row on sheet1 and compares it to the entire list of combinations on sheet2 and returns the number of matches that it finds.

Finally, the conditional formatting rule is set to highlight any row for witch the COUNTIF() finds at least one match.

Answer 2

Use COUNTIFS with

• the values in Columns B and C on Sheet 1 as your criteria
• Columns A and B on Sheet 2 as your criteria ranges

and check that the result is > 0.

=COUNTIFS(Sheet2!$A:$A,$B2,Sheet2!$B:$B,$C2)>0