Multiple Criteria Conditional Format

[MrCameronExcel]

Hello,

I would like to apply conditional formatting into a matrix based on multiple criteria and table values.

The below matrix of flat numbers is where I would like conditional formatting applied to. The second table below is where I need to cross reference data.

Criteria:
If the Block (Table 2) matches the Block (Table 1), and the Flat (Table 2) matches the Flat (Table 1 - matrix), then conditional format the flat number in the matrix (Table 1) based on the number in the Helper Column for Conditional Formatting (Table 2).

Any help or pointers is much appreciated!

Thank you

TABLE 1

Block A
Floor	Flat No.
1st	1	2	3	4	5	6	7
2nd	8	9	10	11	12	13	14
3rd	15	16	17	18	19	20	21

TABLE 2

Survey	Helper Column for Conditional Formatting	Block	Flat
No Survey Info	1	Block A	1​
Potential ACM	2	Block A	2​
Sufficient Clear	3	Block A	3​
Sufficient Clear	3	Block A	4​
Sufficient Clear	3	Block A	5​
Sufficient Clear	3	Block B	1​
Sufficient Clear	3	Block B	2​
No Survey Info	1	Block B	3​
Potential ACM	2	Block B	4​
Sufficient Clear	3	Block B	5​

 

[MrCameronExcel]

I achieved the correct result in a column returning True/False using the formula below, but for some reason the same formula didn't work when used for conditional formatting all the relevant cells in the matrix

=AND(COUNTIF($J$6:$P$28,$E2),$D2="Bray", $B2=3)

Note* the exact column or row is just based on how I have the tables shown on my spreadsheet

 

[MrCameronExcel]

Solved it.

Used the below formula (specific to my spreadsheet).

=IF(INDEX($B$2:$B$642, MATCH("Block A" & I6, $D$2:$D$642 & $E$2:$E$642,0))=3,1,0)