是否存在类似HLOOKUP()的函数可搜索Google Sheets/Excel二维区域？

Google Sheets 解法 #

通过定位目标值的行/列号，结合INDEX函数就能获取下方指定位置的值，具体步骤如下：

1. 定位目标值的行号和列号
   假设搜索值存放在单元格B1，数据区域为A:Z（可根据实际范围调整）：

   • 行号公式：=SUMPRODUCT((A:Z=B1)*ROW(A:Z))
   • 列号公式：=SUMPRODUCT((A:Z=B1)*COLUMN(A:Z))

2. 返回下方指定单元格的值
   以你的例子为例，需要返回目标单元格下方2行的值（C4→C6），将行号加2后传入INDEX：

   =INDEX(A:Z, SUMPRODUCT((A:Z=B1)*ROW(A:Z)) + 2, SUMPRODUCT((A:Z=B1)*COLUMN(A:Z)))
   

   注：如果表格存在多个相同搜索值，公式会返回第一个匹配项的位置。

Excel 解法 #

逻辑和Google Sheets一致，针对不同版本有两种写法：

通用版本（适配所有Excel版本） #

用SUMPRODUCT定位行/列，再通过INDEX取值：

=INDEX(A:Z, SUMPRODUCT((A:Z=B1)*ROW(A:Z)) + 2, SUMPRODUCT((A:Z=B1)*COLUMN(A:Z)))

Excel 365/2021 简化版 #

借助XLOOKUP结合TOCOL/TOROW简化写法，先将二维区域转为一维列，找到目标位置后映射回原区域的下方单元格：

=INDEX(A:Z, XLOOKUP(B1, TOCOL(A:Z), ROW(A:Z)) + 2, XLOOKUP(B1, TOROW(A:Z), COLUMN(A:Z)))

注意事项 #

• 若搜索值不唯一，公式仅返回第一个匹配项的结果；
• 若目标单元格下方超出数据范围，公式会返回#REF!错误，可嵌套IFERROR处理：

  =IFERROR(INDEX(A:Z, SUMPRODUCT((A:Z=B1)*ROW(A:Z)) + 2, SUMPRODUCT((A:Z=B1)*COLUMN(A:Z))), "未找到或超出范围")
  

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