Google Sheet中带大于等于条件的Index Match多维度查找及动态列适配问题

核心需求回顾 #

• 基于商品和订单序号，返回对应采购单号，支持大于等于条件匹配（如订单号7、12均返回同一采购单号）
• Table 1新增列时，公式无需修改即可自动适配

原公式问题分析 #

1. 输入12返回#N/A：原公式中MATCH使用了匹配模式-1，该模式要求查找范围为降序排列，若数量列是升序则无法匹配，导致报错。
2. 新增列不识别：通过INDIRECT硬编码了B-D列范围，未使用动态范围，新增列无法被公式纳入计算。

解决方案（两种可选） #

方案1：使用结构化表格+XLOOKUP（推荐） #

1. 先将Table 1转为结构化表格：选中Table 1所有数据 → 右键 → 创建表格（默认名称为Table1）
2. 在Table 2的C2单元格输入公式：

=XLOOKUP(B2, INDEX(FILTER(Table1, Table1[商品]=A2),,2:COLUMNS(Table1)), Table1[#Headers],,1)

公式说明：

• FILTER(Table1, Table1[商品]=A2)：筛选出当前商品对应的行
• INDEX(...,2:COLUMNS(Table1))：提取筛选行中所有数量列（从第2列到最后一列）
• Table1[#Headers]：调用Table 1的表头（采购单号）
• XLOOKUP(...,1)：匹配模式1表示查找大于等于目标值的最小项，完美适配订单号7、12返回同一采购单号的需求
• 新增列时，结构化表格会自动扩展范围，公式无需修改

方案2：使用INDEX+MATCH动态范围（无需结构化表格） #

直接在Table 2的C2输入公式：

=INDEX('Table 1'!1:1, MATCH(B2, INDEX('Table 1'!A:Z, MATCH(A2, 'Table 1'!A:A,0), 2:COLUMNS('Table 1'!A:Z)), 1))

公式说明：

• MATCH(A2, 'Table 1'!A:A,0)：定位当前商品在Table 1中的行号
• INDEX('Table 1'!A:Z, 行号, 2:COLUMNS(...))：提取该行所有数量列（动态覆盖新增列）
• MATCH(B2, ...,1)：升序模式下查找大于等于目标值的最小项，返回对应列号
• INDEX('Table 1'!1:1, 列号)：返回对应的采购单号

验证效果 #

• 输入apple（A2）、7（B2）→ C2返回purchasing no.2
• 输入apple（A2）、12（B2）→ C2返回purchasing no.2
• Table 1新增E列后，公式自动纳入E列数据，无需修改即可输出正确结果

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