Google表格条件格式求助：扩展Xlookup/Find实现假期责任人高亮

问题描述 #

• 工作表1（命名为「休假表」）：行存储员工姓名，列标题为dd/mm/yyyy格式的日期，员工当日休假则对应单元格标记为H
• 工作表2（命名为「任务跟踪表」）：包含「责任人」「日期」「修订到期日」字段
• 需求：为工作表2的「责任人」单元格设置条件格式，当该责任人在「日期」或「修订到期日」当天处于休假状态时，单元格高亮红色
• 已尝试XLOOKUP和FIND函数，但仅部分生效，无法覆盖数组搜索与双日期判断逻辑

解决方案 #

自定义条件格式公式 #

选中工作表2的「责任人」列数据区域（比如A2:A，根据实际数据范围调整），打开条件格式设置面板，选择「自定义公式」，输入以下公式：

=OR(INDEX(休假表!$B$2:$ZZ, MATCH(A2, 休假表!$A$2:$A, 0), MATCH(B2, 休假表!$B$1:$ZZ$1, 0))="H", INDEX(休假表!$B$2:$ZZ, MATCH(A2, 休假表!$A$2:$A, 0), MATCH(C2, 休假表!$B$1:$ZZ$1, 0))="H")

公式逻辑说明 #

1. MATCH(A2, 休假表!$A$2:$A, 0)：定位当前责任人在「休假表」中的行号
2. MATCH(B2, 休假表!$B$1:$ZZ$1, 0)：定位「日期」字段对应的「休假表」列号；MATCH(C2, ...)则定位「修订到期日」的列号
3. INDEX(休假表!$B$2:$ZZ, 行号, 列号)：取出「休假表」中对应责任人+对应日期的单元格值
4. OR(..., ...)：只要「日期」或「修订到期日」任意一天该责任人标记为H，就触发高亮规则

注意事项 #

• 确保「休假表」的日期列标题格式（dd/mm/yyyy）和工作表2的「日期」「修订到期日」格式完全一致，否则MATCH会匹配失败
• 根据实际数据范围调整公式中的单元格区域：比如「休假表」的姓名列如果是A1:A100，就把休假表!$A$2:$A改成休假表!$A$1:$A$100；日期列标题如果到Z1，就把$B$1:$ZZ$1改成$B$1:$Z$1
• 若存在重名员工，建议在两张表中使用唯一标识（比如工号）匹配，避免MATCH返回错误结果

内容的提问来源于stack exchange，提问作者Eva DUB-KAKOSOVA