在Excel中如何使用辅助函数构造数组，从何实现逆向查询

L罗乐 2019-04-14

展开全文

本文转载自公众号：Excel教程，作者：甘亮

我们知道函数在excel应用十分之多，有人把VLOOKUP函数列入最受欢迎的查找函数之一，也有人管它叫excel的数据查找的能人。由此可见，这个函数在查找方面是多么的受欢迎。但是对于逆向查找，使用该函数正常的操作不能实现找个功能，需要添置辅助参数。

PS：对于Vlookup函数，其实还有一个非常致命的缺陷，就是只能查找它所找到的第一值。在没有辅助列和参数的时候，他不能独自完成实现一对多的数据查询工作。

今天我们讲解2种操作方法，可以实现逆向查找，Lsit如下：

A、VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0) OR VLOOKUP(A9,Choose({1,2},B2:B5,A2:A5),2,0)

B、INDEX(D2:D9,MATCH(B2,E2:E9,0))

我们逐步看一下这个三种用法的具体实现结果：

A、VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)

我们都知道这个VLOOLUP函数可以实现精确查找；模糊查找。

函数的用法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

精确查找和模糊查找十分的类似，只是最后一个参数是1，精确查找是0.模糊查找是在列表中不确定能不能查找到确定的值，或者只能查找到值所在的区域。这个是他正常的操作，我们今天要讲的是他在工作表中的逆向查找。

我们都知道用VLOOKUP函数进行查找，总是从前往后查找，也就是要查找的内容在前面列，对应的目标在后面的列，然后看从要查找的列到目标所在列有多少列，而逆向查找，则相反，要查找的目标在前面的列，此时就要将前后的列对调一下位置。

如下图所示，我们有一组数据。当我们知道其中的一个员工的姓名，如何才能知道它所对应的工号。按照常规操作的话，我们需要将D、E的数据进行位置调换，才能实现VLOOKUP函数查找.我们在这里使用函数建立数组，在数组中进行调换。

公式1：=VLOOKUP(A9,IF({1,0},B2:B5,A2:A5),2,0)

公式2：=VLOOKUP(B2,CHOOSE({1,2},E2:E9,D2:D9),2,0)

这里用到一个IF函数，IF函数写法=IF(条件，条件为真时的结果，条件为假的结果），｛1,0｝是一个一维数组，作为IF函数的条件，1代表IF函数条件为真，0代表函数条件为假

在单元格中输入公式=IF(1,'我','你')，显示结果“我”，输入=IF(0,'我','你')，显示结果“你”

PS:CHOOSE函数和IF的作用在这位置的效果，其实是一样的，都是建立数组。

公式剖析：1、这里其实不是VLOOKUP函数可以实现从右至右的查找，而是利用IF（choose）函数的数组效应把两列换位重新组合后，再按正常的从左至右查找。

2、IF({1,0},B2:B5,A2:A5))这是本公式中最重要的组成部分。在EXCEL函数中使用数组时(前提时该函数的参数支持数组)，返回的结果也会是一个数组。这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE，当为1时，它会返回IF的第二个参数(B列)，为0时返回第二个参数(A列)。

B、INDEX(D2:D9,MATCH(B2,E2:E9,0))

在际应用中，只要求返回位置的问题不多，好像MATCH函数一时派不上用场了。其实这个函数更多的时候，是与其他引用类函数组合应用，最典型的使用是与INDEX函数组合，能够完成类似VLOOKUP函数和HLOOKUP函数的查找功能，并且可以实现逆向查询，即从左向右或是从下向上查询。

对于这种逆向查询的数据可以使用LOOKUP函数，今天再说说用INDEX MATCH函数实现的方法。INDEX函数是常用的引用类函数之一，可以在一个区域引用或数组范围中，根据指定的行号和列号来返回一个值。使用INDEX函数和MATCH函数的组合应用来查询数据，公式看似相对复杂一些，但在实际应用中，更加灵活多变。

公式：INDEX(D2:D9,MATCH(B2,E2:E9,0))