Google Sheets多行列查询问题：查找指定值并返回左侧单元格内容

问题背景 #

需要在MAIN!A42:H92区域内，搜索OPTIONS!A30、A31、A32三个特定端口配置值，找到后返回对应值左侧单元格的内容。此前使用INDEX+MATCH因跨列查找失败，VLOOKUP、FIND也未达成需求。

可行公式方案 #

1. 简洁版：使用XLOOKUP（推荐） #

XLOOKUP支持一维数组的查找与返回，通过FLATTEN将多列区域转为一维数组，即可实现跨列匹配：

=XLOOKUP(OPTIONS!A30, FLATTEN(MAIN!B42:H92), FLATTEN(MAIN!A42:G92))

• 替换OPTIONS!A30为A31/A32，即可分别匹配另外两个值
• 若需批量返回三个值的结果，可使用数组公式：

  =ARRAYFORMULA(XLOOKUP(OPTIONS!A30:A32, FLATTEN(MAIN!B42:H92), FLATTEN(MAIN!A42:G92)))
  

2. 兼容版：INDEX+MATCH组合 #

如果版本不支持XLOOKUP，可通过计算行号、列号实现：

=INDEX(MAIN!A42:G92, 
       INT((MATCH(OPTIONS!A30, FLATTEN(MAIN!B42:H92), 0)-1)/COLUMNS(MAIN!B42:H92))+1, 
       MOD(MATCH(OPTIONS!A30, FLATTEN(MAIN!B42:H92), 0)-1, COLUMNS(MAIN!B42:H92))+1)

• FLATTEN(MAIN!B42:H92)：将搜索区域转为一维数组，让MATCH可正常定位
• 行号计算：通过整数除法确定匹配值所在行
• 列号计算：通过取余确定匹配值所在列，对应到左侧单元格区域

注意事项 #

• 确保MAIN!B42:H92中每个搜索值唯一，否则会返回第一个匹配结果
• 若需处理无匹配的情况，可包裹IFERROR：

  =IFERROR(XLOOKUP(OPTIONS!A30, FLATTEN(MAIN!B42:H92), FLATTEN(MAIN!A42:G92)), "未找到匹配项")
  

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