Excel中用INDEX与IF实现多条件查询（取第3个匹配值）

嘿，这个需求我之前帮朋友处理过，正好可以给你几个实用的解决方案，分不同Excel版本来适配：

适用于Excel 365/2021及以后版本（支持动态数组） #

这版本有更简洁的函数组合，推荐用FILTER+INDEX的写法，直观又好维护：

=IFERROR(INDEX(FILTER(Sheet1!Data[Value], (Sheet1!Data[ID]=@计算表!A2)*(Sheet1!Data[CODE]="TEST2")), 3), "无匹配值")

公式拆解： #

• (Sheet1!Data[ID]=@计算表!A2)*(Sheet1!Data[CODE]="TEST2")：先筛选出和当前行ID匹配且CODE为TEST2的所有行
• FILTER(...)：把符合条件的Value值提取出来，形成一个有序的动态数组（按原表顺序排列）
• INDEX(...,3)：取这个数组里的第3个元素，就是你要的目标值
• IFERROR(...)：如果符合条件的记录不足3条，会返回自定义提示（比如"无匹配值"），避免出现错误码

如果你习惯用XLOOKUP，也可以这么写：

=IFERROR(XLOOKUP(3, IF((Sheet1!Data[ID]=@计算表!A2)*(Sheet1!Data[CODE]="TEST2"), SEQUENCE(ROWS(Sheet1!Data))), Sheet1!Data[Value]), "无匹配值")

适用于Excel 2019及更早版本（不支持动态数组） #

旧版本没有动态数组函数，得用数组公式来实现，输入公式后要按Ctrl+Shift+Enter确认（不要直接回车）：

=IFERROR(INDEX(Sheet1!Data[Value], SMALL(IF((Sheet1!Data[ID]=计算表!A2)*(Sheet1!Data[CODE]="TEST2"), ROW(Sheet1!Data)-ROW(Sheet1!Data[#Headers])), 3)), "无匹配值")

公式拆解： #

• ROW(Sheet1!Data)-ROW(Sheet1!Data[#Headers])：计算Data表中每行相对于表头的行号（从1开始，避免表头行干扰）
• IF(..., 行号)：只保留符合条件的行号，不符合的返回FALSE
• SMALL(...,3)：从符合条件的行号里取出第3小的那个（也就是第3个匹配项的位置）
• INDEX：根据这个位置返回对应的Value值

注意事项 #

• 确保Sheet1里的Data是正式的Excel表格（插入→表格），这样结构化引用（比如Data[ID]）会自动适配表格的增减，比普通单元格引用更稳定
• 如果你的计算表不是正式表格，把公式里的@计算表!A2改成计算表!A2即可
• 要是需要获取第N个匹配值，直接把公式里的3改成对应的数字就行

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