VLOOKUP函数让大家又爱又恨，其实它功能强大满足各种查询需要，我们用它查工资、查住址……无所不能。稻壳儿整理出VLOOKUP函数常见用法，真正提高你的办公效率。

- 01 -

了解函数语法

VLOOKUP应用实例教程

VLOOKUP应用实例教程

公式：

=VLOOKUP（查找值，数据表，列序数，[匹配条件]）

解析：

公式的含义=VLOOKUP（找什么，在哪找，第几列，精确找还是大概找一找）

第4个参数[匹配条件]：

  • 为0时代表精确查找（必须完全一致）
  • 为1时代表模糊查找（缺省也默认模糊查找，如果找不到精确匹配值，则返回小于“查找值”的最大数值）

- 02 -

精确查找

如查找某学号学生的成绩，学号固定唯一，这属于精确查找：

VLOOKUP应用实例教程

▲利用函数公式，可以快速、精准查询学生成绩

公式：

=VLOOKUP(E2,A2:C10,3,0)

解析：

查找值为E2单元格的值，查找范围为A2：C10，返回地3列（C列），查找方式为0精确查找。

- 03 -

模糊查找

模糊查找在很多时候用处也很大，比如给某个数值自动划分优良差等级：

VLOOKUP应用实例教程

公式：

=VLOOKUP(C2,$G$2:$H$4,2,1)

解析：

  • 查找范围为$G$2:$H$4（注意加上$符号即可将范围锁定，下拉拖拽公式时不会发生改变）
  • 利用模糊查找0，即得到了对于各个分数段等级（注意数据区域要按从小到大排列，因返回值为小于“查找值”的最大数值）

- 04 -

字符模糊查找

查找下图名称包含AA的产品的价格：

VLOOKUP应用实例教程

▲第4个产品的字符名称包含AA

公式：

=VLOOKUP('*'&D2&'*',A2:B7,2,0)

解析：

通配符“*”来表示任意文本，把*放在字符的两边，即'*' & 字符 & '*'代表包含AA

- 05 -

反向查找

即通过查找右边列查找返回左边列，一般VLOOKUP从左向右查找，那么反过来怎么做呢？

VLOOKUP应用实例教程

▲查找姓名2对应的学号

公式：

=VLOOKUP(E2,IF({1,0},B2:B10,A2:A10),2,0)

解析：

  • IF({1,0},B2:B10,A2:A10)是实现反向查找的关键，相当于将数组A列和B列互换，这里1和0不是实际意义上的数字，而是1相关于TRUE，0相当于FALSE；
  • 当为1时，它会返回IF的第二个参数（B列），为0时返回第二个参数（A列）。

- 06 -

多条件查找

查找下图中华东地区B产品销售额（需要满足地区为华东，产品为B）

VLOOKUP应用实例教程

VLOOKUP应用实例教程

VLOOKUP应用实例教程

公式：

{=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)}

解析：

  • 这里利用数组（地区和产品分类）求得均满足条件的销售额，E2&F2连接在一起作为整体要查找值，A2:A10&B2:B10对应连接在一起作为被查找部分，再利用IF({1,0}把C列合并。
  • 这样数组被重新配置成一个新的结构，相当于一个新表，下面就可以利用VLOOKUP的基本查找功能了
  • 公式两边的大括号 {公式} ，不是手输，是鼠标放在编辑栏同时按Ctrl+Shift+Enter得到的，俗称三建结束或组合键结束。

- 07 -

一对多查询

VLOOKUP能否一对多查询？即符合某查询条件的多个查询结果均返回。比如经典组合数组函数INDEX+SMALL+IF。

但是数组函数有个缺点是对于内存耗用较大，经常使用表格的朋友都遇到过内存耗用过多软件、电脑罢工的情况，而VLOOKUP函数可以不使用数组运算，大大减少内存消耗。

VLOOKUP应用实例教程

VLOOKUP应用实例教程

▲如上图，求供货商A的所有供货产品名称，

- 7.1 -

VLOOKUP应用实例教程

公式：

=IF(B2=$E$2,COUNTIF($B$2:$B2,$E$2),"")

解析：

  • 先在A列增加辅助列，对于符合条件的按1、2、3……在A列自动编号，查询时只要查询编号就可以了。
  • 在辅助列中，对于不是供货商A的显示为空，是供货商A的依据$B$2:$B2区域的值计数。这就实现了供货商A在辅助列按1、2、3……编号了。这里注意（$）符号的运用。

- 7.2 -

VLOOKUP应用实例教程

公式：

=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

解析：

ROW(A1)的值等于1，ROW(A2)的值等于2，利用VLOOKUP查找ROW(A*)，返回第3列C列，就返回了所有、满足条件的值。IFERROR函数的作用是对于查询不到的返回空，屏蔽错误值。

- 08 -

同时查询多列

VLOOKUP函数第3个参数表示返回的值在第几列，我们经常将第3个参数修改为数字2、3、4……实现，如果项目很多修改起来很不方便。我们可以

通过COLUMN函数，一次输入公式查询所有按顺序的返回结果：

VLOOKUP应用实例教程

公式：

=VLOOKUP($G2,$A:$E,COLUMN(B2),0)

解析：

通过COLUMN函数产生列号从而得到2、3、4……，替代参数3，无论多少有个项目都可以实现一个公式查询所有返回结果。这里也注意（$）符号的运用。

- 09 -

提取电话号码

如果电话号码混杂在一串杂乱无章的字符中，我们怎么能够把它提取出来呢？如下图，是不是感觉无从下手？VLOOKUP就能做得到。

VLOOKUP应用实例教程

公式：

{=VLOOKUP(0,MID(B2,ROW($1:$99),11)*{0,1},2,0)}

解析：

  • 这里利用MID函数将文本拆成分别从第1个字符、第2个字符……开始的11位长度的字符串，然后分别乘以{0,1}。如果拆出来的字符串为11位文本，再乘以0或1，结果为错误值{错误，错误}，如果拆出来的字符串为11位数字，再乘以0或1，结果为{0,18812345678}。
  • 接着发挥VLOOKUP的作用，在新数值区域第一列用0查找时，返回的结果便是0对应的11位电话号码。

通过学习VLOOKUP，我们在处理表格的时候，并不仅仅只会复制粘贴。有些文档处理并不需要我们反复地操作，这样既费时又容易出错，记住几个简单的公式就能帮你搞定所有的难题。

查看原文 >>
相关文章