Conditional formatting using lookup table and combination of column and header value

[uPhone]

Hi!

So this is a simplified example of what I'm working with.

The first table is a representation of '# of products sold' in each category by state. So NJ sold 0 books.

The next table is my color code look up table.

I need a formula that will highlight cells B2:D4 (in my first sheet) with the corresponding color code when the '# of products sold' = 0

Any idea if this is possible in Excel?

Thanks!!

Sheet1:

| A | B | C | D
1 | Category | NJ | PA | NY
2 | Books | 0 | 3 | 2
3 | Clothes | 5 | 0 | 4
4 | Electronics | 1 | 2 | 0


Sheet2:

| A | B | C
1| Category | State| Color Code
2| Books | NJ | Red
3| Clothes | NJ | Blue
4| Electronics | NJ | Green
5| Books | PA | Green
6| Clothes | PA | Red
7| Electronics | PA | Red
8| Books | NY | Blue
9| Clothes | NY | Blue
10| Electronics| NY | Green

[63falcondude]

Hello and welcome to the forum.

In order to do with without VBA, you will have to create one rule for each color on Sheet2. In this case, you have 3 colors so we will create 3 rules.

Rule 1:
Highlight B2:D4 of Sheet1 > Conditional Formatting > New Rule > Use a formula
=AND(B2=0,INDEX(Sheet2!$C$2:$C$10,MATCH($A2&B$1,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,0))="Red")
Format: Fill Red > OK > OK

Rule 2:
Highlight B2:D4 of Sheet1 > Conditional Formatting > New Rule > Use a formula
=AND(B2=0,INDEX(Sheet2!$C$2:$C$10,MATCH($A2&B$1,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,0))="Blue")
Format: Fill Blue > OK > OK

Rule 3:
Highlight B2:D4 of Sheet1 > Conditional Formatting > New Rule > Use a formula
=AND(B2=0,INDEX(Sheet2!$C$2:$C$10,MATCH($A2&B$1,Sheet2!$A$2:$A$10&Sheet2!$B$2:$B$10,0))="Green")
Format: Fill Green > OK > OK

[bebo021999]

Creat 3 independent formulas for 3 colours.

See attachment.

[uPhone]

Genius!! Thank you guys!!!!

[63falcondude]

You're welcome. Glad we could help.