Excel，根据整个工作表中找到的文本返回行和列值

Question

假设我有一个如下所示的 Excel 工作表:

我需要找到任何值为“E”的单元格，并返回该行第一列和该列第一行的值。

例如，在本例中，预期输出将为 February 3。

我之前已经能够使用 HLOOKUP 和 VLOOKUP 来做类似的事情，但我总是在单行或单列中搜索文本，而在这里，我需要在整个表中搜索值。

有什么办法可以做到这一点吗？

请注意，我无法控制电子表格的格式，是的，我完全同意这不是存储和处理此数据的良好格式。

Answer 1

假设你的例子

位于Sheet1中，则公式:

{=INDEX(Sheet1!$A$1:$A$1000,INT(SMALL(IF(Sheet1!$B$2:$K$10="E",ROW(Sheet1!$B$2:$K$10)+COLUMN(Sheet1!$B$2:$K$10)/1000),ROW($A1))))}

和

{=INDEX(Sheet1!$A$1:$ALK$1,,RIGHT(TEXT(SMALL(IF(Sheet1!$B$2:$K$10="E",ROW(Sheet1!$B$2:$K$10)+COLUMN(Sheet1!$B$2:$K$10)/1000),ROW($A1)),"0.000"),3))}

向下复制都会得到

在另一张纸中。

第一个公式位于此处的 B2 和 C2 中。

两个公式都是数组公式。将其放入单元格中，无需键入大括号。然后按[Ctrl]+[Shift]+[Enter]确认。现在应该出现大括号。

公式中的搜索范围Sheet1!$B$2:$K$10可以扩展到Sheet1!$B$2:$ALK$999。但这个范围越大，性能就越差。

方法是获取搜索范围中出现“E”的数字列表(行号+列号/1000)。即行号作为整数部分，列号作为小数部分，其中 1 为 0.001，2 为 0.002，...，999 为 0.999。然后使用 Small 从最小到最大获取这些数字。然后通过获取整数部分得到真实的行号。并从最后 3 位中获取真实的列号。

要使用计算出的小数的最后三位数字作为列号，我们必须确保在所有情况下都有三位数字作为小数位。所以我们必须使用格式0.000。为此，我们可以使用 TEXT 函数。