Excel求助：如何用Lookup/Match实现颜色代码到对应颜色类别的查询？

问题背景 #

你的表格结构为：A1:L1是颜色类别（如Red、Brown），A2:L67为对应颜色代码（多数单元格为空），存在部分代码分类不符合认知的情况（如222被归在Brown列，但实际应属于Red）。需求是输入任意颜色代码，自动返回所属类别，替代手动Ctrl+F查找。

方法1：INDEX+SUMPRODUCT组合（兼容所有Excel版本） #

假设待查代码放在A70，在目标单元格输入以下公式：

=INDEX($A$1:$L$1,SUMPRODUCT(MAX(($A$2:$L$67=A70)*COLUMN($A$2:$L$67)))-COLUMN($A$1)+1)

逻辑说明：

• ($A$2:$L$67=A70)*COLUMN(...)：定位匹配代码所在的列号，未匹配项返回0
• MAX(...)：提取有效匹配的列号（忽略空单元格）
• INDEX(...)：根据列号返回对应表头的颜色类别

分类错误修正方案：

新增一张修正表（例如N列存需修正的代码，O列存正确颜色），用IFERROR优先调用修正结果：

=IFERROR(VLOOKUP(A70,$N$2:$O$100,2,FALSE),INDEX($A$1:$L$1,SUMPRODUCT(MAX(($A$2:$L$67=A70)*COLUMN($A$2:$L$67)))-COLUMN($A$1)+1))

方法2：XLOOKUP函数（适用于Excel 365/2021及以上） #

XLOOKUP支持跨区域按列查找，语法更简洁：

=XLOOKUP(A70,$A$2:$L$67,$A$1:$L$1,"未找到",0,2)

参数说明：

• 第1参数：待查代码（如A70）
• 第2参数：所有颜色代码的区域
• 第3参数：颜色类别表头区域
• "未找到"：无匹配时的提示文本
• 0：精确匹配
• 2：按列方向查找

同样可嵌套修正表：

=IFERROR(VLOOKUP(A70,$N$2:$O$100,2,FALSE),XLOOKUP(A70,$A$2:$L$67,$A$1:$L$1,"未找到",0,2))

方法3：数组公式（旧版Excel需按Ctrl+Shift+Enter） #

=INDEX($A$1:$L$1,MATCH(TRUE,$A$2:$L$67=A70,0))

注意：旧版本Excel输入后需按Ctrl+Shift+Enter触发数组运算，新版本直接回车即可。

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