对于Excel的“查找之王”，说点你听得懂的大白话！

小伙伴们好啊，今天和大家来说说VLOOKUP的那些事儿，深入了解一下VLOOKUP函数，看看这位大众情人还藏着多少不为人知的秘密。

VLOOKUP函数几乎成了Excel的代名词。会用VLOOKUP函数的人一定是Excel高手。

我们公司的财务人员常常说的一句话是：

我现在太忙了，我把基础表给你，你自己V一下吧。一个字母V就代表了Excel。

1

基础语法

VLOOKUP函数的基础语法为：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

用中文翻译过来就是VLOOKUP( 目标值 , 目标区域 , 第几列 , 查找方式 )。

VLOOKUP函数中的V缩写于单词Vertical，表示垂直的，整个函数就是Vertical Look Up，垂直的查找，也就是在目标区域的第一列，纵向查找目标值，然后返回第n列的对应结果。

第4个参数的查找方式有两种：数字0或FALSE，代表精确匹配；数字1或TRUE，代表模糊匹配。查找区域必须升序，查找的方案也是“二分法”。

这两种查找方式的原理与MATCH函数完全一致，不过VLOOKUP比MATCH少了一个-1的参数。

2

根据姓名查找等级

先讲个故事。

Excel Home论坛有一位版主，曾经也是Excel的“小白”，那时他的工作中有一项是核对人员成本，大概有上千行的数据。

每月的最后几天需要赶时间做出来，最后只能发动老婆一起做，每次都是几乎3个通宵才能完成。

这样的工作效率实在太低了，于是他下决心寻找高效方法，功夫不负有心人，终于学会了用VLOOKUP函数，于是三天的工作量缩短到半天，后来这位版主慢慢升职到了高管的位置。

这个故事告诉我们，努力工作加班并不能从根本上解决问题，能促使你进步的是一颗“偷懒”的心。

我们用一个案例来展示VLOOKUP函数的最基础应用。

如上图所示，C15:E22是数据区域，要查找G15:G16单元格区域中姓名的对应分数等级，可以在H15单元格中输入以下公式并向下复制到H16单元格。

=VLOOKUP(G15,$C$15:$E$22,3,0)

G15单元格的“许褚”为查找目标值，在C15:E22单元格区域中的第一列，即在C15:C22 单元格区域中查找“许褚”，然后返回C15:E22区域中的第3列的值，即得到分数等级为“B”，最后注意第4个参数数字0，代表精确匹配。

3

案例：VLOOKUP完成各种方式的精确查找

如下图所示，A~G列是基础数据源，其中A列是部门，B列是员工号，C列是姓名，D~G列是基本工资、绩效奖、加班费及总工资。根据这个数据源进行各种方式的精确匹配查找。

常规查找

先写一个最基础的公式，如下图所示。

根据J3:J5单元格区域的员工号，查询每个员工号对应的姓名。在K3单元格中输入以下公式并向下复制到K5单元格。

=VLOOKUP(J3,B:C,2,0)

输入公式的时候，千万别忘记第4个参数数字0。在VLOOKUP函数查询的时候，如果数据源与本案例相似，是从第一行开始一直向下延伸，建议大家使用整列引用，这样会具有更好的扩展性，并且不影响计算效率。

文本数字查找

有多少人这样处理过问题：当有多列数据时，用VLOOKUP函数引用区域，在写第3个参数的时候，开始去手动数“1，2，3，…，45，46”，然后在第3个参数的位置写下46。相信很多人都这样做过，我曾经就是其中一员。接下来看看，如何学会“偷懒”技能。

如下图所示，在K8单元格中输入“=VLOOKUP(J8,”，然后用鼠标选中B:G区域，注意这时候不要松开鼠标，鼠标指针为白色空心十字的状态，仔细看看屏幕上出现了什么？在鼠标指针右侧的地方会显示“1048576R×6C”，R表示Row，即行的意思，C表示Column，即列的意思。

这说明选择了1 048 576 行×6列的区域，所以第3个参数不用数，直接写6就可以了。在不同的Excel版本中，提示行列数显示的位置和方式不同，有的是在选定区域的左上角，有的仅显示“6C”，但无论在哪儿，仔细找一找都会找到的。

最后第4个参数0，表示精确匹配，完整公式为：

=VLOOKUP(J8,B:G,6,0)

将公式向下复制到K10单元格，这时出问题了，为什么后面得到的都是错误值，如下图所示。

再仔细看看数据源，J9和J10单元格的左上角都有个小“绿帽子”，最常见的“绿帽子”一般是这几种情况：文本型的数字、错误值、单元格中的公式与周围环境不一致。

这里明显不属于后两者，所以是文本型数字。VLOOKUP在查询的时候和MATCH一样，都是会根据数据类型判断的，所以要将文本型数字转化为数值型，“减负”即可。

=VLOOKUP(--J8,B:G,6,0)

如下图所示，即可得到正确结果。

查无此人

再次根据员工号查姓名，如下图所示。

在K13单元格中输入公式“=VLOOKUP(J13, B:C,2,0)”，并向下复制到K15单元格，然后发现K15单元格返回结果为#N/A，因为原始数据中没有“209”这个员工号，所以对于错误值标注一下“查无此人”，将公式完善为：

=IFERROR(VLOOKUP(J13,B:C,2,0)," 查无此人 ")

查找一系列值

前面都是根据目标值返回一列的结果，如何使用VLOOKUP函数返回n列的信息呢？根据列的变化返回一系列数字，自然想到了COLUMN函数，如下图所示。在K18单元格中输入公式：

=VLOOKUP(J18,C:G,COLUMN(B:B),0)

公式需要复制时，别忘记使用“图钉”，将公式完善，并复制到K18:N20单元格区域。

=VLOOKUP($J18,$C:$G,COLUMN(B:B),0)

这里必须提示一点，我们根据数据源C列的姓名，返回后面D列的值时，千万不能将VLOOKUP函数的第3个参数写成COLUMN(D:D)（它的结果是数字4）， 而是要写为COLUMN(B:B)（它的结果是数字2）。因为我们选择的区域是C:G，返回的结果是这个C:G区域中的第2列。写公式的时候需要知道每一步的结果是什么，公式中要的是什么。

逆向查找

如下图示，根据数据源中C列的姓名，查询A列的部门和B列的员工号，形成逆向查找，我们先把公式写出来，然后再详细讲解，在K23单元格中输入以下公式，并向下向右复制。

=VLOOKUP($J23,IF(,$C$2:$C$9,A$2:A$9),2,0)

这个公式中J23、2、0都是常见的参数，查找区域IF(,$C$2:$C$9,A$2:A$9)是此公式的关键，它具体表示什么意思呢？下面我们对它进行剖析一下。

我们之前讲IF函数的时候，已经讲过最基础的用法。这里来回忆一下。

公式“=IF(1,"a","b")”的结果为“"a"”；公式“=IF(0,"a","b")”返回的结果为“"b"”。那么数字1相当于TRUE，数字0相当于FALSE。

我们将IF函数的第1个参数变成一个数组“=IF(,"a","b")”，它的结果同样是一个数组“{"a","b"}”，与IF中的第1个参数是一一对应的关系。

所以IF(,$C$2:$C$9,A$2:A$9) 的结果就是将C2:C9放前面，A2:A9放在后面，构造成一个8行2列的数组，如下图所示。

这个区域已经构造完了，将它作为VLOOKUP函数的第2个参数。为什么第3个参数要写数字2呢？因为构造的区域只有两列。

不知道大家有没有注意一个细节，前面写公式的时候，我们都是选择整列引用，而到了这里却只限定了第2行到第9行区域？这是因为在IF函数中，经过 的数组运算，如果选择了整列，那相当于对整列的1 048 576行数据做计算，你想想计算效率能高吗？

查找指定列

如下图所示，根据J列的姓名，查找相应人员对应工资科目的明细，科目信息是根据第27行的标题而定的，我们不知道要查找的是第几列，遇到这种情况该怎么办呢？可以考虑用MATCH函数。在K28单元格中输入公式：

=VLOOKUP($J28,$C:$G,MATCH(K$27,$C$1:$G$1,0),0)

将公式向下向右复制，千万别忘记用“图钉”，另外还有几个方面需要注意。

（1）MATCH函数的第2个参数不能随手写为$1:$1。“MATCH(K$27,$1:$1,0)”的结果返回的是4，整个公式是指从C:G区域中返回第4列的值，不是我们要查找的内容。前面我们选择的区域是C列到G列，为了简单一些，我们把查找标题的范围也限制在C列到G列，所以公式就是“$C$1:$G$1”。

（2）有的读者认为“,0),0)”部分重复了，于是公式写到“VLOOKUP($J28,$C:$G,MATCH (K$27,$C$1:$G$1,0))”这里就结束了。这样的公式并不完整，前面的0是MATCH函数的，而不是VLOOKUP函数的。初学函数，务必把每一个参数写全，用来保证工作的准确性。

通配符查找

我们来查找第一个姓黄的人员的总工资，公式为：

=VLOOKUP(" 黄 *",C:G,5,0)

返回结果为“8 400”，注意，公式中第3个参数的数字5不是数出来的，而是选择区域的时候，按住鼠标不放，自动标识出来的。

查找第一个姓黄的且姓名为两个字的人员的总工资，公式为：

=VLOOKUP(" 黄 ?",C:G,5,0)

返回结果为“6 890”，对应的是“黄忠”的总工资。

好了，今天的内容就是这些，希望对你有所帮助。

本文内容摘录于

北京大学出版社新书

《白话聊Excel函数应用100例》

▼

Excel Home打造的Excel函数实战指南。以降低学习曲线为宗旨，真正能看得懂、学得会、用得巧的函数实战书。