Google Sheets：按公司ID与日期匹配转置open amount至对应列

问题分析 #

你之前用VLOOKUP、CHOOSE报错“值超出有效范围”，大概率是因为VLOOKUP要求查找值必须在查询区域的第一列，且第三参数（返回列索引）超出了查询区域的列数；用CHOOSE时如果没配合数组公式，单个单元格无法处理多列匹配逻辑，导致范围错误。

以下是3种靠谱的解决方法，直接套用即可：

方法1：INDEX+MATCH 精准匹配（适合手动构建表头） #

假设原始数据在原始数据标签页：

• A列：idCompany
• B列：MM-YYYY
• C列：open amount

新标签页的设置：

1. A列用=UNIQUE(原始数据!A:A)生成唯一idCompany列表（从A2开始，手动输入也可）
2. 第一行用=TRANSPOSE(UNIQUE(原始数据!B:B))生成唯一MM-YYYY列头（从B1开始，手动输入也可）

在新标签页的B2单元格输入公式，下拉+右拉填充：

=IFERROR(INDEX(原始数据!$C:$C, MATCH($A2&B$1, 原始数据!$A:$A&原始数据!$B:$B, 0)), "NULL")

• 原理：用$A2&B$1拼接id和日期作为匹配键，在原始数据的id+日期拼接列里找位置，返回对应open amount；找不到则显示NULL。
• 若想一键填充整个区域，套上ARRAYFORMULA：

=ARRAYFORMULA(IFERROR(INDEX(原始数据!$C:$C, MATCH($A2:$A&B$1:$1, 原始数据!$A:$A&原始数据!$B:$B, 0)), "NULL"))

方法2：QUERY+PIVOT 一键生成交叉表（最省心） #

直接在新标签页的A1单元格输入公式，自动生成带表头的完整交叉表：

=ARRAYFORMULA(IFERROR(QUERY(原始数据!A:C, "SELECT A, SUM(C) WHERE A IS NOT NULL PIVOT B LABEL A 'idCompany'", 1), "NULL"))

• 原理：PIVOT B直接把MM-YYYY转成列，SUM(C)用于聚合数据（如果每个id+日期只有一条数据，SUM/MAX/MIN结果都一样）；LABEL A 'idCompany'自定义行表头；IFERROR把空值替换成NULL。
• 优势：不用手动构建id和日期列表，公式自动生成所有内容，原始数据更新后自动同步。

方法3：动态数组函数（适合新版Google Sheets） #

如果你的Google Sheets支持动态数组，用XLOOKUP配合UNIQUE更简洁：

1. 生成id列表：=UNIQUE(原始数据!A:A)（A2开始）
2. 生成日期列头：=TRANSPOSE(UNIQUE(原始数据!B:B))（B1开始）
3. B2单元格公式：

=ARRAYFORMULA(IFERROR(XLOOKUP($A2:$A&B$1:$1, 原始数据!$A:$A&原始数据!$B:$B, 原始数据!$C:$C, "NULL"), "NULL"))

• XLOOKUP比VLOOKUP更灵活，支持任意位置的查找值，不用局限于第一列。

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