vlookup函数的使用方法

在日常工作中，总是会用到vlookup函数，今天和大家分享3大类18种vlookup函数的经典用法，从基础用法到高阶用法，帮助大家提高处理数据的效率。

基础用法

语法：VLOOKUP(查找值，数据表，序列数，匹配条件）

语法说明：

【查找值】按照某个值来查找想要的数据，即查找的对象。

【数据表】在什么范围内查找，即查找的区域，需要按F4绝对引用。

【序列数】匹配数据在查找区域的第几列，第几列指的是查找区域内想要返回的值所在的列。

【匹配条件】0或FALSE代表精准匹配，1或TRUE代表近似匹配。

1、精准查找

公式：

=VLOOKUP(G17,$C$16:$E$21,3,FALSE)

根据货品类型来查找对应的单价和数量，如下图：

需要注意的是C16:E21查找区域需要用$符号绝对引用定位，否则当下拉公式的时候，查找范围会跟随向下移动，最终会导致匹配数据出错，如下图：

2、近似匹配

公式：

=VLOOKUP(H27,$C$26:$D$31,2,TRUE)

近似匹配适合用在区间查找，查找销售额在某个区间的提成比例，匹配条件用的TRUE，如下图：

3、反向查找

当我们查找的对象所在列，在被查找的值所在列右边，我们可以把查找的列移动，然后再进行查找，即就是反向查找，如下图：

4、多条件查找

连接列公式：=C48&D48

公式：

=VLOOKUP(H48&I48,$B$47:$F$52,5,FALSE)

根据水果类型和产地查找市场价，可以添加辅助列，用连接符&把查找区域的水果列和产地列连接，然后再查找，如下图：

嵌套进阶用法

1、屏蔽错误值（IFERROR）

当查找对象在查找范围内找不到，就会报错，如下图：

有时候我们需要把这种查不到从而报错的项，显示为空白单元格，或者其它的内容，可以用IFERROR函数和vlookup函数嵌套使用达到效果，如下图：

公式：

=IFERROR(VLOOKUP(F8,$B$6:$D$11,3,0),"")

2、关键词查找

关键词查找，也叫模糊查找，就是查找对象内容不完整在查找区域里只有一部分，比如下图中查找对象“合作客户”只是查找区域里的“合作客户”中的一部分内容。

关键词查找一般要用到通配符，*代表匹配0个或多个字符，？代表匹配1个字符，&代表连接符。

公式：

=VLOOKUP("*"&F17&"*",$B$16:$D$21,3,0)

3、文本数值混合查找（连接符 &）

公式：

=VLOOKUP(F27&"",$B$26:$C$31,2,0)

当查找对象和查找区域格式不一致，一个为数值格式，一个为文本格式，如果用基础的vlookup函数查找会报错，我们可以用查找对象+连接符&+""来和vlookup嵌套使用来查找，如下图：

4、去除空格查找（substitute）

我们有时候遇到的数据总是会有一些空格，如果需要匹配查找数据，处理起来会很麻烦。

substitute函数单独使用是替换函数，如果和vlookup函数嵌套使用，就不需要先去除一遍空格然后再查找。

substitute函数语法：substitute（字符串，原字符串，新字符串）

语法说明：

【字符串】需要替换的单元格。

【原字符串】需要替换单元格内的什么内容。

【新字符串】想要替换的新内容。

示例：将F38单元格内的空格替换掉，如下图：

公式：

=SUBSTITUTE(F38," ","")

嵌套使用查找示例：将姓名去除掉空格后查找所对应的工资是多少？

场景1：

公式：

=VLOOKUP(SUBSTITUTE(F38," ",""),$B$37:$D$42,3,0)

场景2：

公式：

{=VLOOKUP(F46,SUBSTITUTE($B$45:$D$50," ",""),3,0)}

需要注意的是，如果这样嵌套的话，需要用大括号{}给公式包括，然后再按住ctrl+shift+Enter，否则会出错#VALUE!。

5、去除不可见字符查找（clean）

有时候有些数据看起来没有什么区别，但是它是有些空白的空格，如果直接查找会报错#N/A，如下图：

这时候可以用clean函数嵌套vlookup函数使用。

公式：{=VLOOKUP(CLEAN(F56),CLEAN($B$55:$D$60),3,FALSE)}

需要注意的是我用的WPS也是需要加大括号按ctrl+shift+Enter，否则也会出错。

6、多列批量查找（column）

column是引用函数，用于返回指定引用的列号。

语法：column（需要返回的列的序号）

用法就是你想知道某个单元格在第几列，就可以用这个函数返回列数，如下图：

当我们的查找对象和查找区域格式列名顺序都一样时，可以用column函数嵌套vlookup函数进行多列批量查找，直接拖动就全部查找，如下图：

公式：

=VLOOKUP($G76,$B$75:$E$80,COLUMN(B1),0)

需要注意的是$G76查找对象需要引用，行变列不变，否则拖动会出错。

7、多列动态查找（match）

match函数的用法和vlookup函数差不多，都是匹配函数，match函数是相互匹配，可以查找某个值在查找区域的那个位置，例如查找A列的姓名在B列的那个位置。

match函数和vlookup函数嵌套，可以多列动态查找，不像和colunm嵌套一样需要查找对象列名顺序都要和查找区域一致才行。

match函数语法：match（查找值，查找区域，匹配类型）

语法说明：

【查找值】查找的对象。

【查找区域】在那个区域查找，查找范围。

【匹配类型】0代表精准查找，1代表模糊查找。

match函数和vlookup函数嵌套使用，可以多列动态查找，如图示列：

公式：

=VLOOKUP($G106,$B$105:$E$110,MATCH(H$105,$B$105:$E$105),0)

原理：match函数会返回查找对象的列在查找区域的位置数，然后vlookup在进行查找匹配。上述公式相当于就是

=VLOOKUP($G106,$B$105:$E$110,3,0)

8、一对多查找（countif）

案列：查找同一个部门不同的员工是哪些？

第一步：添加辅助列，用连接符&和countif函数给部门加上序号，例如市场部有三个分别标上序号，如下图：

公式：

=C85&COUNTIF($C$85:C85,C85)

第二步：再按照辅助列来匹配市场部的员工，匹配不到的员工就留空白单元格。

公式：

=IFERROR(VLOOKUP($G85&COLUMN(A1),$B$84:$D$92,3,0),"")

公式解释：这里嵌套column引用函数是为了给查找值加上序号数，去匹配辅助列；iferror函数是将部门没有员工的保留空白单元格显示。

9、多行合并查找

案列：查找同一个部门不同的员工是哪些？

第一步：添加辅助列，先将市场部的员工查找出来连接在一起，如下图：

公式：

=C98&IFERROR("、"&VLOOKUP(B98,B99:$D$106,3,0),"")

第二步：再使用vlookup函数根据部门名称，匹配辅助列，如下图：

公式：

=VLOOKUP(F98,$B$97:$D$105,3,0)

10、多表混合查找（if）

if函数是条件判断函数。

if函数语法：=if（条件，真值，假值）

解释：当满足条件时，返回一个真值，否则就返回一个假值，这个函数和SQL语句用法一样的。

案列：根据是否是会员及消费金额来匹配对应的赠品是什么？

场景：会员和非会员是单独存放的表格，需要查找在一个表格中，如下图：

公式：

=IFERROR(VLOOKUP(G111,IF(F111="是",$B$111:$C$114,$B$117:$C$120),2,TRUE),"")

解释：这个就是把vlookup函数的查找范围先用条件函数判断一下该去那个表范围查找。如下图:

11、跨多表查找（indirect）

indirect函数可以把一个文本地址转换为真正的地址，即返回文本字符串所指定的内引用。

indirect函数语法：=indirect（单元格引用，引用的样式）

【单元格引用】对指定单元格中数据内容的引用。

【引用的样式】指定单元格的引用类型，可不写。

案例：查找相应产品1月到3月的销售金额，每个月的销售金额单独存放在不同的工作表。

可以使用column函数得到表1，表2，表3的地址，再用indirect把文本地址转换，不然会出错，具体用法如下图：

公式：

=VLOOKUP($B126,INDIRECT("表"&COLUMN(A1)&"!$A$3:$F$8"),6,0)

高阶用法（数组）

1、反向查找

上面我们反向查找是通过构建辅助列或者是手动交换两列位置来进行查找，其实可以数组自动交换位置实现反向查找，如下图：

公式：

=VLOOKUP(G7,IF({1,0},$C$7:$C$11,$B$7:$B$11),2,0)

解释：IF({1,0}表示无论值是真还是假都返回值

2、多条件查找

案例：查找相同水果到不同产地的市场价。

公式：

=VLOOKUP(G17&H17,IF({1,0},$B$17:$B$21&$C$17:$C$21,$E$17:$E$21),2,0)

其中IF({1,0},$B$17:$B$21&$C$17:$C$21,$E$17:$E$21)就是将水果和产地连接一起成一列和市场价返回成新的数组，当做vlookup函数的查找区域，如下图：

3、一对多查找

案列：查找不同区域的员工有哪些。

公式：

=IFERROR(VLOOKUP(COLUMN(A1),IF({1,0},COUNTIF(INDIRECT("b27:b"&ROW($27:$34)),$G27),$C$27:$C$34),2,0),"")

这个就是前面的函数多层嵌套，可以实现自动查找。

好了这期内容就分享完了，相信日常工作中都能用到，其中一些较难的也有其它方式可以实现，可以关注我，了解更多数据处理的方法。