如何基于Sheet1 A列值匹配Sheet2 B、C列区间返回对应D列值

问题核心 #

你之前的公式仅对比相同行号的行列数据，没有遍历Sheet2全量区间行，因此无法实现全表扫描匹配。

方案1：Excel 365/2021 动态数组公式（一键输出所有结果） #

直接在新工作表任意空白单元格输入以下公式，按回车后所有匹配结果会自动溢出生成，无需手动下拉：

=TOCOL(MAKEARRAY(COUNTA(Sheet1!A:A),COUNTA(Sheet2!D:D),LAMBDA(r,c,IF(AND(INDEX(Sheet1!A:A,r)>=INDEX(Sheet2!B:B,c),INDEX(Sheet1!A:A,r)<INDEX(Sheet2!C:C,c)),INDEX(Sheet2!D:D,c),NA()))),2)

公式逻辑：先遍历Sheet1所有A列值、Sheet2所有区间行，生成匹配结果矩阵，再剔除无匹配的空值，最终输出符合要求的所有D列值。

方案2：旧版Excel 数组公式 #

如果使用的是2019及更早版本无动态数组功能，按以下步骤操作：

1. 先在新工作表A1单元格输入公式：

=IFERROR(INDEX(Sheet2!$D:$D,SMALL(IF((Sheet1!$A2>=Sheet2!$B$2:$B$1000)*(Sheet1!$A2<Sheet2!$C$2:$C$1000),ROW($2:$1000),99999),ROW(A1))),"")

2. 输入完成后按下Ctrl+Shift+Enter三键组合确认数组公式
3. 下拉单元格直到出现空白，即可得到Sheet1 A2单元格对应的所有匹配结果
4. 如需匹配A列其他单元格，修改公式中Sheet1!$A2为对应单元格位置即可

方案3：Power Query 批量处理（适合万行以上大数据量） #

无需写复杂公式，可视化操作即可实现循环匹配：

• 依次将Sheet1、Sheet2的数据导入Power Query：点击「数据」选项卡→「从表格/区域」，导入后均选择「仅创建连接」
• 打开Sheet1对应的Power Query查询，点击「添加列」→「自定义列」，输入公式：

Table.SelectRows(Sheet2的查询名称, (x)=> [A] >= x[B] and [A] < x[C])

• 点击自定义列右上角的展开按钮，仅保留D列数据，删除空行后点击「关闭并上载」到新工作表即可

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