如何在excel中实现反向查询

我们平时在用excel一般都是正向查找，举个栗子：

下面这个简单的表里面，给你一个id，比如201903，查出对应的名字来，用vlookup函数很简单就能实现。

通过编号查名字

那反向查找呢，给你“cc”，让你查对应的编号是多少。我们知道vlookup有四个参数（用谁查，在哪个区域查，返回这个区域的第几位，精确还是模糊查），第三个参数是不能为负数的，所以用通常的方法没法实现反向查找的功能。

那遇到这种情况怎么办呢？

最简单的办法当然是直接在名字后面把编号复制一栏，然后继续用vlookup，一次性的查找用这种方法还可以，查完之后把公式删了，再把辅助列删了就好了，经常更改的数据用这种方法，数据就冗余了。

下面介绍三种不用辅助列的反向查找的方法：（悄悄说一句，其实再过一阵子，微软发布了xlookup函数后，就可以直接支持这种功能了，有兴趣的可以直接看微软官方文档：xlookup）

1、offset+match函数

offset函数的作用，一句话描述就是：选定一个单元格，然后可以向上下左右四个方向进行移动，返回移动后选中的单元格的值

match函数的作用，返回一个值在一个区域内是第几个，比如b在（a,b,c,d）这个区域里面就是第二个

具体到这个例子上来，offset函数以“编号”这个单元格做基准单元格，用match函数查出“cc”这个值在名字这一列中的第几个，然后作为offset函数向下移动的值就好了

offset+match 实现反向查找

2、vlookup函数

vlookup函数其实也可以实现反向查找，只是不能用我们常用的方式实现，需要用数组公式配合。

在介绍这个方法之前，我们先了解一下vlookup函数查找的原理：

vlookup函数的第二个参数是一个区域，这个区域在excel中其实就是一个数组，举个栗子：

最普遍的vlookup用法

然后我们选中第二个参数，按F9：

vlookup的参数变成了数组

f9的作用是运算你选中的区域，从这个例子我们可以看出，vlookup其实就是在一个数组里面查第一个参数对应的值，所以要反向查找的话，我们只需要构建一个第一列是所需查找的值的数组就好了。

话不多说，直接上公式：

vlookup函数实现反向查找

这个函数的第二个参数是：if({1,0},用于查找的值所在的列,需要返回的值所在的列)，用这种方法构建了一个新的数组，我们还是用f9来看一下：

创建新的数组

这个新的数组把aa，bb……这一列放在了前面，后面对应需要返回的值，变相将两列换了位置，就能用vlookup函数进行正常查找了。

3、lookup函数

lookup函数也是查找函数，有时候用起来比vlookup还简单，但为什么不被广泛使用呢，因为这个函数需要升序排列，举个栗子：

lookup有两个参数和三个参数的形式，这边用到的是三个参数：（查找值，查找值所在的区域，返回值所在区域）

lookup函数演示

正常说来，查找201907返回的应该是ee，但因为左列数值没有升序排列，返回值变成了gg，也就是把左边一列升序排列后201907对应的值。因为这个限制，所以这个函数没有被广泛使用。

下面说用lookup实现反向查找，还是直接上公式：

lookup实现反向查找

在这个例子里面，查找值不是cc，而是1，为什么呢，我们一步步看，先用f9选中(B2:B11=D2)，这一步返回的是一系列布尔值，如下图：

布尔值

我们看到，除了第三个值以外，别的都是false，因为B2:B11的第三个单元格和D2相等，而我们知道，false其实就等于0，true等于1

所以原公式里面，用0/(B2:B11=D2)，相当于用1除以0和1，得到的是一个错误值和0组成的数组：

错误值和0组成的数组

除了第三项意外，其他值都是错误值。

这个时候，lookup函数用第一个参数1，去这个数组中查找，毫无疑问是查不到值的，根据lookup函数的原理，查不到值的时候，会查找小于查找值的最大的值，而这个例子里面，小于1的最大值也就是0，所以成功查到了第三项，然后将第三个参数中的第三项“201903”返回，得到正确值。

总结一下，其实vlookup和lookup函数的实现方式其实都是用构建一个新的数组的方式实现的，如果有别的方式可以构建符合他们需要的数组的话，其实一样可以实现反向查找。