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

Question

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

R    A    B    C

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

我希望能够使用Excel公式在范围 A1:C3 中查找 Chad 并返回 C2 或 2,3。请问该如何实现？（我最终会将其应用于Google Sheets）。

谢谢！

- user3491852

---

你尝试过什么？你有什么想法吗？如果你能告诉我们，那就太好了。 - L42

也许编写一个自定义的.gs函数会有所帮助。 - PEZO

Answer 1

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

• if you want the row and column result of 2,3 in E2:

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

• if you want the R1C1 style cell address string of C2 in E2:

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

• if you want the found cell's contents of Chad in E2:

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

工作原理:

• SUMPRODUCT 在此情况下返回一个布尔数组（单元格中找到搜索值为TRUE，未找到为FALSE），与表格中每个单元格及其相应行/列号（绝对）的乘积之和；因此，结果本质上是找到该值的单元格的行/列（绝对）号码，因为在数学术语中TRUE=1，FALSE=0
• ADDRESS 返回单元格的地址作为文本（而不是引用!）
• INDIRECT 返回对应于单元格文本地址的引用

来源和信用归属：XOR LX的这个回答。可以通过在评论中添加链接并提到重复的问题来进行补充，但我想更详细地扩展和解释答案，因此需要更多字符。

- Yin Cognyto

---

谢谢！使用此公式可以查找相邻单元格（E2）的值在另一个工作表（sheet2）的二维数组（D2：H）中出现的位置，然后提取找到该值所在行的A列单元格的值：=INDEX(sheet2!$A:$A, SUMPRODUCT((sheet2!$D$2:$H = E2) * row(sheet2!$D$2:$H))) - alxndr

Answer 2

假设您正在使用Excel 2007或更新版本。

您需要一个辅助列。如果您的表格看起来像您的示例中那样，在单元格D1中写入：

=IFERROR(MATCH($E$1,$A1:$C1,0),0)

把它拖下来。然后在单元格E1中写入您的搜索值（例如"Chad"）。然后，使用此公式，在单元格E2中获得搜索结果：

=IF(MAX($D:$D)=0,NA(),MATCH(MAX($D:$D),$D:$D,1)&","&MAX($D:$D))

- ttaaoossuuuu

---

在将此答案重新实现到我的解决方案后，我不得不修复匹配以使用匹配类型0，即=IF(MAX($D:$D)=0,NA(),MATCH(MAX($D:$D),$D:$D,0)&","&MAX($D:$D))，除此之外，一切都非常好，谢谢Taosique。 - Stefan

Answer 3

如果您想要一个更简单的解决方案，可以仅使用一个助手（或者不使用助手，但需要使用复杂的公式）。

假设我采用您的示例。我将使用D列来显示结果：

- 在D1中，我放置要查找的名称：“Chad”。 - 在D2中，我放置助手，它将返回搜索值的索引（如果未找到，则返回-1）：`=IFERROR(MATCH(D1,SPLIT(TEXTJOIN(";",TRUE,A1:C3),";"),0),-1)`。 - 在D3中，我放置获取行、列值的公式，如果未找到则返回FALSE：`=IF(D2<>-1,ROUNDUP(DIVIDE(D2,COLUMNS(A1:C3))) & "," & IF(MOD(D2,COLUMNS(A1:C3))=0,COLUMNS(A1:C3),MOD(D2,COLUMNS(A1:C3))))`。

如果您真的想只使用一个公式，那么在 D3 中可以通过将所有对 D2 的引用替换为在 D2 中使用的公式来实现。

- Flo

Answer 4

这个公式可以返回一个二维数组中给定值的行列号。

=LET(
  array,           B2:D4,
  findvalues,      C7,

  arrayrows,       ROWS(array),
  arraycols,       COLUMNS(array),
  rowindex,        SEQUENCE(arrayrows*arraycols,,1,1/arraycols),
  colindex,        MOD(SEQUENCE(arrayrows*arraycols,,0),arraycols)+1,
  flatarray,       INDEX(array,rowindex,colindex),
  valueflatindex,  MATCH(findvalues,flatarray,0),
  valuerow,        ROUNDUP(valueflatindex/arraycols,0),
  valuecol,        MOD(valueflatindex-1,arraycols)+1,
  absvaluerow,     MIN(ROW(array))+valuerow-1,
  absvaluecol,     MIN(COLUMN(array))+valuecol-1,

  CHOOSE({1,2},absvaluerow,absvaluecol)
)

    A        B        C        D        E
1
2            John     Matt     Pete
3            Sara     Bret     Chad
4            Lila     Maya     Cami
5
6
7            find:    Chad
8            formula: 3        4

更精确地说，此公式逐行扫描给定的数组，并返回给定值第一次出现的地址。

如果您需要相对于数组左上角单元格的行列号，则在CHOOSE(...)中，使用valuerow/valuecol，而不是absvaluerow/absvaluecol。

如果您想要将值以逗号分隔并放在一个单元格中，而不是使用CHOOSE(...)，则使用absvaluerow & "," & absvaluecol。

如果您的Excel版本不支持最新的函数，例如LET，则如果您重写它使其不使用LET变量，则该公式仍应起作用。

查找多个值：

您还可以使用此公式在数组中查找多个值，如this thread中所述。

- f-hollow