Excel如何按赛事类型、组别、日期区间多条件匹配返回对应结果

问题场景 #

创建虚构示例数据集用于说明需求，数据集截图如下：

核心需求：将E列（RESULT列）的对应值提取填充至K列（Result列），待查询数据源格式示例如下：

Tennis      1        01/01/2020
Tennis      2        04/01/2021
Basketball  2        25/05/2018
Squash      2        11/09/2019
Football    1        18/02/2016

取值需同时满足3个匹配条件：

• 第一维度：匹配运动赛事类型，赛事类型不固定
• 第二维度：匹配参赛组别，组别不固定；当前示例仅设置2个组别简化演示，实际场景最多可存在6-7个参赛组别
• 第三维度：查询日期需落在对应记录C列（起始日期）与D列（结束日期）的区间范围内
  已知可使用LOOKUP函数实现单条件的日期区间匹配，但不清楚如何叠加前两个维度的匹配规则，且暂未掌握VBA使用方法，需要可实现该需求的非VBA解决方案。

可直接套用的公式方案 #

根据使用的Excel版本选择对应公式即可，无需编写VBA代码。

方案1：Excel 365/2021及以上版本（推荐，逻辑最清晰） #

在K2单元格输入以下公式，按回车后下拉填充整列：

=XLOOKUP(1,(A$2:A$1000=H2)*(B$2:B$1000=I2)*(C$2:C$1000<=J2)*(D$2:D$1000>=J2),E$2:E$1000,"无匹配")

说明：

• 公式里的A$2:A$1000对应原始数据的实际行范围，根据自身表格的实际行数修改即可，不建议直接引用整列避免计算卡顿
• 四个括号内的判断分别对应「运动类型匹配」「组别匹配」「查询日期晚于等于起始日期」「查询日期早于等于结束日期」，四个条件同时满足时乘积为1，XLOOKUP会定位到第一条符合条件的记录，返回对应E列的结果
• 最后一个参数是找不到符合条件记录时的返回值，可按需自行修改

方案2：兼容所有旧版Excel（2019及更早版本） #

在K2单元格输入以下公式，按回车（极老版本按Ctrl+Shift+Enter三键确认数组公式）后下拉填充：

=LOOKUP(1,0/((A$2:A$1000=H2)*(B$2:B$1000=I2)*(C$2:C$1000<=J2)*(D$2:D$1000>=J2)),E$2:E$1000)

说明：

• 条件判断逻辑和上述方案一致，不满足所有条件的记录在0/条件乘积计算时会返回#DIV/0!错误值，LOOKUP会自动忽略错误值，定位到符合条件的记录返回对应结果
• 注意：如果同一运动类型+同一组别下存在重叠的日期区间，公式会返回最后一个匹配区间的结果，这种情况提前把原始数据按C列起始日期升序排序即可保证结果正确。

大数据量优化方案（万行以上数据推荐） #

如果数据量超过1万行，公式计算会明显卡顿，可以用Power Query实现零代码自动匹配，后续数据更新只要点刷新就能同步结果：

1. 分别选中原始数据区域、待查询数据区域，按Ctrl+T将两者转为超级表
2. 在「数据」选项卡点击「从表格/区域」，将待查询表导入Power Query编辑器
3. 点击「合并查询」，匹配字段选择「运动类型」「组别」两列和原始表对应列关联，连接方式选左外部
4. 展开合并后的原始表字段，筛选出满足「起始日期<=查询日期<=结束日期」的记录，仅保留RESULT列
5. 点击「关闭并上载」即可生成匹配完成的结果表，后续原始数据更新后右键结果表选择「刷新」即可自动重算。

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