Excle表格：vlookup函数详解，通俗易懂篇

Vlookup函数是电子表格中的一个查找匹配函数，适用于Excel与WPS各个版本。

在所有的函数中，vlookup函数应该是使用频率最高的函数之一，对新手小伙伴来说，掌握它是十分有必要的。

本篇文章从其基本用法、进阶用法、匹配不出来原因分析、如何规避错误值、冷门用法科普等五个维度来详细介绍下vlookup函数，希望能够帮助小伙伴们快速入门、以及加深对此函数的认识。

一、基本用法

语法解释

vlookup函数一共有4个参数，参数1要搜索的值、参数2搜索区域、参数3返回的列数、参数4匹配模式，参数有点多，没关系，我们用一个实例来看下每个参数的具体作用。

精准匹配

下图中，根据左边的信息表，利用vlookup函数匹配出李白的年龄，G2单元格中的公式该如何输入？

参数1：搜索值，找谁？

虽然最终结果是获取年龄字段，但是查找值并不是年龄，而是查找“李白的年龄”，主语是“李白”，所以参数1、查找值是“李白”，也就是F2单元格。

参数2：搜索区域，去哪找？

重点来了：查找区域的第一列必须是搜索值所对应的列(查找对象必须位于查询区域的最左列)，参数1搜索值是“李白”，对应到左边到表格就是“姓名”字段，所以参数2搜索区域必须以B列作为首列，以B列作为首列向后拖动到我们需要匹配的值所在列，也就是D列年龄列(也可以继续向后拖动，只要包括年龄列就可以)，所以参数为就是B:D列。

参数3：在第几列？

这里有个误区，很多小伙伴觉得左边的匹配表一共4列(A-D列)，年龄列在D列，也就是第4列，所以参数3输入了“4”，这是错误的。

参数3实际指的是返回值(年龄)在搜索区域中所处的列数，也就是在参数2框选区域中所处的列数，上面我们框选了B:D列，实际上仅框选了3列，B是第一列、C是第二列、D是第三列....所以返回值“年龄”在第三列，参数3输入数字“3”。

参数4：匹配模式？

匹配模式分为精准匹配(输入0、FALSE或者省略都可以)、近似匹配(输入1或者TRUE)，最常用的当属精准匹配，也就是要完完全全找到“李白”这个人，而不是“李小白”、“李白小”，所以参数4输入数字“0”，表示精准匹配，也可以直接省略或者输入FALSE。

日常工作中，99%的情况下都是使用vlookup函数的精准匹配，也就是参数4大家可以默认输入0。

通过以上例子，介绍了vlookup函数的最基本、最实用的用法，最容易出错的两点在于：

1、参数2/搜索区域 的首列必须是返回值所在的列；

2、参数3/列数 必须是返回值在搜索区域对应的列数。

---

近似匹配

上面说到99%的情况下，vlookup函数的参数4都是0，也就是精准匹配，那么还剩下1%的情况，需要用到vlookup函数的近似匹配，在表格中也有非常合适的应用场景，那就是数据分组功能。

下图中如何根据左侧A-B列的评级表，去匹配每位同学所属的评级？

首先建立一列辅助列，在G2单元格内输入公式：=VLOOKUP( F2,B:C,2,1)，向下填充，完成区间匹配。

这里利用vlookup函数近似匹配的功能，它会返回小于等于查找值的最大值。

查找数字56，等于查找 “小于等于56的最大值”，60/80/90都要大于56，只有0小于56，所以返回0对应的评级“不及格”； 查找数字88，等于查找 “小于等于88的最大值”，0/60/80都要小于88，但80最大，所以返回80对应的评级“良好”；

最重要的一点，利用vlookup函数近似匹配之前，需要对匹配表数据进行升序处理，即上图中先对辅助列B列升序(上图中B列数字由小到大排序)。

因为vlookup函数近似匹配采用的是二分法，一般是从中间开始向上下两端查找，不断二分，默认数据升序处理。

不升序会怎么样？

下图中，匹配序列处于乱序状态，利用vlookup函数近似匹配、返回小于等于查找值的最大值，正确结果应该是a1,(6是小于等于6.5的最大值)，但是函数返回结果却是a2。

二分法中间开始查找，序列中间值为7,7要大于查找值6.5，所以继续向上查找，上面的1要小于6.5，查找停止，返回1对应值。所以，在近似匹配的时候，切记要升序处理。

 

二、进阶用法

进阶用法主要是vlookup函数搭配辅助列或者其它函数，来实现数据匹配，常用的方法主要分为以下几种。

在此之前，还有一个比较重要的概念就是 单元格引用 ，在vlookup函数匹配的时候，参数1与参数2经常涉及到相对引用与绝对引用，十分重要。Excel入门必看篇，单元格的相对引用、绝对引用与混合引用

多列查找

1、结合column函数

下图中，匹配各同学的性别、年龄、城市三个字段，直接利用COLUMN函数(返回单元的列)，动态返回需求列。公式=VLOOKUP($F2,$A:$D,COLUMN(B2),0)，其中参数1与参数2皆涉及到单元格的引用。

2、结合match函数

上图需要匹配的列与原表列的位置顺序一样，可以用COLUMN函数解决，如果顺序不一致的话，需要结合match函数使用，公式=VLOOKUP($F2,$A:$D,MATCH(G$1,$A$1:$D$1,0),0) 注意其中的引用方式。

match函数返回查找值在数组中的位置，比如查找字段“性别”，它在数组“A1:D1”中处于第2位，所以vlookup函数参数三就等于2。

 

以上两种方式都是通过动态变更参数三，从而完成多字段匹配。

逆向查找

由于vlookup函数的参数2、查找区域首列必须为搜索值对应的列，如果返回值所在的列在搜索值对应的列前方的话，vlookup正常用法无法完成匹配。

下图中，根据A-B列的数据源，匹配出E列的学号，根据前文，搜索区域只能从B列开始、向后拖动，但是返回值在A列，肯定查不到正确的结果。

 这种情况我的建议是调整列的位置，比如在学号列前方复制插入姓名列，逆向转正向匹配。

当然也可以使用INDEX+MATCH组合以及XLOOKUP函数解决逆向匹配的问题，这里不是我们的讨论范围，感兴趣的小伙伴可以自行了解。

 

多条件匹配

多个字段匹配的情况，可以在源数据的基础上，增加一列辅助列，利用“&”将各列数据进行连接。

模糊查找

“*”是通配符，代表0到多个字符，"*"&D2&"*"则表示包含D2关键字的任意字符串：

一对多查找

下图中，根据B-C列数据源，匹配出E列部门的所有员工姓名。由于vlookup函数只能返回首个值，然后有多个员工，这里利用COUNTIF函数将每个部门的个数进行编号。

辅助列公式=B2&COUNTIF($B$1:B2,B2)，注意其中的绝对引用。这样每个部门都会被标上序号。

接着F2输入公式=VLOOKUP($E$2&ROW(A1),A:C,3,0)，公式下拉，即可返回多个结果。

三、匹配不出来的原因

明明有数据，vlookup却匹配不出来，在工作中是比较常见的。

第一种常见错误是匹配区域未绝对引用、发生变化所致。下图中匹配年龄字段。

在E2单元格内输入公式=VLOOKUP(D2,A2:B9,2,0)，由于参数2未采用绝对引用，公式在下拉到“赵云”的时候，引用区域变成了“A4:B11”，区域内并无此人，当然查找不到数据。

正确的做法是：参数2引用区域采用绝对引用(选中引用区域，按下F4键)

 

第二种常见错误是匹配字段中存在不可见字符，比如空白符，利用Ctrl+H将空白替换掉就可以了。

其它错误可以参考下面这篇文章。明明有数据，为什么我的VLOOKUP总是匹配不出来？

四、规避错误值

vlookup函数在未匹配到数据的情况下，函数会返回#N/A，如果想到规避这种错误值，可以利用IFNA或者IFERROR函数。

公式=IFNA(VLOOKUP(D2,A:B,2,0),"查无此人")，在vlookup函数外层嵌套一个IFNA函数，表示内层的函数表达式(参数1)结果为#N/A，则返回指定的值(参数2)，否则返回表达式本身的结果。

IFERROR函数与IFNA用法一致，只不过前者比后者更加强大，关于Excel的错误类型总结，可以参考下面的文章。Excel入门科普文，表格常见错误类型总结

五、冷门用法(仅作了解)

这里的冷门用法是指公式写起来比较麻烦、或者有更高效的函数可以替代，让我们一起看看吧~

IF函数{1,0}用法，每位使用vlookup的童鞋不得不面对的问题。

比如vlookup函数逆向查找，公式=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)

这里完全可以用match+index函数替代(高版本可用Xlookup)。

 比如多条件匹配，公式=VLOOKUP( E2&F2,IF({1,0},A:A&B:B,C:C),2,0)，需要Ctrl+shift+Enter三键齐按完成公式的输入。这里也可以用sumif或者sumproduct函数替代。

vlookup函数搭配IF函数的{1,0}功能是不推荐大家使用的，如果想要了解IF{1,0}的具体原理，可以看下面这篇文章。拜托，Vlookup函数的这个功能真的别再用了

最后再给大家分享一个vlookup的小技巧，提取固定数字字符串的技巧。

下图中，提取A列是首次出现的手机号码，输入以下公式：

=VLOOKUP(0,MID(A2&"a",ROW($1:$99),11)*{0,1},2,0)，Ctrl+shift+Enter三键。