excel - 在二维范围内查找匹配的公式

Question

我需要一个公式来查找二维范围内的值并返回匹配单元格的坐标或单元格地址。例如:

R    A    B    C

1    John Matt Pete
2    Sara Bret Chad
3    Lila Maya Cami

我想在 A1:C3 范围中搜索 Chad 并返回 C2 或 2,3。如何使用 Excel 公式完成此操作？ (实际上我最终会将其应用到 Google 表格中)。

谢谢!

Answer 1

老问题，但我想我应该在这里分享一个更简单、更优雅的答案，它不涉及辅助列或复杂的公式，以便更多的人可以更轻松地完成工作。假设该表包含唯一值并且您使用 E1存储您的搜索字符串Chad和E2显示结果:

• 如果你想要 2,3 的行和列结果在E2 :

  =SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)) & "," & SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3))
  

• 如果您想要 C2 的 R1C1 样式单元格地址字符串在E2 :

  =ADDRESS(SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)),SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3)))
  

• 如果您想要找到 Chad 的单元格内容在E2 :

  =INDIRECT(ADDRESS(SUMPRODUCT((A1:C3=E1)*ROW(A1:C3)),SUMPRODUCT((A1:C3=E1)*COLUMN(A1:C3))))
  

事情是如何运作的:

• SUMPRODUCT在这种情况下返回 bool 数组 TRUE 之间的乘积之和。 (在单元格中找到的搜索值)和 FALSE (在单元格中找不到搜索值)表格中的每个单元格以及工作表中这些单元格的相应行/列(绝对)编号；因此，结果本质上是找到该值的单元格的行/列(绝对)编号，自 TRUE=1 起和FALSE=0用数学术语来说
• ADDRESS以文本形式返回单元格地址(不作为引用!)
• INDIRECT返回与单元格文本地址对应的引用

来源和来源:this answer通过 XOR LX。本来可以在评论中添加链接，提及重复的问题，但我想稍微扩展和解释答案，因此需要更多字符。