excel - 查找表格中符合条件的单元格

Question

我想找到一个公式解决方案，它需要 2 个参数:一个表格和一个文本，并返回表格中包含该文本作为子字符串的单元格。

例如，

• 给定下表 B2:D5 和文本 im，我希望公式返回单元格 B2，其值为 Jim。
• 给定表 B2:D5 和文本 omas，我希望公式返回单元格 C5，其值为 Thomas。

我们可以假设表格中只有一个单元格满足这一条件。如果有人想更进一步，当表格中有多个单元格符合条件时，我们可以尝试返回左上角的单元格。

理想情况下，

• 该公式返回单元格而不是单元格值。对于一个单元格，我的意思是我们甚至可以通过 : 将其与另一个单元格组合起来以构建一个范围，例如 ...:F10。
• 我更喜欢没有 LAMBDA 功能且仍处于预览阶段的解决方案。

有人知道如何实现这个目标吗？

Answer 1

使用 SEARCH 针对条件和输入表生成匹配矩阵。查找具有匹配项的第一列，以及该列中具有匹配项的第一行，然后将这些数字传递给 INDEX。使用 LET 使其更易于阅读:

=LET(
  t, R1C1:R5C3,
  cond, "*" & RC5 & "*",
  matches, IFERROR(SEARCH(cond, t), 9999),
  col, MIN(matches*COLUMN(t)),
  rw, MIN(INDEX(matches*ROW(t), FALSE, col)),
  INDEX(t, rw, col)
)

由于单元格引用会自动计算单元格的值，因此我在第 7 列中添加了第二个公式来转储引用字符串。

定义:t是输入表。 matches 是匹配数组。它要么是 1，要么是错误，所以我用 IFERROR 换行，并且由于稍后要获取 MIN，所以我使用任意高的数字，以便我的数组为 1 或 9999。 col 使用 MIN 来查找最低匹配列，然后我使用 INDEX 从输入表 (t) 返回该列并获取最小行。将这些数字组合起来得到一个可以在间接中使用的字符串。

请注意，您可以将此公式与 : 链接起来以生成范围: