求助：构建多条件搜索返回数值的Google Sheets VLOOKUP数组公式

问题概述 #

• 现有三个标签页：
  • Keyword Data：存储按Campaign、Ad Group、匹配类型、日期分段的关键词全量数据，仅部分行带转化记录
  • Conversion Data：仅包含产生转化的关键词数据，额外包含「转化类型」字段，此架构由Google Ads导出限制导致
  • Full Data Set：目标是保留Keyword Data全量数据，同时补充对应关键词当日的转化状态及具体转化类型，拆解「全转化」行，用于透视表分析转化成本
• 已完成步骤：将Keyword Data数据导入Full Data Set，通过辅助表提取Conversion Data中唯一转化动作值并转置为列

现有尝试及问题 #

使用以下公式尝试匹配转化数据，但未生效：

=ArrayFormula(VLOOKUP($B2:$B&$C2:$C&$D2:$D&$H2:$H&J$1, 'Conversion Data'!$H$3:$I, 1, FALSE))

无效原因：

1. VLOOKUP要求查找值必须位于查找范围的首列，但公式中组合的唯一键（Ad Group+关键词+匹配类型+周+转化动作）并非'Conversion Data'!$H$3:$I的首列（H列为周），匹配逻辑不成立
2. 公式仅返回查找范围的第1列（周数据），无法获取转化相关的有效信息

修正方案 #

方案1：使用XLOOKUP实现精准匹配 #

在Full Data Set的第一个转化类型列（如J2单元格）输入以下公式，然后向右拖动填充至所有转化类型列：

=ArrayFormula(IFERROR(XLOOKUP($A2:$A&$B2:$B&$C2:$C&$D2:$D&$H2:$H&J$1, 'Conversion Data'!$A:$A&'Conversion Data'!$B:$B&'Conversion Data'!$C:$C&'Conversion Data'!$D:$D&'Conversion Data'!$H:$H&'Conversion Data'!$I:$I, 'Conversion Data'!$J:$J, 0), 0))

逻辑说明：

• 组合Campaign+Ad Group+关键词+匹配类型+周+转化动作作为唯一匹配键
• 从Conversion Data中匹配对应键的转化数量，无转化则返回0

方案2：使用INDEX+MATCH组合 #

若版本不支持XLOOKUP，可改用以下公式：

=ArrayFormula(IFERROR(INDEX('Conversion Data'!$J:$J, MATCH($A2:$A&$B2:$B&$C2:$C&$D2:$D&$H2:$H&J$1, 'Conversion Data'!$A:$A&'Conversion Data'!$B:$B&'Conversion Data'!$C:$C&'Conversion Data'!$D:$D&'Conversion Data'!$H:$H&'Conversion Data'!$I:$I, 0)), 0))

方案3：批量查询（适合多列批量处理） #

使用QUERY函数批量获取对应维度的转化数据：

=ArrayFormula(BYROW($A2:$A, LAMBDA(camp, IFERROR(QUERY('Conversion Data'!$A:$J, "SELECT J WHERE A='"&camp&"' AND B='"&$B2&"' AND C='"&$C2&"' AND D='"&$D2&"' AND H='"&$H2&"' AND I='"&J$1&"'", 0), 0))))

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