如何在XLOOKUP公式中用拼接值匹配多条件（支持顺序无关与空值）

问题概述 #

现有Google Sheets表格，需要以B1、C1为条件返回对应数据。当前用单条件公式=XLOOKUP($B$1,$E$2:$E$37,XLOOKUP($A$5,$G$1:$L$1,$G$2:$L$37))能正常工作，但尝试用B1+C1匹配E、F列时，出现报错：

Array arguments to XLOOKUP are of different size

需满足两个限制：

• C1单元格可能为空；
• B1和C1的顺序与表格E、F列的顺序无关（比如B1是Water、C1是Rock，和两者调换顺序时要返回相同结果）；
• 尽量不用IFERROR或ISNA函数，想知道是否必须用ARRAYFORMULA才能实现。

解决方案 #

不需要依赖ARRAYFORMULA就能实现，核心是构建精准的无序匹配条件，同时处理C1为空的场景。

方案一：多条件逻辑判断 #

=XLOOKUP(TRUE, 
  (($E$2:$E$37=$B$1)+($E$2:$E$37=$C$1))*(($F$2:$F$37=$B$1)+($F$2:$F$37=$C$1))*(LEN($B$1&$C$1)=LEN($E$2:$E$37&$F$2:$F$37)),
  XLOOKUP($A$5,$G$1:$L$1,$G$2:$L$37)
)

逻辑说明

1. (($E$2:$E$37=$B$1)+($E$2:$E$37=$C$1))：判断E列的值是B1或C1其中一个
2. (($F$2:$F$37=$B$1)+($F$2:$F$37=$C$1))：判断F列的值是剩下的那个条件值
3. (LEN($B$1&$C$1)=LEN($E$2:$E$37&$F$2:$F$37))：处理C1为空的情况——此时B1需完全匹配E列（对应F列为空，拼接后的长度一致），避免误匹配到E/F列任意一个为空的其他行
4. 外层XLOOKUP匹配第一个满足所有条件的行，返回对应数据

方案二：文本排序统一匹配 #

如果更倾向于简洁写法，可通过排序统一条件和目标列的文本顺序，同时兼容空值：

=XLOOKUP(
  IF($C$1="",$B$1,SORT({$B$1,$C$1})),
  IF($F$2:$F$37="",$E$2:$E$37,BYROW($E$2:$F$37,LAMBDA(r,SORT(r)))),
  XLOOKUP($A$5,$G$1:$L$1,$G$2:$L$37)
)

逻辑说明

• 用SORT把B1/C1、E/F行的内容统一排序，这样不管顺序如何，匹配逻辑都一致
• 通过IF判断空值：当C1为空时，直接用B1匹配E列对应F列空的行

关键结论 #

• 不需要ARRAYFORMULA：上述公式通过XLOOKUP的数组判断、BYROW（仅方案二用到）即可实现需求，无需外层包裹ARRAYFORMULA
• 无需错误捕获函数：通过精准的条件判断保证匹配逻辑严谨，不需要IFERROR或ISNA来规避错误

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