Conditional formatting if cell text matches one of multiple other checked cells texts

Question

If a recipe is checked, I want the formula for the conditional formatting to be looking for a recipe match (of all of the recipes checked (for which rows column C is True)) in D3:G7 and highlight certain cells if a match occurs.

The highlighted cells should be:

• The cell in the row where the match was found in column A.
• The cells where the match was found.

I have tried for hours and got familiar with a lot of new functions while working on my sheet but I just keep getting overwhelmed and find myself at a dead end on this specific task.

Would very much appreciate your help!

Answer 1

Finally, after literally 8+ hours of trying around, I found a solution.

First I filter all the Recipes that are checked with in cell I3

=FILTER(B3:B,C3:C=TRUE).

Then I use 2 conditional formatting formulas:

Forumla for A1:A

=AND(countif($I$3:$I,B3)>0,B3<>"")

Formula for D3:G7

=AND(countif($I$3:$I,D3)>0,D3<>"")

For anyone wondering, I use AND to include a second rule (D3<>"") to exclude blank cells from highlighting.

Can't believe it was so easy..

This is how it looks now:

Answer 2

Conditional Formatting formula

For each value in D3:G, count how many rows in $B$3:$B have the same value, and also $C$3:$C is checked (true).

Formula will be interpreted as True for any non-zero result

Formula: =SUMPRODUCT($B$3:$B=D3, $C$3:$C)

Range:   D3:G