用公式查找Excel工作表数据

1、行列双查找

双查找是一个公式，该公式将在行和列中的值匹配的基础上从表中返回值。在下面的示例数据中，你可能想要返回相应的“a”所在行和“33”所在列的值。如果单元格F16的值包含在要匹配的行，并且单元格G16的值包含在要匹配的列中，在H16单元格中输入下面的公式，将从表中返回相应的值。

H16单元格公式：

=OFFSET(F8,MATCH(F16,F9:F13,0),MATCH(G16,G8:K8,0))

2、查找左边列数据

VLOOKUP函数只能从首列查找，然后返回右边某列的值。例如，您可以在下面的示例数据F列中查找值，然后从G列中返回相应的值，但是，不可以从G列中查找值，然后从F列中返回相应的值。

下面这个公式可以做到：

=OFFSET(G32,MATCH(F41,G32:G38,0)-1,-1)

3、任意查找第N个匹配值

VLOOKUP函数另一个限制是，如果查找列中有重复的值，仅查找第一个匹配的值。例如下面的数据表：

要查找匹配的第二个值、第三个值，可以使用下面的数组公式（CTRL+SHIFT+ENTER，三键齐按）：

=INDEX(B5:C19,SMALL(IF(B5:B19=E6,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),F6),2)

如果要查找最后一个匹配结果，可以使用下面的数组公式：

=INDEX(B5:C19,SMALL(IF(B5:B19=E13,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),COUNTIF(B5:B19,E13)),2)

4、任意查找第N个多条件匹配值

在下面的示例数据中，查询第 2 次出现的、A2:A13 中值是"Dog"、并且C2:C13 中值是"Male"，返回第 5 列的数据。

使用下面的数组公式：

=INDEX(A2:E13,SMALL(IF((A2:A13=H4)*(C2:C13=H5),ROW(A2:A13)-ROW(A2)+1,ROW(A13)+1),H6),5)

5、查找最接近的匹配值

在下面的示例数据中，要求查询最接近的2个值，以及向上最接近的值和向下最接近的值。

（1）查询最接近的2个值

同时选定连续的两列单元格，输入以下数组公式：

=INDEX(B2:B11,MATCH(SMALL(ABS(B2:B11-C13),{1,2}),ABS(B2:B11-C13),0))

或者，同时选定连续的两行单元格，输入以下数组公式：

=INDEX(B2:B11,MATCH(SMALL(ABS(B2:B11-C13),{1;2}),ABS(B2:B11-C13),0))

（2）分别查询向上和向下最接近的值

向上最接近值的数组公式：

=INDEX(D2:D6,MATCH(MIN(IF(D2:D6-E8>=0,D2:D6,FALSE)),IF(D2:D6-E8>=0,D2:D6,FALSE),0))

向下最接近值的数组公式：

=INDEX(D2:D6,MATCH(MAX(IF(D2:D6-E8<=0,D2:D6,FALSE)),IF(D2:D6-E8<=0,D2:D6,FALSE),0))