Conditional formatting with named ranges

Question

I'm trying to format table cells when the first row value and header column matches values from a named range in Google Sheets. I have solved it as a formula with TRUE and FALSE, but I can't adopt it for the conditional formatting.

Example Sheet: https://docs.google.com/spreadsheets/d/1MpViBGIOYoeMpB2zpb81hwlNOJcShP86OHYlI92mKGA/edit?usp=sharing

My source table: --> named ranges "mapping_name" and "mapping"

+-------+--------+---------+-----------+
|       |        |         |           |
+-------+--------+---------+-----------+
| name1 | Monday | Tuesday |           |
| name2 | Monday |         | Wednesday |
| name3 |        |         |           |
+-------+--------+---------+-----------+

My table to be formatted:

+-------+--------+---------+-----------+--+--+
|       | Monday | Tuesday | Wednesday |  |  |
+-------+--------+---------+-----------+--+--+
| name1 | True   | True    | True      |  |  |
| name2 | True   | True    | False     |  |  |
| name3 | False  | False   | False     |  |  |
+-------+--------+---------+-----------+--+--+

My formula looks like this

=OR(ARRAYFORMULA((B$34 = index(mapping;match($A35;mapping_name;0))) * ($A35 = index(mapping_name;match($A35;mapping_name;0)))))

B34 is the header with weekdays A35 is the name column

The formula works, but I can't translate it to a format that works for conditional formatting. Any help would be appreciated!

Answer 1

In your 'expected result' table, I used this formula for conditional formatting

=ISNUMBER(MATCH($A18&"_"&B$17, ArrayFormula(TRANSPOSE(SPLIT(TEXTJOIN("@", 1, $A$4:$A$6&"_"&$B$4:$D$6), "@"))), 0))

Or, if you want to used the named ranges, try

=ISNUMBER(MATCH($A18&"_"&B$17, ArrayFormula(TRANSPOSE(SPLIT(TEXTJOIN("@", 1, INDIRECT("mapping_name")&"_"&INDIRECT("mapping")), "@"))), 0))

(Note the use of quotation marks around the named ranges.)

See if that works for you?