答案1
从查找表获取 58 很容易:
=VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE)
然后,您可以添加条件格式规则“使用公式确定......”并使公式如下:
=(E4 >= VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE))
使用一条规则,您只能获得一种颜色。要获得 4 种不同的颜色,您必须为同一区域创建四条不同的条件格式规则:
=AND(E4 >= VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE), B4 = 1, C4 = 1) > red rule
=AND(E4 >= VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE), B4 = 1, C4 = 2) > yellow rule
=AND(E4 >= VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE), B4 = 2, C4 = 1) > beige rule
=AND(E4 >= VLOOKUP(B4, sheet1!$B$9:$E$10, 1+C4, FALSE), B4 = 2, C4 = 2) > green rule