如何通过电子表格公式判断值在多单元格存在并实现排班条件格式

问题背景 #

现有两个表格：

• 按日期排列的员工排班表
• 员工与主工作站点的分配表（Staffing Roster）

需求：为排班表中员工单元格按站点着色（披萨站标蓝色，冰淇淋站标粉色），且不新增辅助列。

可行公式方案 #

方案1：VLOOKUP直接判断 #

适用于站点分配表中员工列（A列）为唯一值的场景：

• 披萨站条件格式公式：

  =VLOOKUP(B1, 'Staffing Roster'!$A$1:$B$72, 2, FALSE)="Pizza"
  

• 冰淇淋站条件格式公式：

  =VLOOKUP(B1, 'Staffing Roster'!$A$1:$B$72, 2, FALSE)="Ice Cream"
  

方案2：INDEX+MATCH（更可靠，不受排序限制） #

如果站点分配表的员工列未排序，推荐使用此公式：

• 披萨站条件格式公式：

  =INDEX('Staffing Roster'!$B$1:$B$72, MATCH(B1, 'Staffing Roster'!$A$1:$A$72, 0))="Pizza"
  

• 冰淇淋站条件格式公式：

  =INDEX('Staffing Roster'!$B$1:$B$72, MATCH(B1, 'Staffing Roster'!$A$1:$A$72, 0))="Ice Cream"
  

处理姓名匹配不一致的情况 #

若员工姓名存在大小写、空格差异，用EXACT函数强制精确匹配：

=EXACT(INDEX('Staffing Roster'!$B$1:$B$72, MATCH(B1, 'Staffing Roster'!$A$1:$A$72, 0)), "Pizza")

之前方案报错的原因 #

1. LOOKUP函数误用：LOOKUP要求查找范围必须升序排列，原公式中返回范围用整列B:B易导致计算异常，且未正确使用绝对引用。
2. 绝对引用缺失：站点分配表的单元格范围未加$锁定，导致条件格式应用到其他单元格时范围偏移。
3. 整列引用问题：使用B:B这类整列引用会增加计算负荷，且容易引入无效数据干扰判断。

操作注意事项 #

• 应用条件格式时，需选中排班表中所有需要着色的员工单元格，公式中的B1要对应选中区域的左上角单元格。
• 站点分配表的范围尽量用精确的单元格区域（如$A$1:$B$72），避免整列引用。

内容的提问来源于stack exchange，提问作者Amanda