文|仟樱雪

Excel函数千千万，但大众情人，独他无二。

VLOOKUP函数，传说中的神查找&匹对，高效掌握它，分分钟，帮你搞定那个她！

---

01

当之无愧的中央空调——VLOOKUP函数，在企业中，不仅财务、采购、商品、物流、运营、生产这些部门会出现它的身影，而且客服、IT、研发、行政、人资、后勤等等这些部门，它都游刃有余。

熟练掌握VLOOKUP函数，是大部分数据处理人的初始必经之路，但是无数的表哥、表姐在利用VLOOUP函数，进行初级入门的查找、匹配时，遇到各种报错，便累觉不爱了。

枚举各种报错处理，见招拆招的铲除VLOOUP函数查找路上的妖魔鬼怪，揭开数据分析的唯美面纱，是每一个表哥、表姐的必备法宝。

---

02

众所周知，VLOOKUP函数是一个查找、匹配函数，给定一个查找的目标，它就能从指定的查找范围中查找返回想要查找到的值。它的基本“家庭结构”为：

查找结果=VLOOKUP（找谁，查找范围，在查找范围里第几列，精确查找OR模糊查找)

例如：根据右侧的“产品编码”，查找产品所属于的“品类”，

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

第一个参数：找谁？找M列的“产品编码”；

第二个参数：在哪儿找，在左侧数据源表的E列到G列，E列是要找的“谁”（产品编码）所在的列，G列是“谁”所对应的目标（品类）所在的列,即E:G;

第三个参数：在左侧数据源区域中，从要找的“谁”（产品编码）所在E列，到目标列（品类）所在G列的列数字，本例中是3;

第四个参数：查找的方式，0或者FALSE代表精确查找(查找结果是数据源表格中第一条满足条件的记录的相应值),1或者TRUE代表模糊查找（查找最接近但比它小的那个值）

---

03

掌握了VLOOKUP的前世今生以及家庭结构，接下来开始斩妖除魔，进行常见报错情况和处理枚举。

（一）VLOOKUP函数初级报错——结构性错误枚举

1.查找范围不包含返回目标值列

错误公式=VLOOKUP($M2,$E$1:$F$20,3,0)

说明：该错误是查找范围，根本不存在产品“品类”所在列，应该将G列纳入查找范围,即将查找范围E:F,更改为E:G。

处理方法：查找范围可以很大，但是必须包含查找目标列。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

1.查找范围开始列位置，不是“找谁”所在列

错误公式=VLOOKUP($M2,$F$1:$G$20,3,0)

说明：VLOOKUP函数的查找范围，开始列必须是要查找的“谁”（数据源中“产品编码”所在列），该错误是未从开始列，计量查找范围，将F:G,更改为E:G。

处理方法：查找范围可以很大，但必须从“找谁”所在列，开始计量查找范围。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)。

1.第三参数——查找参数，数字输入错误

a.偶然因素，人为手动输入错误

错误公式=VLOOKUP($M2,$E$1:$G$20,1,0)

说明：该错误是第三参数——查找参数错误，在数据源表中从“产品编码”E列开始数，3列，到“品类”所在G列止，而不是1列，数字1，是“产品编码”所在列，应该将1，更改为3。

1.第三参数——查找参数，数字输入错误

b.偶然因素，人为一叶障目，视觉错误

错误公式=VLOOKUP($M2,$E$1:$G$20,2,0)

说明：该错误是第三参数——查找参数错误，在数据源表中“产品编码”所在E列和“品类”所在G列中间隐藏了1列，导致人为视觉计量，参数为2，导致错误。

处理方法：查找范围可以很大，但必须从“找谁”所在列开始计量查找范围，释放隐藏列，避免视觉误差。

正确公式=VLOOKUP($M2,$E$1:$G$20,3,0)

注意：数据源中列数据，有时会被隐藏很多列，在计数从开始列到目标终止列时，一定要注意。

1.漏掉第四位参数或输入为1

错误公式=VLOOKUP($M3,$E$1:$G$20,3,1)

说明：该错误是第四参数，VLOOKUP函数如果缺少第四个参数，默认为模糊查找(1或者TRUE)，则进行数字的区间查找，继续使用1或者TRUE,我们就无法精确查找到结果，出现报错，将其更改为0或者FALSE。

注意：当第四位参数为1或者TRUE时，默认为模糊查找。

1.第四位参数,模糊匹配时，报错

例如：根据右侧产品的单价，查找在左侧的产品的销售单价的单价等级

错误公式=VLOOKUP($w2,$S$2:$T$7,2,0)

说明：该错误本案例是第四参数设置错误，模糊查找(1或者TRUE)，是进行数字的区间查找的最佳选择，继续使用0或者FALSE,我们就无法精确查找到结果，出现报错。

处理办法：右侧公式中在查找“销售单价”为15.25时，返回“单价等级”所对应的级别，原因是在左侧的销售单价区域中0和20中，VLOOKUP函数只选比查找值15.25小的那一个对应的级别，所以公式会返回0所对应的级别F，将其0改为1或者TRUE。

正确公式=VLOOKUP($w2,$S$2:$T$7,2,1)

1.第四位参数,模糊匹配时，匹配错误

公式=VLOOKUP($w2,$S$2:$T$7,2,1)，公式正确时，匹配结果错误

例如：以下案例中的价位区间从0开始到99元截止

说明：VLOOKUP使用模糊查找时，数据源区域的数据必须为升序列（从小到大）,本例子中销售单价为65和45的顺序上下反了，导致VLOOKUP函数，一脸懵逼不知如何查找，从而查找出模糊的错误数据。

处理方法：将数据源区域的顺序按照从小到大更正即可。

1.VLOOKUP函数不支持按区域或者数组查找

例如：根据右侧“平台”和“产品编码”，在左侧日平台销售数据，查找“产品名称”

错误公式=VLOOKUP($M$1:$N$7,$B$1:$J$20,9,0)

说明：VLOOKUP函数查找区域不支持按照数组或者区域进行查找，本例中查找“平台”和“产品编码”的“产品名称”，因同一个编码在不同的平台是不同的产品，属于需组合处理或者进行结构转化后的匹配

处理方法：将“平台”和“产品编码”进行组合

正确公式=VLOOKUP($N2,$A$1:$K$20,11,0)

1.VLOOKUP函数不支持逆序查找

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)

说明：vlookup函数不支持反向查找。当查找区域中，查找列和目标列的顺序相反时，本例中左侧数据源区域“产品编码"是查找列，“品类”是目标列，但是“品类”在“产品编码”的前一列。此时从“品类”所在列开始输入查找区域，是逆序查找，VLOOUP函数并不支持此类查找，则自动报错

处理方法：将数据源区域的“品类”所在列，“飘移”到“产品编码”的右侧，再进行从左到右的数据区域选择 ，如下所示：

正确公式=VLOOKUP($O2,$F$1:$M$20,8,0)

（一）VLOOKUP函数初级报错——内容性错误枚举

1.查找值与查找范围开始列，数据格式不一致

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)

例如：根据右侧“产品编码”，在左侧查找“产品编码”对应的“产品名称”，

说明：VLOOKUP函数还是个很“别扭”的函数，如果查找值与查找范围开始列的数据类型不匹配，则会报错，本例左侧查找范围中“项目编号”是文本型数据（单元格左上方的绿色三角，表示文本格式数据），而右侧查找值“产品编码”是数值型（无绿三角），两者数据类型不匹配，则报错。

处理方法：统一数据类型格式，统一使用文本型格式或统一使用数值型数据,将右侧的“找谁”的数值型格式，添加一个空格格式转换成文本型格式进行查找。

正确公式=VLOOKUP($O2&"",$E$1:$F$20,2,0)

1.查找范围开始列与查找值，数据格式不一致

错误公式=VLOOKUP($O2,$E$1:$F$20,2,0)

说明：同样是数据格式不统一问题，如果查找值与查找范围开始列的数据类型不匹配，则会报错，本例左侧查找范围中“项目编号”是数值型数据，而右侧查找值“产品编码”是文本型（带绿三角），两者数据类型不匹配，则报错。

处理方法：统一数据类型格式，统一使用文本型格式或统一使用数值型数据,将右侧的“找谁”的文本型格式，“*1”转换成数值型格式进行查找。

正确公式=VLOOKUP($O2*1,$E$1:$F$20,2,0)

注意：不到万不得已，尽量想办法不要更改数据源。

1.查找区域中含有“隐形”字符（例如空格）

公式正确，但是出现查找报错：

说明：常见文本型数据，在录入系统时人为误差导致“隐形空格”存在或者数据源从网页或数据库中取出来是带有格式的，观察看不出来带有空格，本例中右侧数据，“产品编码”前带有空格，左右数据格式不统一导致报错但没有空格格式的，正常查找匹配数据。

处理方法：剔除右侧数据的文本空格，在右侧“产品编码”列加入几列空列，然后对A列进行分列操作(数据-分列)，即可把不可见字符分离出去，然后整合数据后进行匹配。

正确公式=VLOOKUP($O3,$E$1:$F$20,2,0)

1.查找区域中含有不规则“隐形”字符（例如空格）

说明：查找列含有不规则的空格，有时1个或者2个，甚至多个空格，这些就是类空格的不可见字符。

处理方法：直接在单元格中复制不可见字符粘贴到替换窗口，替换掉即可。

1.数据源中出现重复记录，数据查找错误

错误公式=VLOOKUP($P5,$E$1:$H$20,4,0)

例如：根据右侧“平台”、“产品编码”数据查找其对应的“收入”；

说明：本例中原始数据中同一个“产品编码”属于不同的“平台”，但是“收入”是不同的，则直接根据“产品编码”查找数据源中的“收入”，VLOOKUP会傻掉，不知道匹配哪个收入数据，VLOOKUP一般默认从上到下的查找顺序，遇到的第一个“产品编码”的收入则进行查找结果呈现,因此出现数据内容的错误。

处理方法：辅助列处理，将“产品编码”和“平台”进行组合匹配，但是数据源中也需要进行组合，进行统一查找。

正确公式=VLOOKUP($O5,$A$1:$I$20,9,0)

注意：此类错误，一般不会轻易被发现，但是会导致总金额或者汇总数据差距甚远

1.查找范围不固定，导致部分查找匹配

错误公式=VLOOKUP($N6,E5:H24,4,0)

说明：本例中根据“产品编码”查找对应“收入”，但是查找区域没固定，其中编码为“64234”的产品在左侧数据源中是第2行，但是查找区域是“E5：H24”属于“相对引用”，第2行根本不在查找范围内，因此出现报错。

处理方法：将区域进行行列绝对引用，锁定不变动 。

正确公式=VLOOKUP($N6,$E$1:$H$20,4,0)

1.文本简称模糊匹配报错

错误公式=VLOOKUP($N2,$F$1:$I$20,4,0)

例如：根据右侧的“产品名称”简称，查找对应的销售数量；

说明：右侧“产品名称”是数据源中的产品名称的简称，根据简称进行精确查找匹配，无法一一匹配，故此报错。

处理方法：利用通配符“*”进行精确匹配，查找带有简称关键字词的数据，例如查找带有“李”姓的同学，等。

正确公式=VLOOKUP($N2&"*",$F$1:$I$20,4,0)

1.通配符使用错误导致报错

错误公式=VLOOKUP($X3,$R$2:$S$6,2,0)

例如：根据右侧的单价区间，在左侧的单价区间对应的“单价等级”，查找对应的单价等级；

说明：“~”符号在文本中时，在VLOOKUP函数中，会被默认特殊函数条件，不是文本“~”。

处理方法：用“~~”就可以表示查找文本的“~”了。

正确公式=VLOOKUP(SUBSTITUTE($X5,"~","~~"),$R$2:$S$6,2,0)

---

03

VLOOKUP函数查找小妙招

1.VLOOKUP+IF组合逆序查找匹配

公式=VLOOKUP($N2,IF({1,0},$F$1:$F$20,$E$1:$E$20),2,0)

说明：根据右侧“产品编码”，查找其对应的“品类”，但是在左侧的数据源中，“品类”所在列E列，却在“产品编码”列的前面列，逆序而处之，故此需要进行逆序查找匹配；

（1）公式1=IF({1,0},$F$1:$F$20,$E$1:$E$20)

是将左侧的数据源区域进行了逆序的正向扭转；

（1）公式2=VLOOKUP($N2,IF({1,0},$F$1:$F$20,$E$1:$E$20),2,0)是组合函数，扭转顺序后的查找匹配。

1.VLOOKUP+CHOOSE组合逆序查找匹配

公式=VLOOKUP($P2,CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20),2,0)

说明：根据右侧“产品编码”，查找其对应的“品类”，但是在左侧的数据源中，“品类”所在列E列，却在“产品编码”列的前面列，逆序而处之，故此需要进行逆序查找匹配；

(1)公式1=CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20)

是将左侧的数据源区域进行了逆序的正向扭转；

（2）公式2=VLOOKUP($P2,CHOOSE({1,2},$F$1:$F$20,$E$1:$E$20),2,0)

是组合函数，扭转顺序后的查找匹配。

1.COLUMN()/ROW()函数批量查找

COLUMN(),用于同一行数据自动产生列序列号；

ROW(),用于同一列数据自动产生序行号。

公式=VLOOKUP($P2,$E$1:$G$20,COLUMN(C1),0)

等价于=VLOOKUP($P2,$E$1:$G$20,3,0)

左侧的数据源中，因此利用column（）函数自动计数“品类”和“收入所在列”，其中column（C1）=3，返回“品类”所在列的内容，因此只需将公式右侧填充即可，自动填充“收入”等内容的匹配查找；

后话：VLOOKUP函数查找匹配完毕，并不是万事大吉，一定需返回原数据源筛选查看是否查找匹对正确，进行查错处理，保证查找匹配正确。

---

04

Excel函数千千万，但大众情人，独VLOOUP无二，熟练掌握这20个斩妖除魔利器，前途无可限量，再也不会轻易入坑了。

那些年，我们走过的查找之路，趟过的20个坑，如今坑已平，那个她，分秒可见倾城容颜。

（注：2017.09.20 工作技能积累打卡，记录点滴数据分析成长之路，纵使分析工具换过千千万万，Excel之美，难相忘，感谢老大的启蒙！不足之处，望见谅，后续更新）