Excel查找匹配函数(VLOOKUP)：功能与应用解析
文章目录
- 概述
- VLOOKUP函数
- 语法
- 查询并返回单列结果
- 查找并返回多列结果
- MATCH函数
- VLOOKUP+MATCH
- 从右向左逆向查找：
- INDEX函数
- INDEX+MATCH
- 函数匹配方式
- 查找匹配注意事项
- 函数名称错误: #NAME?
- 值错误：#VALUE!
- 引用错误：#REF!
- 找不到数据：#N/A
- 报错排障：
- 数据格式不规范如何运用
概述
Excel中的查找匹配函数可以应用于各种数据处理和分析的场景，提供了强大的功能和便利的工具，能够帮助我们更高效地处理和分析数据。
Excel中的查找匹配函数在数据处理和分析中起着至关重要的作用，常见的应用场景包括：
- 数据检索： 帮助用户快速在大量数据中查找特定值或条件，节省时间和减少人工错误。
- 数据合并： 将不同数据表中的相关信息合并到一起，实现数据整合和分析。
- 数据过滤： 根据特定条件过滤数据，提取需要的信息，进行进一步分析和报告。
- 数据校准： 检查数据的一致性，发现数据之间的不匹配或错误，有助于数据校准处理。
- 动态报表： 创建动态报表，根据输入的条件自动更新报表内容，方便用户进行数据查看和分析。
- 数据验证： 进行数据验证与监控，帮助用户确保数据的准确性和完整性。
VLOOKUP函数
语法
VLOOKUP是数据清洗/数据分析中出镜率最高的函数，没有之一。
VLOOKUP 函数用于在垂直方向上查找某个值，并返回该值所在行指定列的数值。其基本语法如下：
- lookup_value ： 是要查找的值。
- table_array： 是包含要搜索的数据的范围，第一列包含要匹配的值。
- col_index_num： 是要返回值所在列数（从 table_array 的第一列起算）。
- range_lookup： 是一个逻辑值，指定是否进行近似匹配（TRUE 或省略 或 1）或精确匹配（FALSE 或 0）
查询并返回单列结果
在H2单元格输入以下公式就能自动得到结果。
=VLOOKUP(G2,A:E,2,0)
公式含义为:用G2单元格的值(FL005)，去查找范围(A:E)中匹配第一列(A列)中的数据，找到一模一样的数据(工号)之后，返回查找区域内第2列中同一行的数据，也就是姓名。
如果要返回的是性别呢?把返回列改成3
如果要返回的是部门呢?把返回列改成4
…
依次类推。
将公式向下填充后，只要G列中有工号，并且这个工号在A列到E列中存在，就能找到它对应的姓名，修改查找的对象，结果也会自动更新。例如，将G2修改成“1010”，姓名自动变成“徐静”
报错：#N/A
H8单元格：“1011”的工号在A列并不存在，返回错误值
H9单元格：G9单元格 未填写工号，返回错误值
上面的示例，公式生成的数据和查找匹配的数据在同一个工作表中。而实际工作中，这两类数据通常分居两张不同的表。区别：在选择查找匹配的范围时，要去另外一张工作表中选择而已。
=VLOOKUP(H2,基础信息表!A:E,2,0)
查找并返回多列结果
有时候，我们可能需要查找一个对象，返回多列匹配的结果。例如，查找下表工号(G2)，返回姓名、性别、部门、奖金（H2:K2）。
①可以H2:K2的每个单元格里分别输入公式，从而自动算出匹配的姓名、部门和奖金。
=VLOOKUP(G2,A:E,2,0)
=VLOOKUP(G2,A:E,3,0)
=VLOOKUP(G2,A:E,4,0)
=VLOOKUP(G2,A:E,5,0)
MATCH函数
上述方法还是会增加手动工作量，下面介绍一个简单的方法：
首先来学习一个函数：MATCH函数。
MATCH 函数用于在数组或范围中查找某个值，并返回其在数组中的位置。其基本语法如下：
- lookup_value： 是要查找的值。
- lookup_array： 是包含要搜索的数据的范围。
- match_type： 是一个数值，指定要进行的匹配类型（1 代表大于、-1 代表小于、0 代表精确匹配）。
在H2单元格输入以下公式，向右填充公式：
=MATCH(H1,$A$1:$E$1,0)
“姓名”在A1:E1范围内的第2个，故返回值为2；
“性别”在A1:E1范围内的第3个，故返回值为3；
…
VLOOKUP+MATCH
用 MATCH 数自动计算得到的动态结果第【N】列，就可以作为VLOOKUP函数中的第3个参数，从而实现查找一个对象，返回多个结果的一对多查询。
在 H2单元格中输入如下公式后，向右填充公式，就自动算出姓名、性别、部门、奖金。
=VLOOKUP($G2,$A:$E,MATCH(H1,$A$1:$E$1,0),0)
从右向左逆向查找：
使用 VLOOKUP查找匹配有一个必备的前提条件，那就是查找对象必须在匹配范围内的首列，否则无法找到结果。
那想在H2单元格查找返回相应工号的的姓名应该怎么操作呢？
INDEX函数
INDEX 函数返回范围或数组中的某个位置的值。结合 MATCH 函数可以实现更灵活的查找匹配操作。
公式含义为:在给定的区域内，返回第几行第几列的值。例如，“INDEX(A1:E11,11,3)”，意思是返回A1:E11区域内第11行第3列的值，也就是C11单元格中的值，详细图示如下所示。
INDEX+MATCH
INDEX和 MATCH 函数刚好能够互补，用MATCH查找匹配可以得到位置信息作为行/列序。INDEX函数就可以将其纳为数，返回区域中交叉点的值。详细公式和计算结果如下。
=INDEX(A:A,MATCH(G2,B:B,0))
公式含义:先用MATCH函数查找工号1010，返回该工号在B列中的位置(第11行)，MATCH所得的位置为11，然后INDEX数将MATCH所得的结果作为【行序】参数，返回A列中第11行的值(徐静)。由于INDEX区域参数只有一列，列序省略不填。
函数匹配方式
在下表的单元格中输入相同的 VLOOKUP函数公式，分别采用两种匹配模式。将公式向下填充后，查找匹配的结果有所差异。
这一招常用来按区间、等级查找和匹配。
注意:
用 VLOOKUP 模糊匹配模式，必须先将匹配范围内的数据，按照从小到大升序排序。
查找匹配注意事项
函数名称错误: #NAME?
出现该错误，通常是函数名称中的字符错了、漏了、多了、顺序弄错了，或者函数参数中多了不该有的标点符号。
值错误：#VALUE!
VLOOKUP中缺少返回值的参数时，就会出现该错误。
引用错误：#REF!
当函数中所引用的位置不存在时，会导致该错误。使用VLOOKUP函数，容易出现以下两种情况。
找不到数据：#N/A
虽然完成公式后，显示错误值#N/A，不一定是公式本身出错。此错误值表示在匹配范围内找不到和查找对象匹配的数据，很可能是正常情况。还可以利用这一特性，核对A表数据是否存在于B表中。
如果在目标表格中有该数据，却还是匹配不出来时，就要检查以下3种情况:
- 查找对象在查找范围内是否处于第1列(必须是，否则就会出错);
- 匹配范围有没有包含完整的数据区域(必须包含，否则找不到);
- 数据是否规范、一致(看编辑栏中的真实内容，必须一模一样)
报错排障：
当VLOOKUP函数出错时，可以通过以下4步进行排障:
- 检查函数拼写和符号是否完全正确;
- 检查每一个参数是否按要求填写;
- 检查引用区域是否包含查找对象(特别是批量填充公式时，引用范围是否需要锁定);
- 数据源是否规范一致。
数据格式不规范如何运用
①查找的值为数值型，匹配值是文本型；
需将查找值拼接空白字符串变成文本型即可。
=VLOOKUP(G3&“”,A:B,2,0)
①查找的值为文本型，匹配值是数值型；
需将查找值*1变成数值型即可。
③混合型
查找的值有数值型也有数值型，匹配值有数值型也有数值型；
=IF(ISNA(VLOOKUP(G3*1,A:B,2,0)),VLOOKUP(G3&"",A:B,2,0),VLOOKUP(G3*1,A:B,2,0))
- IF(ISNA(VLOOKUP(G31,A:B,2,0)),：首先，它使用VLOOKUP函数尝试以G31（将G3的内容乘以1来转换为数值类型）作为查找值在A列中查找。如果VLOOKUP返回#N/A错误，说明没有找到精确匹配。
- VLOOKUP(G3&“”,A:B,2,0)：在这种情况下，它使用VLOOKUP函数以G3的内容作为字符串查找值在A列中查找。通过在G3后附加一个空字符串来确保查找值是字符串类型。
- VLOOKUP(G31,A:B,2,0))：最后，如果第一个VLOOKUP函数没有返回#N/A错误，它直接再次使用VLOOKUP函数以G31（数值类型）作为查找值在A列中查找。
