excel使用vlookup时有些数据明明存在却显示NA错误

这你可问对人了，作为从业 4 年的数据分析师，VLOOKUP 函数应该是 Excel 中最常用，其实也是最基础的函数，但这个函数真的用起来还是非常灵活的。用法其实非常多样化！

一、介绍

这个函数其实就是个查找函数，他可以正向查找，逆向查找，多条件查找，还能模糊匹配。

基础语法极其简单，一个四个参数。

第 1 参数 lookup_value：表示要在表格或区域的第一列中查询的值。

第 2 参数 table_array：表示要查询的单元格区域，这个区域中的首列必须要包含查询值，否则公式将返回错误值。

第 3 参数 col_index_num：用于指定返回查询区域中第几列的数值。

第 4 参数[range_lookup]：可选，决定函数的查找方式，如果是为 FALSE 或 0，则是采用精确匹配方式；如果为 TRUE 或 1，则使用近似匹配方式，同时要求查询区域的首列必须按照升序进行排序。

有人说了，上面这个一看就会，上手就废，有没有啥好方法可以解决这个问题？

比如说我想要根据左侧的表，取把右侧的表格填写完成。

在 F2 单元格中输入公式：=vlookup(E2,$A$2:$C$12,3,0)，然后向下拖动填充公式至 F5 单元格，所要查找的学生数学成绩就出来了。

解析下：在 A2:C12 这个表格，在第一列查找贾朵朵这个人，找到之后，找到对应的第三列，然后精准返回数学值。

注意事项：

查找的人一定是在搜索范围的「第一列」查找的人与搜索范围的「第一列」要一致，避免空格，格式等问题在下拉的时候，索引范围是跟着动的，所以一定要绝对索引。从 A2:C12 改成$A$2:$C$12，这样下拉时候，就会一直在这个范围内搜索。而不是从 A2:C12 变成 A3:C13

是不是很简单？

有同学说了，看这个还是看不明白，有啥视频或者教程，这样我可以看得更明白一点。

对于初学者，本人的建议是跟着老师学习，最好是同时有长期教课经验和牛逼工作经验的老师，保证他确实是一个实战数据分析大佬，又确实能教会别人，两者缺一，要不然就是把你教成书呆子，要不就是大肚茶壶倒饺子——有货说不出。

个人推荐知乎的一个课程，主讲老师是前 IBM 数据分析大佬猴子，课程是结合国内互联网一线大厂的案例（如下图），从基础讲起，用案例讲知识点，带练以 Excel 为代表的数据分析工具，你还可以顺便学习包括 power BI，SQL 等，传授数据分析常用十大分析方法，通过大厂的实际数据案例实操，让大家扎扎实实学会 Excel，对小白来说是很友好的选择。

二、单条件查找之：vlookup 跨工作表查找

这个案例，查找值和查找区域分别放在了两个不同的工作表，要求根据查询表中的产品编号（查找值），在产品表（查找区域）中进行查找，并将对应的产品信息返回到查询表中。

产品表

查询表

在查询表中的 B2 单元格中输入公式：=vlookup($A2,产品表!$A:$F,COLUMN(B1),0)并向右、向下拖动填充到 F7 单元格。

vlookup 跨工作表查找公式解析：

第 1 参数：$A2，查找值，公式要分别向右、向下填充，要保持向右填充查找列一直处在 A 列，所以列号固定不变，向下填充，查找的产品编号要跟着变化，所以行号无需固定；

第 2 参数：产品表!$A:$F，查找区域，因为是跨工作表查找，所以跨工作表引用的表示方法为：工作表名称!表格区域（如，产品表!$A:$F）。另外在输入公式的这一处时，可以直接通过点击鼠标切换到「产品表」工作表中，选择目标单元格区域，前面会自动加上工作表名称。同样这里的查找区域也要进行固定。

第 3 参数和第 4 参数和上面的案例相似，这里就不多做介绍了，大家不理解的可以去看下上面两个案例中第 2，第 3 参数的解析。

多条件查找之：vlookup 结合辅助列查找

如下图要求查找出 A、B、C、D 店对应月份的销量和营业额。

实际上这里的辅助列的用法就是用连接符&把多个条件，连接成一个条件，然后再使用 vlookup 函数进行查找，所以查找之前，要在查找区域所在的表建立一个辅助列，将门店和月份用连接符&连接起来，如：A2=B2&C2（A 店 1 月）

然后在 J2 单元格中输入公式：=VLOOKUP($H2&$I2,$A$2:$E$17,COLUMN(D:D),FALSE)，并向右、向下拖动填充公式至 K5 单元格。

vlookup 结合辅助列查找公式解析：

第 1 参数：$H2&$I2，表示要查找的值，即用连接符&将两个条件连接成了一个条件，连接后的查找值会变成：”A 店 3 月”，因为公式要向右、向下填充，向右填充时要保持列号固定不变，所以使用相对引用固定列号，行号要随着向下填充变化，所以无需固定。

第 2 参数：$A$2:$E$17，表示查找区域，因为第一列要包含查找值，第一列是构建的辅助列，正好是包含查找值的，同样公式要向右、向下填充，要保持查找区域不能随着公式的移动而发生变化，所以也要使用绝对引用。

第 3 参数：COLUMN(D:D)，这个参数是用于返回第几列上的值，本案例的目的是要随着公式向右复制，从而依次返回销量、销售额，参数是要随着变动的，而销量在查找区域的第 4 列，销售额在第 5 列，所以使用函数 COLUMN(D:D)返回列号 4，当公式向右填充时，会变成 COLUMN(E:E)返回列号 5，来达到返回对应列号值的目的。

第 4 参数：0，因为是精确匹配，所以该参数为 0 或 FALSE。

多条件查找之：vlookup 结合数组的用法

上面的那个案例是借助辅助列的方法来完成多条件查找，但是如果有新的数据添加进来，每次都要先更新一下辅助列，步骤有点繁琐，而接下来这个案例只要使用 IF 函数，结合数组同样也能够实现 vlookup 函数的多条件查找，而且步骤一点都不繁琐。

在 I2 单元格中输入公式：=VLOOKUP($G2&$H2,IF(,$A$2:$A$17&$B$2:$B$17,C$2:C$17),2,FALSE)，并向右、向下拖动填充公式至 J5 单元格。

vlookup 结合数组应对多条件查找公式解析：

第 1 参数：$G2&$H2，表示查找值，同样和上一个案例一样也是用连接符&将两个条件连接成了一个条件，这里的第 1 参数和上一个案例的第一参数意思是一样，所以就不多做解释。主要区别在第 2 参数。

第 2 参数：IF(,$A$2:$A$17&$B$2:$B$17,C$2:C$17)，表示查找区域，使用了 if 函数对数据组内的进行条件判断：

当为 1 时返回$A$2:$A$17&$B$2:$B$17，作为查找区域的第一列，因为这里也使用了连接符&，将门店和月份进行了连接，所以符合查找区域第一列包含查找值；

当为 0 时返回 C$2:C$17，并且作为查找区域的第二列，在第 3 参数中需要返回该列的（销量）数据；

同样公式要向右、向下填充，查找区域的第一列要使用绝对引用进行固定，而查找区域的第二列，使用相对引用，向下填充要保持行号不变，列号要随着向右的拖动变成 D$2:D$17，这样就可以在 J2:J5 区域返回营业额的数据了。

第 3 参数：2，返回区域中的第二列，虽然要返回的数据是两列，销量和营业额，但是当公式向右填充到 J2 单元格时，公式就变成了：=VLOOKUP($G2&$H2,IF(,$A$2:$A$17&$B$2:$B$17,D$2:D$17),2,FALSE)，这样 D$2:D$17 就变成了查找区域的第二列，所以返回该列的值了。

第 4 参数：同样精确匹配。

Vlookup 结合数组进行反向查找

下图表格中要求，根据姓名，查找对应的学生编号，前面的案例都是向右查找，而这个案例是向左查找。

vlookup 函数在查找时，要求查找区域的第 1 列必须要包含查找值，然后向右返回对应行的值，这个案例要返回的值在第 1 列，包含查找的值在第 2 列，所以我们可以使用 if 函数来构建一个数组，做一个位置调换，将左表的 B 列放在查找区域的第一列，A 列放在在第二列，这样就可以使用 vlookup 进行查找了。

在 H2 单元格中输入公式：=vlookup(G2,IF(,B$2:B$12,A$2:A$12),2,0) ，并向下拖动填充到 H3 单元格。

vlookup 反向查找公式解析：

第 1、3、4 参数，和前面的几个案例意思是一样的，这里面就不具体介绍了，

对第 2 参数做一下解释：IF(,B$2:B$12,A$2:A$12) ，使用 If 函数构建了一个数组，当条件满足时先返回姓名这一列，然后再返回学号这一列，数组结果为：{“张明明”,”NX-003″;”黄海”,”NX-005″;”夏红鑫”,”NX-011″;…..}这样就可以进行正常的查找了。

如果前面的关于数组的案例的公式理解了，这里就容易理解了。

看到这里的你是不是感觉有点吃力了？没关系，这很正常，我同事工作 4 年，都还没有搞清楚这个函数的基本用法。

要说的是，Excel 只是工具，要把这个工具用好，数据分析的指导是必须的，再次推荐知乎的【数据分析入门训练营】，一线互联网大厂大牛带队，直播互动讲解互联网数据分析工作流+常见分析模型运作原理，还有大厂真实案例带练实操，打破简历 0 项目经验。想转行数分升职加薪的建议领取：

三、单条件查找之：一对多查找

这里的一对多查找的意思是，通过一个条件，查找并返回所有匹配该条件的记录值， vlookup 函数在进行数据查找时，只匹配第一个符合条件的记录，后面即使有重复的记录也不会再匹配，所以就要结合辅助列、数组来完成一对多的查找。

如下图案例所示，要求找出陈强 4 月份的所有出勤日期。

在使用 vlookup 函数查找之前，要先在出勤日期这一列后面添加一个辅助列，并且使用 Countif 函数统计出姓名是第几次重复出现的。在 C2 单元格中输入公式：=COUNTIF($A$2:A2,A2),并向下拖动填充到 C30 单元格。

然后在 E2 单元格中输入公式：=VLOOKUP(“陈强”&ROW(1:1),IF(,$A$2:$A$30&$C$2:$C$30,B$2:B$30),2,FALSE)，并向下拖动填充公式，直至出现错误值。

说下解题思路：

vlookup 函数在查找数据时，遇到多条重复的记录，只会匹配第 1 条记录，后面重复的会被忽略，而想要把每一条重复的记录都找出来，就要让查找区域第一列的值都变成唯一值，

所以：需要增加一个辅助列（C 列），先通过 countif 函数，统计姓名是第几次重复出现，并和姓名连接起来，这样查找区域首列重复的姓名，也变成了唯一值（比如陈强第 1 次出现时是」陈强 1」、第 2 次出现时是」陈强 2」…），

最后再使用 if 函数，来实现将连接后的包含查找值的区域放在首列，要返回的区域放在第二列，即第 2 参数：IF(,$A$2:$A$30&$C$2:$C$30,B$2:B$30)；

那么第 1 参数：”陈强”&ROW(1:1)，将要查找的陈强和 ROW(1:1)函数构成的数字相连接，查找值就会变成：”陈强 1″，当公式向下填充时就会相应的变成：”陈强 2″，”陈强 3″，这样查找值就符合查找区域首列包含查找值的要求了。

最后第 3、第 4 参数分别是返回对应行上第 2 列的值和精确匹配。

那结果中没有查到后返回的错误值，能不让显示出来呢？

只要在外层加上 iferror 函数就可以了，完整的公式：

=IFERROR(VLOOKUP(“陈强”&ROW(4:4),IF(,$A$2:$A$30&$C$2:$C$30,B$2:B$30),2,FALSE),””)

然后还是推荐大家报名下面的免费体验课程。毕竟有人教，这些东西还是能够快速理解清楚的。有些东西文字是很难看明白的。

我写这些的时候，真的非常担心大家能不能看明白，所以还是推荐大家去看直播教程。

四、vlookup 模糊匹配，代替 IF 做多条件判断

前面几个案例都是介绍的精确匹配的用法，另外还有一个近似匹配是如何应用呢？

使用 vlookup 函数进行近似匹配查找的前提是，要查找的区域的第一列必须是按照升序排序，否则 vlookup 无法返回正确的值。

所以这里就利用了 vlookup 函数模糊匹配的这个功能来进行条件判断，只要设置好条件的区间，并且按照升序排序，就可以代替 if 函数做条件判断。

下图这个案例是根据左表业绩的等级评定标准，对右表相应的人员业绩进行评定，如果使用 if 函数，需要嵌套好几层，这里使用 vlookup 函数简短的一段代码就可以搞定。

在 G3 单元格中输入公式：=vlookup(F3,A$3:B$7,2,1) ，向下拖动填充到 G11 单元格。

vlookup 做多条件判断公式解析：

第 1 参数：F3，表示要查找的值，即：业绩；

第 2 参数：A$3:B$7，表示要查找的区域，因为本案例是近似匹配，所以查找的区域第一列必须要按照升序排序，条件区间分别为：0 代表，0-59、60 代表，60-69、70 代表，70-79、80 代表，80-89、90 代表，90-99。

第 3 参数：2，返回等级所在的列号。

第 4 参数：1，模糊匹配，也可以使用 TRUE 表示，Vlookup 模糊匹配的用法是：在对查找区域内进行查找时，如果没有找到相等的值，则会将小于查找值的最大值返回出来。所以查找 F3 单元格的业绩（49）时，查找到的是 0，返回的等级是 E。

关于 vlookup 函数的使用方法，今天的分享就到这里了，内容比较多，建议大家收藏，然后仔细的看完，如果这些方法都掌握了，相信能够解决 Excel 工作中的大部分查找难题。

如果你觉得这篇文章让你掌握了 vlookup 函数的更多使用方法，就给我点赞吧。

最后，如果你想更系统地学习数据分析，下面这个知乎官方开设的「数据分析实战训练营」真的挺不错的，再次推荐给大家~这个课为期 3 天，直播讲解+学习社群的形式，对小白友好。

可以用最短的时间掌握职场上常用的工具操作、分析技巧方法、和数据思维都能讲明白，连我这种老鸟都觉得很受用，而且只要 1 毛钱，感兴趣的朋友可以试试哦，链接放这啦

本文作者：@透视哥