Google Sheets中使用ArrayFormula+XLOOKUP匹配工单号查询工程师姓名问题

问题根源 #

你的公式将每行B26:H45的所有单元格拼接成字符串进行匹配，但实际场景是每行仅单个单元格填充工单号、其余为空，拼接后的字符串会包含大量空字符，与A71中输入的纯工单号无法匹配；同时ArrayFormula与XLOOKUP的组合使用逻辑冗余，导致公式无法正常运行。

可行解决方案 #

方案1：使用INDEX+MATCH（简单高效） #

直接在B26:H45的二维区域中查找工单号，返回对应行的工程师姓名：

=IFERROR(INDEX($A$26:$A$45,ROUNDUP(MATCH(A71,$B$26:$H$45,0)/COLUMNS($B$26:$H$45),0)),"Not found")

• 逻辑说明：MATCH返回工单号在B26:H45区域内的偏移位置，通过除以列数并向上取整得到对应的行号，再用INDEX提取A列的工程师姓名；IFERROR处理无匹配的情况，返回"Not found"。

方案2：使用XLOOKUP+FLATTEN（更直观） #

将二维的工单号区域转为一维数组，同时生成对应行的工程师姓名数组，再用XLOOKUP匹配：

=XLOOKUP(A71,FLATTEN($B$26:$H$45),INDEX($A$26:$A$45,ROUNDUP(SEQUENCE(ROWS($B$26:$H$45)*COLUMNS($B$26:$H$45))/COLUMNS($B$26:$H$45),0)),"Not found",0)

• 逻辑说明：FLATTEN把B26:H45的多行多列转为单列；SEQUENCE生成对应长度的序列，通过计算得到每个位置对应的行号，INDEX提取对应的工程师姓名；最后用XLOOKUP匹配工单号并返回结果。

方案3：使用FILTER（支持多匹配返回） #

如果存在多个相同工单号需要返回所有对应工程师姓名，可使用FILTER：

=IFERROR(JOIN(", ",FILTER($A$26:$A$45,$B$26:$H$45=A71)),"Not found")

• 逻辑说明：FILTER筛选出所有包含目标工单号的行对应的工程师姓名，JOIN用逗号拼接结果；无匹配时返回"Not found"。

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