如何编写公式实现输入姓名/科目自动匹配对应学生分数

场景1：一维数据列表（姓名、科目、分数逐行存储） #

假设数据存于Sheet1!A:C，A列=姓名，B列=科目，C列=分数。

多条件精确匹配（同时输入姓名+科目） #

• 兼容全Excel版本的INDEX+MATCH数组公式：

  =INDEX(Sheet1!$C:$C,MATCH(1,(Sheet1!$A:$A=D1)*(Sheet1!$B:$B=E1),0))
  

  注：旧版Excel需按Ctrl+Shift+Enter确认，新版直接回车。
• 新版Excel简化版XLOOKUP：

  =XLOOKUP(1,(Sheet1!$A:$A=D1)*(Sheet1!$B:$B=E1),Sheet1!$C:$C,"无匹配结果")
  

单条件匹配（仅输入姓名/科目） #

• 输入姓名（D1），提取该生所有分数：

  =FILTER(Sheet1!$C:$C,Sheet1!$A:$A=D1,"无匹配")
  

• 输入科目（E1），提取该科目所有分数：

  =FILTER(Sheet1!$C:$C,Sheet1!$B:$B=E1,"无匹配")
  

场景2：二维数据表格（行=姓名，列=科目，交叉单元格=分数） #

假设数据存于Sheet1!A1:E10，A列=姓名，第1行=科目。

精准定位分数 #

• INDEX+MATCH组合：

  =INDEX(Sheet1!$B:$E,MATCH(G1,Sheet1!$A:$A,0),MATCH(H1,Sheet1!$1:$1,0))
  

• XLOOKUP嵌套写法：

  =XLOOKUP(H1,Sheet1!$1:$1,XLOOKUP(G1,Sheet1!$A:$A,Sheet1!$B:$E,"无匹配"),"无匹配")
  

结合你提到的SEARCH/MATCH失败经历，推测是模糊关键词匹配+动态筛选场景，以下是解决方案：

模糊匹配关键词并提取分数 #

输入关键词（如“李”）后，自动提取所有含该关键词的姓名/科目对应的分数：

• 模糊匹配姓名：

  =FILTER(Sheet1!$C:$C,ISNUMBER(SEARCH(D1,Sheet1!$A:$A)),"无匹配")
  

• 模糊匹配科目：

  =FILTER(Sheet1!$C:$C,ISNUMBER(SEARCH(E1,Sheet1!$B:$B)),"无匹配")
  

  注：SEARCH不区分大小写，若需区分改用FIND。

下拉选择联动显示 #

如果动图是选择姓名后自动关联显示科目和分数：

1. 给目标单元格（如G1）设置数据验证，来源选择Sheet1!$A:$A（姓名列）；
2. 显示该生所有科目（用TEXTJOIN拼接）：

   =TEXTJOIN(", ",TRUE,FILTER(Sheet1!$B:$B,Sheet1!$A:$A=G1,"无匹配"))
   

3. 显示对应分数：

   =TEXTJOIN(", ",TRUE,FILTER(Sheet1!$C:$C,Sheet1!$A:$A=G1,"无匹配"))
   

• MATCH默认精确匹配（参数为0），若误用模糊匹配（1/-1）且数据未排序会出错；
• 单元格存在隐形空格：用TRIM清除，比如TRIM(Sheet1!$A:$A)=TRIM(D1)；
• 数组公式未正确确认：旧版Excel需按Ctrl+Shift+Enter触发数组运算。

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