文|仟樱雪
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之美，难相忘，感谢老大的启蒙！不足之处，望见谅，后续更新）
