Excel：单元格含指定子串时如何返回Table2中的对应目标值

核心思路 #

通过子串匹配定位列 + 行标题匹配定位行，结合INDEX/MATCH/SEARCH函数实现精准取值。

公式实现（分版本） #

假设Table1的Variable列为A列，aaaa/bbbb/cccc列为B/C/D列；Table2的表头为第1行，行标题为第1列（即aaaa在Table2的A2单元格）。

1. Excel 365/2021（动态数组版本）

在Table1的B2单元格输入以下公式，直接回车后会自动填充整列：

=INDEX(Table2,MATCH(B$1,Table2[#All],0),MATCH(TRUE,ISNUMBER(SEARCH(Table2[#Headers],$A2)),0))

2. 旧版Excel（非动态数组）

在Table1的B2单元格输入相同公式，然后按Ctrl+Shift+Enter触发数组计算，再手动下拉填充：

=INDEX(Table2,MATCH(B$1,Table2[#All],0),MATCH(TRUE,ISNUMBER(SEARCH(Table2[#Headers],$A2)),0))

公式拆解 #

• SEARCH(Table2[#Headers],$A2)：遍历Table2的所有表头，检查是否为当前Variable单元格（A2）的子串，返回匹配位置或错误值
• ISNUMBER(...)：将匹配位置转为TRUE，错误值转为FALSE，得到布尔数组
• MATCH(TRUE,...)：定位第一个TRUE的位置，即对应金属的列号
• MATCH(B$1,Table2[#All],0)：找到当前列标题（如aaaa）在Table2中的行号
• INDEX(Table2,行号,列号)：根据行号和列号返回Table2中对应的目标值

注意事项 #

• 若Variable单元格包含多个金属子串，公式会返回第一个匹配的对应值
• SEARCH函数不区分大小写，但若金属名称拼写不一致（如大小写之外的差异）会导致匹配失败

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