Google Sheets：如何用数组公式批量匹配列中指定子字符串？

问题分析 #

你当前的单个单元格公式能从Sheet2的子字符串列表中，匹配Sheet1对应单元格的完整字符串，并返回Sheet2里最后一个匹配的子字符串，但没法直接扩展到整列。核心问题是原公式依赖INDIRECT、ADDRESS这类非数组友好的函数，且单个单元格引用（比如A2）无法自动遍历整列。

替代数组公式方案 #

方案1：使用BYROW+XLOOKUP（推荐） #

直接遍历Sheet1的目标列（假设是A列，从A2开始），对每一行执行匹配逻辑，保留“取最后一个匹配项”的原逻辑：

=BYROW(Sheet1!A2:A, LAMBDA(x, IF(x="", "", XLOOKUP(TRUE, ISNUMBER(SEARCH(Sheet2!A2:A, x)), Sheet2!A2:A, "", 0, -1))))

• 逻辑拆解：
  • BYROW遍历Sheet1的A2:A每一行，把当前行内容传给变量x
  • ISNUMBER(SEARCH(Sheet2!A2:A, x))生成布尔数组，标记Sheet2每个子字符串是否匹配当前行的完整字符串
  • XLOOKUP的最后一个参数-1表示从后往前查找，直接返回Sheet2中最后一个匹配的子字符串，和原公式用LARGE取最大行号的逻辑一致
  • 加入空值判断：如果Sheet1当前行是空，返回空值避免无效结果

方案2：兼容旧版的数组公式（无需LAMBDA） #

如果你的Google Sheets版本不支持BYROW/LAMBDA函数，可使用以下公式：

=ARRAYFORMULA(IF(Sheet1!A2:A="", "", VLOOKUP(MAX(IF(ISNUMBER(SEARCH(Sheet2!A2:A, Sheet1!A2:A)), ROW(Sheet2!A2:A))), {ROW(Sheet2!A2:A), Sheet2!A2:A}, 2, FALSE)))

• 逻辑拆解：
  • MAX(IF(ISNUMBER(SEARCH(Sheet2!A2:A, Sheet1!A2:A)), ROW(Sheet2!A2:A)))生成每个Sheet1行对应的Sheet2中最后一个匹配项的行号
  • 通过VLOOKUP匹配行号对应的Sheet2子字符串
  • 同样加入空值判断，避免无效结果

原公式无法直接数组化的原因 #

原公式中的单个单元格引用（如A2）无法自动对应每一行；同时INDIRECT和ADDRESS在数组环境下会返回多个地址字符串，无法正确解析为单元格引用，导致结果错误或溢出。

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