
答案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