如何在Excel中以动态年月列值为搜索参数执行XLOOKUP

核心思路 #

利用表格中动态更新的“月份”和“年份”列，拼接出与月度数据列标题一致的格式，再通过XLOOKUP完成列定位与数据提取。

场景1：获取指定项目ID对应的当前月份数据 #

假设表格结构：

• A列：月份
• B列：年份
• C列：项目ID
• D~O列：月度数据列（标题格式如1-23/11-2023）

公式示例：

=XLOOKUP("15023", $C:$C, XLOOKUP(TEXT(DATE($B$2,$A$2,1),"m-yy"), $D$1:$O$1, $D:$O))

公式解释：

1. TEXT(DATE($B$2,$A$2,1),"m-yy")：固定取表格中更新后的年月（这里用第一行的B2、A2，因为所有行的年月统一更新），生成与列标题一致的格式（如11-23）；若列标题是完整年份格式（如12-2023），则把格式改为"m-yyyy"。
2. 内层XLOOKUP：根据生成的标题，定位到对应的月度数据整列。
3. 外层XLOOKUP：根据指定的项目ID（如15023），从定位到的列中提取对应的数据。

场景2：获取当前行项目对应的当前月份数据 #

如果要直接提取当前行项目的当月数据，公式更简洁：

=XLOOKUP(TEXT(DATE(B2,A2,1),"m-yy"), $D$1:$O$1, D2:O2)

公式解释：

• 用当前行的年月拼接成列标题格式，直接在当前行的月度数据单元格范围（D2~O2）中匹配标题并返回对应值。

注意事项 #

• 列标题区域（如$D$1:$O$1）需用绝对引用，避免下拉公式时引用偏移。
• 确保拼接的格式与实际列标题完全一致（比如列标题是11-23就用m-yy，是11-2023就用m-yyyy）。

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