Excel双向查找：先匹配科目再查找首个超阈值数值

需求说明 #

• 输入两个查找值：科目名称（如“Maths”）和指定数值（如6.5）
• 先精确匹配科目对应的行，再在该行中找到第一个大于指定数值的结果（表格每行数值均为递增排序）
• 示例：输入Maths、6.5时输出6.6；输入Art、3.6时输出3.67
• 当前问题：已能实现单行数值查找，但无法整合先文本匹配再数值筛选的双向逻辑

参考表格 #

实现方案 #

方案1：XLOOKUP嵌套（适用于Excel 365/2021及以上版本）

假设科目输入单元格为G1，指定数值输入单元格为G2，科目数据区域为A2:A4，数值区域为B2:E4，公式如下：

=XLOOKUP(TRUE,XLOOKUP(G1,A2:A4,B2:E4)>G2,XLOOKUP(G1,A2:A4,B2:E4))

逻辑说明：

1. 内层XLOOKUP(G1,A2:A4,B2:E4)：精准匹配科目名称，返回对应整行的数值数组
2. 通过>G2生成布尔数组，标记数组中大于指定值的元素
3. 外层XLOOKUP(TRUE,...)：定位第一个TRUE对应的数值，即为目标结果

方案2：INDEX+MATCH组合（兼容所有Excel版本）

同样基于上述单元格和区域定义，公式如下：

=INDEX(INDEX(B2:E4,MATCH(G1,A2:A4,0),0),MATCH(TRUE,INDEX(B2:E4,MATCH(G1,A2:A4,0),0)>G2,0))

逻辑说明：

1. MATCH(G1,A2:A4,0)：获取科目对应的行号
2. INDEX(B2:E4,行号,0)：提取该行的所有数值
3. MATCH(TRUE, 提取数值>G2,0)：找到该行中首个大于指定值的列位置
4. 外层INDEX：根据列位置提取最终结果

补充处理 #

如果指定数值大于该行所有数值，公式会返回#N/A，可使用IFNA函数添加兜底提示：

=IFNA(XLOOKUP(TRUE,XLOOKUP(G1,A2:A4,B2:E4)>G2,XLOOKUP(G1,A2:A4,B2:E4)),"无符合条件的值")

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