Vlookup的9种查找方式，灵活运用函数原来这么简单！

这两天看了不少网上的教程，也找了一些题库实战，整理一下EXCEL中VLOOKUP函数的常用技巧。

首先了解一下绝对引用和相对引用的概念，EXCEL默认横向拉动的时候列变行不变，竖向拉动的时候行变列不变。

• 绝对引用时无论横向还是竖向拉动，行列都不变

• 相对引用时，$在行前面则横向竖向行动都不变，$在列前面则横向竖向拉动列都不变

一. 基础查找

VLOOKUP（查找目标，查找区域，返回列，0）

下面是一个实例：

在员工信息表中根据员工工号查询员工姓名，我想要在D1:G9的表格区域中查找工号分别为A01048、A05023、A09095的员工姓名，首先查询工号A01048的员工姓名，之后可以直接下拉公式，表格自动填充结果。

注意：此公式查找目标一定要在查找区域的第一列，比如我通过工号查姓名，那么工号必须在查找区域的第一列，即D1:G9；如果是通过姓名查部门，那么查找区域需要是E1：G9，而且查找区域需要用 $ 符号绝对引用，不然下拉公式的时候区域变化可能会查不到数据。

返回列指查询内容所在列，如我想要查找的是姓名，姓名这一列在D1:G9区域的第二列，所以返回列的值是2，以此类推。

最后一个参数0或1表示函数精确查找或模糊查找。精确即完全一样,模糊即包含的意思。参数如果指定值是0或FALSE就表示精确匹配,而值为1或TRUE时则表示模糊匹配，漏掉该参数则默认模糊匹配。

精确匹配是使用历遍法查找，模糊匹配是使用二分法查找。

公式01

二. 多列查找 VLOOKUP(查找目标,查找区域,COLUMN(A1/B1/C1...),0)

上例通过工号查询姓名，如果想要通过工号同时查询姓名和部门，则可以使用COLUMNS函数。不使用COLUMNS函数往右拖动返回列的值不变，无法查询其他列。

不使用函数直接拖动

使用COLUMNS函数，A1、B1、C1...分别代表1、2、3...，向右拖动直接变化返回列，返回列的值递增。注意不要忘了相对引用查找目标，固定列，此例中为$A3。

公式02

三. 字符的模糊查找

VLOOKUP("*"&查找目标&"*",查找区域，返回列，0)

如我并不知道具体的工号，只知道工号中包含A05，就可以使用模糊查找。第四个参数0是使用遍历法进行精确查找，因此当从上而下查找到包含A05的工号，即停止查找。举例来说，张丽和夏远的工号都包含A05,但是查找出的姓名是张丽，因为张丽在前面。

公式03

四. 数字的区间查找

VLOOKUP（查找目标，查找区域，返回列，1）

由于二分法的原理，引用的数字区域一定要从小到大排序，杂乱的数字是无法准确查找到的。模糊查找的原理是：给一定个数，它会找到和它最接近，但比它小的那个数。

公式04

五. 反向查找

VLOOKUP(查找目标,IF({1,0},查找列,区域列),返回列,1)

注意：这里其实不是VLOOKUP可以实现从右至左的查找，而是利用IF函数的数组效应把两列换位重新组合后，再按正常的从左至右查找。

IF({1,0},查找列，区域列)这是本公式中最重要的组成部分。在EXCEL函数中使用数组时，返回的结果也会是一个数组。这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE，当为1时，它会返回IF的第二个参数(查找列)，为0时返回第二个参数(区域列)。

公式05

六. 多条件查找

{=VLOOKUP（查找条件A & 查找条件B，IF（{1,0}，区域A & 区域B，查找区域），返回列，1）}

根据员工号和姓名两个条件查询部门，输入公式=VLOOKUP(A3&B3,IF({1,0},E1:E9&F1:F9,G1:G9),2,0)

以后按ctrl+shift+enter自动变成数组形式

{=VLOOKUP(A3&B3,IF({1,0},E1:E9&F1:F9,G1:G9),2,0)}

公式06

公式剖析：

• A3&B3 把两个条件连接在一起。把他们做为一个整体进行查找。

• E1:E9&F1:F9，和条件连接相对应，把工号和姓名列也连接在一起，作为一个待查找的整体。

• IF({1,0},E1:E9&F1:F9,G1:G9) 用IF（{1，0}把连接后的两列与G列数据合并成一个两列的内存数组。按F9后可以查看的结果。

• 完成了数组的重构后，接下来就是VLOOKUP的基本查找功能了，另外公式中含有多个数据与多个数据运算，所以必须以数组形式输入，即按ctrl shift后按ENTER结束输入。

七. 批量查找

{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)}

公式07

公式剖析：

• B$9&ROW(A1) 连接序号，公式向下复制时会变成B$9连接1，2，3。

• 给所有的张一进行编号。要想生成编号，就需要生成一个不断扩充的区域(INDIRECT("b2:b"&ROW($2:$6))，然后在这个逐行扩充的区域内统计"张一"的个数，在连接上$B$2:$B$6后就可以对所有的张一进行编号了。

• IF({1，0}把编号后的B列和C组重构成一个两列数组。

八. 跨表查找

VLOOKUP(查询目标,查询表！查询区域，返回列，0)

下例跨表查询员工所在部门。

公式08

跨工作表的时候，会以！代表工作表的名字。

九. 跨多表查找

VLOOKUP(查询目标,INDIRECT(工作表名 &查询区域),返回列,0)

如汇总查询各个表中同一个人的工资信息，公式为：=VLOOKUP($A3,INDIRECT(B$2&"!A1:B9"),2,0)

查询区域使用INDIRECT函数构造引用区域，INDIRECT()函数的功能：返回由文本字符串所指定的引用,语法格式为 INDIRECT(ref_text,[a1])，ref_text 为对单元格的引用。

indirect函数的引用的两种形式：加引号和不加引号。

• =INDIRECT("B2")——加引号：文本引用——即引用B2单元格所在的文本（1月工资)。

• =INDIRECT(B2)——不加引号：地址引用——因为B2的值为1月工资，1月工资是工作表的名字，所以引用工作表的区域。

$A3 和 B$2 代表相对引用。

公式09

如果不知道查询目标在哪个表里，则使用公式：

IFERROR(VLOOKUP(查询目标,A表!查询区域,返回列,0),

VLOOKUP(查询目标,B表!查询区域,返回列,0),.....

VLOOKUP(查询目标,N表!查询区域,返回列,0))

意思是从A表开始查询，前面的查询不到就到后面的表中查找。如果有更多的表，那就一层层的套用下去，一直到最后一个N表。

可以简化为：

=VLOOKUP(查询目标,INDIRECT(LOOKUP(1,0/COUNTIF

(INDIRECT({"A表";"B表";"...";"N表"}&"!a:a"),查询目标),{"A表";"B表";"...";"N表"})&"!查找区域"),返回列,0)

• {""}：大括号内是要查找的多个工作表名称，用逗号分隔

• a:a :本例是姓名在各个表中的A列，如果在B列则为b:b

End.

作者：栗子

来源：豆瓣

本文均已和作者授权，如转载请联系后台删除