如何用Excel按同一条件查找不同值？求适用公式

示例数据 #

Sheet1 - Kollekte Ontvang #

列1          列2          列3
日期        ProjectCodes  Amount

01/11/2024   G172          1000
01/12/2024   G172          1100
01/10/2025   G172          1150

Sheet2（目标输出表） #

列1    列2   列3   列4
G172   1000  1100  1150

需求 #

需要用Excel公式将Sheet1中ProjectCodes为G172的Amount值，按日期对应关系横向填充到Sheet2的指定列中。

已尝试的公式 #

• 公式1：

=IFERROR(XLOOKUP(L$2&$C3,'KOLLEKTE ONTVANG'!$E:$E&'KOLLEKTE ONTVANG'!$H:$H,'KOLLEKTE ONTVANG'!$S:$S),0)

• 公式2：

=SUMIFS('KOLLEKTE ONTVANG'!$S:$S,'KOLLEKTE ONTVANG'!$E:$E,S$2,'KOLLEKTE ONTVANG'!$H:$H,Opsomming!$C3,'KOLLEKTE ONTVANG'!$A:$A,">=01/10/2024",'KOLLEKTE ONTVANG'!$A:$A,"<=28/02/2025")

解决方案 #

根据需求，推荐以下两种适配不同Excel版本的方案：

方案1：INDEX+MATCH（兼容旧版Excel） #

假设Sheet2中G172在单元格A2，需在B2、C2、D2分别提取对应日期的Amount值：

• B2（匹配01/11/2024的Amount）：

=INDEX('KOLLEKTE ONTVANG'!$C:$C,MATCH(1,('KOLLEKTE ONTVANG'!$B:$B=$A2)*('KOLLEKTE ONTVANG'!$A:$A=DATE(2024,11,1)),0))

• C2（匹配01/12/2024的Amount）：

=INDEX('KOLLEKTE ONTVANG'!$C:$C,MATCH(1,('KOLLEKTE ONTVANG'!$B:$B=$A2)*('KOLLEKTE ONTVANG'!$A:$A=DATE(2024,12,1)),0))

• D2（匹配01/10/2025的Amount）：

=INDEX('KOLLEKTE ONTVANG'!$C:$C,MATCH(1,('KOLLEKTE ONTVANG'!$B:$B=$A2)*('KOLLEKTE ONTVANG'!$A:$A=DATE(2025,10,1)),0))

注意：旧版Excel需按Ctrl+Shift+Enter作为数组公式输入

方案2：XLOOKUP/FILTER（Excel 365/2021及以上版本） #

场景1：Sheet2列标题为对应日期

若Sheet2的B1、C1、D1分别为01/11/2024、01/12/2024、01/10/2025，可在B2输入通用公式后向右拖拽：

=XLOOKUP(1,('KOLLEKTE ONTVANG'!$B:$B=$A2)*('KOLLEKTE ONTVANG'!$A:$A=B$1),'KOLLEKTE ONTVANG'!$C:$C,0)

场景2：按顺序提取该项目所有Amount值

无需日期标题，直接按Sheet1中该项目的Amount顺序横向填充：
在B2输入公式后向右拖拽：

=INDEX(FILTER('KOLLEKTE ONTVANG'!$C:$C,'KOLLEKTE ONTVANG'!$B:$B=$A2),COLUMN(A:A))

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