基于query生成的虚拟数组表：拼接Col2值匹配Lookup表的问题

我有一个由QUERY生成的虚拟数组表（非单元格存储），结构如下：

需求 #

将每一组***分隔行之间的Col2值拼接，用拼接结果在Lookup表中匹配，得到对应的Col4值，预期结果：

Lookup表结构 #

我尝试过用OFFSET函数，但因为是虚拟数组，无法指定单元格引用；用VLOOKUP也遇到困难，求解决方法。

针对Google Sheets（适配QUERY生成的虚拟数组场景） #

可以结合BYROW、SCAN、TEXTJOIN和XLOOKUP实现全程虚拟数组内运算，无需依赖单元格引用：

=LET(
  data, QUERY(...), // 替换为你的原QUERY生成数组公式
  lookup_table, Lookup!A:B,
  // 标记每行所属分组（遇到***则分组ID+1）
  groups, SCAN(0, INDEX(data,,2), LAMBDA(a,c, IF(c="***", a+1, a))),
  // 按分组拼接非***的Col2值
  group_strings, BYROW(groups, LAMBDA(g, TEXTJOIN("", TRUE, FILTER(INDEX(data,,2), groups=g, INDEX(data,,2)<>"***")))),
  // 仅在每组第一行返回匹配结果，其余行留空
  col4, BYROW(SEQUENCE(ROWS(data)), LAMBDA(r, 
    IF(OR(r=1, INDEX(groups,r)<>INDEX(groups,r-1)), 
      XLOOKUP(INDEX(group_strings,r), INDEX(lookup_table,,1), INDEX(lookup_table,,2), ""), 
      ""
    )
  )),
  // 合并原数组与Col4
  HSTACK(data, col4)
)

逻辑说明：

1. SCAN：遍历Col2列，自动识别***分隔符，为每行分配分组ID。
2. BYROW+TEXTJOIN：按分组ID聚合该组内所有有效Col2值，完成拼接。
3. XLOOKUP：用拼接结果匹配Lookup表，获取对应值。
4. HSTACK：将原虚拟数组与生成的Col4列合并，输出最终结果。

针对Excel 365/2021 #

逻辑与Google Sheets一致，仅调整部分函数写法：

=LET(
  data, 你的原虚拟数组公式,
  lookup_table, Lookup!A:B,
  groups, SCAN(0, INDEX(data,,2), LAMBDA(a,c, IF(c="***", a+1, a))),
  group_strings, MAP(groups, LAMBDA(g, TEXTJOIN("", TRUE, FILTER(INDEX(data,,2), groups=g, INDEX(data,,2)<>"***")))),
  col4, MAP(SEQUENCE(ROWS(data)), LAMBDA(r,
    IF(OR(r=1, INDEX(groups,r)<>INDEX(groups,r-1)),
      XLOOKUP(INDEX(group_strings,r), INDEX(lookup_table,,1), INDEX(lookup_table,,2), ""),
      ""
    )
  )),
  HSTACK(data, col4)
)

核心优势 #

全程基于虚拟数组运算，无需将QUERY结果存入单元格，自动适配动态生成的数组结构，完美解决OFFSET/VLOOKUP无法直接处理虚拟数组的问题。

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