查找！查找！永恒的查找！

excelperfect

查找，永恒的主题。

从一大堆数据中找出我们需要的数据，这是Excel中常用的操作。如果使用公式来进行查找，那么Excel提供了丰富的函数，让你随条件不同获取想要的数据。其中，最常见或我们最熟悉的就是VLOOKUP函数、INDEX函数/MATCH函数，等等。

本文从一个特殊的查找问题开始，其效果演示如下图1所示。

图1

通常，我们根据图书名称查找其售价，一般使用VLOOKUP函数能够方便完成。然而，这里是根据大致的价格来查找相应的图书。

我们在单元格C2中输入一个价格，在下面的表中查找在这个价格1元范围内的图书，并在单元格B5中显示图书名称。

显然，VLOOKUP函数已不能应对此种情况，我们要使用经典的INDEX+MATCH函数组合了。

如果单元格C2中的价格刚好是表中某行的价格，那么：

MATCH($C$2,$C$9:$C$21,0)

就会返回该行的行号，传递给INDEX函数就可以获取对应的图书名称了。

如果单元格C2中的价格在表中找不到呢，那么我们就要判断表中是否有价格处于这个价格的±1范围内，如果有则返回其对应的图书名称。那么：

($C$9:$C$21>=($C$2-1))*($C$9:$C$21<=($C$2+1))

会对表中的价格进行比较，返回一个由1/0组成的数组，其中的1值表明该价格处于指定的范围内。这样，我们找到这个1值所在的位置：

MATCH(1,($C$9:$C$21>=($C$2-1))*($C$9:$C$21<=($C$2+1)),0)

就是要查找的图书对应的位置，传递给INDEX函数就可以获取对应的图书名称。

因此，完整的公式为：

=IFERROR(INDEX($B$9:$B$21,IFERROR(MATCH($C$2,$C$9:$C$21,0),MATCH(1,($C$9:$C$21>=($C$2-1))*($C$9:$C$21<=($C$2+1)),0))),'没有找到')

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

然而，如果与单元格C2的价格相差1元的价格不止1个呢？如下图2所示。

图2

如何实现查找重复值？

假设如果有多个符合要求的价格，我只要查找满足价格的图书最多3本，此时LARGE函数就要上场了。

上文中，我们使用：

($C$9:$C$21>=($C$2-1))*($C$9:$C$21<=($C$2+1))

获取了哪些价格满足条件，即数组中是1的值所在的位置的价格。在此基础上，我们乘以：

ROW($C$11:$C$23)-ROW($C$11)+1

就可以得到一个由满足条件的价格所在行位置和0组成的数组，即：

($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1))*(ROW($C$11:$C$23)-ROW($C$11)+1)

对于图2所示的查找数据来说，这个返回的数组是：

{1;0;0;0;0;0;0;0;0;10;11;0;0}

即，表中的第1行、第10行、第11行对应满足我们条件的价格。

然后，使用LARGE函数依次返回这些行位置：

LARGE(($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1))*(ROW($C$11:$C$23)-ROW($C$11)+1),SUM(($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1)))-ROW(A1)+1)

其中，SUM(($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1)))统计共有多少个返回值，传递给INDEX函数可返回相对应的图书。

这样，在单元格B5中的公式：

=IFERROR(INDEX($B$11:$B$23,IFERROR(MATCH($C$2,$C$11:$C$23,0),LARGE(($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1))*(ROW($C$11:$C$23)-ROW($C$11)+1),SUM(($C$11:$C$23>=($C$2-1))*($C$11:$C$23<=($C$2+1)))-ROW(A1)+1))),'没有找到')

这是一个数组公式，输入完成后要按Ctrl+Shift+Enter组合键。

然后，将其下拉复制到单元格B7。

好了！接下来我们举一反三，再来谈谈几种特殊的查找。

如果我们只想查找价格的整数部分，那么可以使用数组公式：

=IFERROR(INDEX($B$9:$B$21,MATCH($C$2,INT($C$9:$C$21),0)),'没有找到')

如果想要查找大于指定价格且书名含有“Excel”的图书，那么可以使用数组公式：

=IFERROR(INDEX($B$9:$B$21,MATCH(1,($C$9:$C$21>$C$2)*ISNUMBER(FIND('Excel',$B$9:$B$21)),0)),'没有找到')

以上两种情形，如果涉及到有重复的结果，则可参照上文公式进一步拓展。

如果想要查找最接近指定价格的书名，可以使用数组公式：

=IFERROR(INDEX($B$9:$B$21,MATCH(MIN(ABS($C$9:$C$21-$C$2)),ABS($C$9:$C$21-$C$2),0)),'没有找到')

看到了吧，查找条件变化，公式也随之调整，以求出想要的结果。