如何在Excel中通过双姓名子串匹配返回对应学号？

假设你的数据源表格（表1）结构如下：

• Sheet1：A列=学号，B列=姓，C列=名

查询表格（表2）结构：

• Sheet2：A列=待匹配的名（子串），B列=待匹配的姓（子串），需在C列返回对应学号

以下是两种可行的实现方法：

方法1：Excel 365/2021 动态数组方案（推荐） #

在Sheet2的C2单元格输入公式，下拉填充：

=IFERROR(FILTER(Sheet1!$A:$A,ISNUMBER(SEARCH(Sheet2!A2,Sheet1!$C:$C))*ISNUMBER(SEARCH(Sheet2!B2,Sheet1!$B:$B)),"无匹配"),"无匹配")

• 逻辑说明：
  • SEARCH(Sheet2!A2,Sheet1!$C:$C)：检查表1的名是否包含表2当前行的名字串，返回匹配位置或错误值
  • ISNUMBER(...)：将匹配位置转为TRUE，错误值转为FALSE
  • 两个条件相乘等价于AND逻辑，仅当两个子串都匹配时返回TRUE
  • FILTER提取所有符合条件的学号，无匹配时返回指定文本
  • 若仅需第一个匹配结果，可嵌套INDEX：=IFERROR(INDEX(FILTER(...),1),"无匹配")

方法2：兼容旧版Excel的数组公式方案 #

在Sheet2的C2单元格输入公式，旧版Excel需按Ctrl+Shift+Enter确认输入，新版直接回车即可，下拉填充：

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(1,ISNUMBER(SEARCH(Sheet2!A2,Sheet1!$C:$C))*ISNUMBER(SEARCH(Sheet2!B2,Sheet1!$B:$B)),0)),"无匹配")

• 逻辑说明：
  • MATCH(1,...)：查找第一个同时满足两个子串匹配条件的行号（条件相乘后为1即代表双条件成立）
  • INDEX根据行号提取对应学号
  • IFERROR处理无匹配时的错误提示

额外注意事项 #

• 若需区分大小写的子串匹配，将SEARCH替换为FIND函数
• 若存在多条匹配记录，方法1的FILTER会返回所有结果，方法2仅返回第一条
• 建议限制数据范围（比如用Sheet1!$A$2:$A$1000代替整列），提升公式运行效率

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