精通vlookup

excle

vlookup函数

VLOOKUP函数是Excel中的一个纵向查找函数，可以实现多个表格之间导入及查询数据等功能。功能是按列查找，最终返回该列所需查询序列所对应的值；与之对应的HLOOKUP是按行查找（极少用到，因为不符合正常人的做表习惯）。

语法规则

VLOOKUP( value, range, index, bool )

入门示例

一、 正向查找

1.根据姓名查找对应的体重

H6单元格处输入：

=VLOOKUP(G6,$B$2:$C$5,2,0)

“$” 含义见下方拓展知识

二、逆向查找

1. 根据姓名查找对应的编号
   I6单元格处输入：

=VLOOKUP(G6,IF({1,0},$B$2:$B$5,$A$2:$A$5),2,0)

“IF” 见下方拓展知识

三、多条件查找

1.查找体重90的坦克是什么品种？
H11单元格处输入：

=VLOOKUP(B3&C3,IF({1,0},B2:B5&C2:C5,D2:D5),2,0)

多条件输入后记得是按【Ctrl+Shift+Enter】才能达到正确答案。

“&” 见下方拓展知识。

四、 查找多列

上面入门示例-正向查找-1，只查询了体重，位于第二列，index直接写为2，如果要继续查询品种、性别，那么vloopup的第三个参数index该如何实现自动变化呢？

H6单元格处输入

=VLOOKUP($G6,$B$2:$E$5,COLUMN(B1),0)

我们对比一下发生了那些变化

• 第一个参数查询值，我们希望往右拉的时候，始终查询的是G列，而不是H，I，J递增，所以加上$锁定。
• 第二个参数范围，因为加上了品种和性别，所以范围我们也要加大到E5
• 第三个参数列数，我们引用了一个新函数COLUMN(),返回当前所处的列数，
  比如：COLUMN(B1）就是第2列，COLUMN(C1）就是第3列。相当于巧妙的利用COLUMN()的返回值充当变化的index。

五、 模糊匹配

比如我们要给所有狗狗的体重打分，规则为：小于80的偏瘦，80-100的正常，100以上的肥胖。
首先我们根据要求制定规则：

然后在F2单元格处输入=VLOOKUP(C2,$B$8:$C$10,2,1),其实就是把查找范围取我们定义的规则。

也就是利用VLOOKUP对体重这个数值，在规则这个范围里查找，取第二列的值作为返回结果，最后一个参数是模糊查询1。

“精准查询与模糊查询区别” 见下方拓展知识

思考重复值

学到这里有没有发现 VLOOKUP 有个缺点，就是只能有一个唯一返回值，如果有多个符合条件，第四个参数精准匹配的话，会返回符合条件的第一个，模糊匹配的话会返回符合条件的最后一个，如果我们想要返回符合条件的所有结果，比如筛选出长毛的狗狗有哪些。我们需要对源数据进行改造：

1. 添加辅助列
   

2. 在B2单元格输入 =E2&COUNTIF(E$2:$E2,E2)
   

“COUNTIF()” 见下方拓展知识、

1. 在A8单元格输入：=VLOOKUP($A$7&ROW(A1),B2:C5,2,0)

“ROW()函数” 见下方拓展知识、

实践练习

1. 现有一个打乱班级的学生名单，如图。要求按照班级把对应的学生查找出来。

答案：
1. 先做辅助列
在A2单元格处输入=B2&COUNTIF($B$2:B2,B2)
然后下拉得到班级序号数据

1. 在D14单元格处输入：=VLOOKUP(D$13&ROW($A1),$A$2:$C$11,3,0)

得到如图的结果

拓展知识

1. $ ：锁定，表示单元格的绝对位置，不受拖拽填充的影响。比如：A3单元格，如果是 $A3 表示横向不变，下拉后会填充A4,A5,A6 ……,向右拉依旧填充A3,A3,A3……，因为横向A锁死了。如果$A$3如论怎么拉都会是A3。

2. IF函数
   语法：=IF( 判断条件，结果为真的返回值，结果为假的返回值 )
   示例：
   =IF(1,"真","假") 结果显示真
   =IF(0,"真","假") 结果显示假

   IF还有一种高级用法，用数组重新构建查找区域：

   =IF({1,0},A1:A5,B1:B5) 结果是
   

   =IF({0,1},A1:A5,B1:B5) 结果是
   

3. & 符
   表示且，通常用于合并单元格内容，或作为多个条件的并列关系。
   比如：
   

4. 精准匹配和模糊匹配？
   精准匹配 0 ， 相当于等于，编程里写作'===', 需要完全相等。直接返回第一个符合条件的值， 如果没有符合条件的值，返回错误码 #N/A。
   模糊匹配 1 ，会查询范围内所有值，返回最后一个符合条件的值，如果没有符合条件的，将会返回小于value的最大数值，所以上面体重的案例，没有狗狗的体重会是0，返回小于30的最大数值0，0对应的是偏瘦，哈哈 理解不？？�

5. COUNTIF()函数
   语法规则 COUNTIF( range , value ）
   返回范围内符合条件的个数
   例如：查找品种是长毛的个数
   

6. ROW()函数
   同COLUMN()类似，ROW()返回单元格所在行，通常用于需要纵向下拉的地方。

7. 不同工作簿数据如何拿到

=工作簿名!A2

总结

函数只是我们解决问题的工具，实际需求千变万化，狗狗案例的解决方法，要思考为什么要这么用，掌握方法，理解思路，才能以不变应万变。

思考以下问题，答对才算真正会用了。

• 什么情况下需要用COLUMN()函数？
• 什么情况下需要用ROW()函数？
• 为什么要用COUNTIF()函数给值标上序号？
• 为什么偏瘦/肥胖，或者 及格/优秀 这种范围的查询要用模糊匹配
*