Google Sheets数组公式问题：匹配下拉列返回对应值及公式修正

最简数组公式 #

方案1（推荐，全版本支持精确匹配） #

=ArrayFormula(IF(Claims!B2:B="", "", VLOOKUP(Claims!B2:B, Ref!A2:B, 2, FALSE)))

方案2（更直观，需支持XLOOKUP函数） #

=ArrayFormula(XLOOKUP(Claims!B2:B, Ref!A2:A, Ref!B2:B, ""))

原有公式修正 #

公式1：LOOKUP版本修正 #

原公式问题：LOOKUP默认要求查找区域升序排序，未排序时会返回错误匹配结果。修正为精确匹配写法，无需排序：

=ArrayFormula(IF(Claims!B2:B="", "", LOOKUP(2, 1/(Ref!A2:A=Claims!B2:B), Ref!B2:B)))

原理：通过1/(Ref!A2:A=Claims!B2:B)生成匹配位置为1、不匹配为错误值的数组，LOOKUP(2,...)会返回最后一个有效匹配结果，实现精确匹配。

公式2：QUERY版本修正 #

原公式问题：文本值未加单引号导致语法报错，且未实现批量数组处理。修正后：

=ArrayFormula(IF(Claims!B2:B="", "", QUERY(Ref!A2:B, "Select B where A = '"&SUBSTITUTE(Claims!B2:B, "'", "\\'")&"'", 0)))

说明：SUBSTITUTE处理文本中的单引号避免语法冲突，ArrayFormula实现批量遍历B列所有单元格。

公式3：FILTER版本修正 #

原公式问题：未实现批量数组处理，仅能单个单元格匹配。修正后用BYROW遍历每个单元格：

=ArrayFormula(BYROW(Claims!B2:B, LAMBDA(x, IF(x="", "", FILTER(Ref!B2:B, Ref!A2:A=x)))))

说明：BYROW逐个处理B列单元格，对每个值执行FILTER匹配，空值返回空。

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