Excel中如何用MAX(INDEX获取匹配查询条件的右侧单元格数据
2026-7-15
Excel：根据查询条件提取最大日期对应的成本数据
我需要在Excel中根据Sheet2的search query，查找Sheet1中对应查询条件的最大日期，并获取该日期所在行右侧相邻的成本数据。尝试使用公式 =OFFSET(MAX(INDEX((D16='Sheet1'!B$2:B$7500)*'Sheet1'!C$2:C$7500,)),0,1) 时出现引用错误，无法提取目标数据。
样本数据
Sheet1样本数据
| search query | date | cost |
|---|---|---|
| 100336288 | 11 July 2023 | -£200.00 |
| 100025143 | 11 July 2023 | -£50.00 |
| 100301369 | 11 July 2023 | -£5,771.40 |
| 100280645 | 11 July 2023 | -£500.00 |
| 100138224 | 11 July 2023 | -£50.00 |
| 100336288 | 30 June 2023 | -£3,000.00 |
| 100336288 | 26 June 2023 | -£999.51 |
| 100280645 | 20 June 2023 | -£167.36 |
| 100280645 | 20 June 2023 | -£332.64 |
| 100025143 | 14 June 2023 | -£50.00 |
| 100138224 | 12 June 2023 | -£50.00 |
| 100336288 | 07 June 2023 | -£200.00 |
| 100336288 | 06 June 2023 | -£700.00 |
| 100336288 | 02 June 2023 | -£1,500.00 |
| 100301369 | 02 June 2023 | -£3,000.00 |
| 100138224 | 12 May 2023 | -£50.00 |
| 100336288 | 09 May 2023 | -£200.00 |
| 100025143 | 08 May 2023 | -£50.00 |
| 100336288 | 04 May 2023 | -£700.00 |
| 100336288 | 13 April 2023 | -£200.00 |
| 100025143 | 12 April 2023 | -£50.00 |
| 100336288 | 04 April 2023 | -£700.00 |
| 100336288 | 13 March 2023 | -£804.44 |
| 100025143 | 11 March 2023 | -£50.00 |
| 100336288 | 07 March 2023 | -£700.00 |
| 100336288 | 06 March 2023 | -£200.00 |
| 100025143 | 10 February 2023 | -£50.00 |
| 100336288 | 06 February 2023 | -£200.00 |
| 100336288 | 06 February 2023 | -£982.00 |
| 100336288 | 06 February 2023 | -£700.00 |
| 100025143 | 17 January 2023 | -£50.00 |
| 100336288 | 09 January 2023 | -£200.00 |
| 100025143 | 06 January 2023 | -£50.00 |
| 100336288 | 05 January 2023 | -£982.00 |
Sheet2样本数据（原公式已移除等号）
| search query | Date公式 |
|---|---|
| 100025143 | MAX(INDEX((A2=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100138224 | MAX(INDEX((A3=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100280645 | MAX(INDEX((A4=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100301369 | MAX(INDEX((A5=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100336288 | MAX(INDEX((A6=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100283819 | MAX(INDEX((A7=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100281696 | MAX(INDEX((A8=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100149591 | MAX(INDEX((A9=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
| 100099054 | MAX(INDEX((A10=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,)) |
问题原因
你之前用的公式错误在于：MAX(INDEX(...))返回的是日期的数值结果，不是单元格的引用位置，因此OFFSET无法基于这个数值进行偏移操作，导致引用错误。
解决方案
方法1：XLOOKUP（适用于Excel 365/2021及以上版本）
在Sheet2的成本列（比如C2单元格）输入以下公式，下拉填充即可：
=XLOOKUP(MAX(INDEX((A2=Sheet1!A$2:A$7500)*Sheet1!B$2:B$7500,)),Sheet1!B$2:B$7500,Sheet1!C$2:C$7500,"无数据",0,1)
- 如果同一查询条件下有多个相同的最大日期，公式会返回最后一个匹配行的成本；若要返回第一个匹配行，把公式最后一个参数
1改成-1。 "无数据"是无匹配结果时的提示文本，可根据需求修改。
方法2：INDEX+MATCH组合（兼容所有Excel版本）
如果使用旧版Excel，可使用以下公式：
=IFERROR(INDEX(Sheet1!C$2:C$7500,MATCH(MAX(INDEX((A2=Sheet1!A$2:A$7500)*Sheet1!B$2:B$7500,)),Sheet1!B$2:B$7500,0)),"无数据")
- 这个公式会返回第一个匹配最大日期行的成本。
IFERROR用来处理无匹配结果的情况，避免显示错误值。
内容的提问来源于stack exchange，提问作者Kyle Johal-Hansen
相关产品推荐
相关产品推荐
