Vlookup函数讲解
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
处理查找 错误
vlookup 使用中一 般会产生 以下错 误：
#VALUE! 和 #REF! 是由于 col_inde x_num设 置不当造 成的，虽 然可以使 用 ISERROR 剔除，但 建议不要 这么做， 因为这类 错误需要 纠正。
有时 col_inde x_num参 数使用 column() 函数生 成，当设 置不当造 成的错误 用 ISERROR 剔除时， 会与#N/A 错误混 淆，不利 于公式查 错。 #N/A是常 见的错误 信息，需 要针对不 同情况处 理： 精确匹配 查找时出 现，是由 于未找到 完全匹配 的值。
当确定应
该出现
#N/A（即
查找值不
存在）
时，如果
不要显示
错误，可
以使用条
件格式或
直接在公
式中处理
注意使用
条件格式
处理后单
元格内的
值仍是
表三
#N/A，其
他单元格
引用该单
元格也会
返回#N/A
A
100
错误。
C
200
当C55选B
时，显然
应该返回
错误（数
据区没有
B）。
这个单元
格使用条
件格式处
理，条件
格式公式
使用ISNA 测试 vlookup 函数是否 返回 #N/A，如 vlookup 函数返回 #N/A，则 ISNA函数 返回TRUE 。再使用 IF函数判 断，即可 去除错误 。 精确查找 时，也可 以直接判 断查找值 是否存在 来去除 #N/A错 误，如此 例使用 COUNTIF 测试数据 区是否有 查找值。
＝
isna(D55
)，并设
定条件字
体颜色与
B
#N/A 底色相同
处理错误
处理错误
近似匹配 查找时出 现#N/A错 误
近似匹配 查找时， 除了前述 的格式等 原因，查 找值小于 数据区的 最小值将 返回#N/A 。 对于此类 错误，只 要数据区 设计合理 就可避免 （建立可 能的最小 值）,当 然也可以 使用ISNA 去除。
表一 张三丰 100
对于空格 或其他不 可见字 符，可以 使用len 函数测试 文本长度 来确定， 或使用 code测试 其ASCII 码
李四军 200
B18单元 格长度 4
张三丰
#N/A 100
该错误由 于查询字 符“张三 丰 ”后 含空格， 可能看不 出，但在 精确匹配 查找时造 成不完全 匹配 对于 lookup_v alue中的 空格，将 其中的空 格替换 掉，或使 用TRIM去 除空格后 查找
32
查找内容 格式不 符，也会 造成＃ N/A错误
200
B
可以使用 鼠标选定 几个单元 格，看状 态栏的合 计。有， 则为数 值，无， 则为文本 。
这两个错
误都是由
于格式不
一致造
成，一个
是按数值
查文本，
一个是按Baidu Nhomakorabea
文本查数
100
#N/A 值
对于此类
错误，可
以修改查
找值、数
据区的格
式，使之
统一；也
可以在公
200
数 据 含 空 格 等
李四军
表二
100
A
因此，这 是一个数 组公式， 需要按 Ctrl+Shi ft+Enter 输入 如果数据 区比较 大，数组 公式会很 慢；不如 直接使用 替换将数 据区的空 格替换后 使用普通 公式。
#N/A
该错误由 于查询字 符“李四 军”后含 不可见字 符（非空 格），使 用trim、 clean、 替换空格 都不能去 除。 由网页复 制、其他 程序转出 的表格， 经常会出 现这类字 符，CODE 可以测试 其并非空 格 对于这类 不可见字 符，可以 先复制该 字符，然 后替换该 字符为空 白
该错误是
由于查询
表表四中
“李四军
”后含空
格，在精
确匹配查
找时不完
李四军 #N/A 全匹配
对于
Table_ar
ray中的
空格，也
可以使用
200 trim。
这个公式
中的
Table_ar
ray已不
是直接引
用单元格
区域，而
是引用对
$B$87:$C
数
$88运算
据
trim后的
含
内存数组
空
格
等
B18第四 个字符码
一
元格中的
致
值仍是数
值形式,
需要激活
（双击）
才会真正
转为文本
。
单元格很
多时，采
用逐个激
活的方法
肯定会累
死。可以
采用分列
或选择性
粘贴的方
法：
当某列数 据需要全 部转换 时，采用 分列是个 好办法。 它可以将 文本转为 数值，也 可将数值 转为文本 。
分列位于 菜单栏— —数据— —分列 由文本转 数值也可 以 复制一空 白单元 格，选定 需转换的 数据区， 选择性粘 贴——加
返回
近似匹配 查找时出 现，是由 于查找值 小于数据 区的最小 值。 如果 vlookup 的参数产 生错误， vlooukp 也返回相 同错误， 那不在本 讨论范围 内。
精确匹配 查找时出 现#N/A错 误
首先检查 引用范围 是否正 确，查询 值是否在 数据区的 第一列。
当确定引 用正确、 不应出现 #N/A（即 应该查找 到值） 时，检查 是否存在 空格或其 它字符、 格式是否 一致
#N/A 式中处理
这个公式 将数值 100用&连 接空字符 串""，强 制转为文 A本 这个公式 将文本 200用*1 运算，强 制转为数 B值
要注意的 是采用修 改格式的 方法，仅 仅将单元 格格式更 改还不 行：
数 据 格 式 不 一 致
例如对于
数
包含数值
据
的常规单
格
元格将格
式
式改为文
不
本后，单
vlookup 使用中一 般会产生 以下错 误：
#VALUE! 和 #REF! 是由于 col_inde x_num设 置不当造 成的，虽 然可以使 用 ISERROR 剔除，但 建议不要 这么做， 因为这类 错误需要 纠正。
有时 col_inde x_num参 数使用 column() 函数生 成，当设 置不当造 成的错误 用 ISERROR 剔除时， 会与#N/A 错误混 淆，不利 于公式查 错。 #N/A是常 见的错误 信息，需 要针对不 同情况处 理： 精确匹配 查找时出 现，是由 于未找到 完全匹配 的值。
当确定应
该出现
#N/A（即
查找值不
存在）
时，如果
不要显示
错误，可
以使用条
件格式或
直接在公
式中处理
注意使用
条件格式
处理后单
元格内的
值仍是
表三
#N/A，其
他单元格
引用该单
元格也会
返回#N/A
A
100
错误。
C
200
当C55选B
时，显然
应该返回
错误（数
据区没有
B）。
这个单元
格使用条
件格式处
理，条件
格式公式
使用ISNA 测试 vlookup 函数是否 返回 #N/A，如 vlookup 函数返回 #N/A，则 ISNA函数 返回TRUE 。再使用 IF函数判 断，即可 去除错误 。 精确查找 时，也可 以直接判 断查找值 是否存在 来去除 #N/A错 误，如此 例使用 COUNTIF 测试数据 区是否有 查找值。
＝
isna(D55
)，并设
定条件字
体颜色与
B
#N/A 底色相同
处理错误
处理错误
近似匹配 查找时出 现#N/A错 误
近似匹配 查找时， 除了前述 的格式等 原因，查 找值小于 数据区的 最小值将 返回#N/A 。 对于此类 错误，只 要数据区 设计合理 就可避免 （建立可 能的最小 值）,当 然也可以 使用ISNA 去除。
表一 张三丰 100
对于空格 或其他不 可见字 符，可以 使用len 函数测试 文本长度 来确定， 或使用 code测试 其ASCII 码
李四军 200
B18单元 格长度 4
张三丰
#N/A 100
该错误由 于查询字 符“张三 丰 ”后 含空格， 可能看不 出，但在 精确匹配 查找时造 成不完全 匹配 对于 lookup_v alue中的 空格，将 其中的空 格替换 掉，或使 用TRIM去 除空格后 查找
32
查找内容 格式不 符，也会 造成＃ N/A错误
200
B
可以使用 鼠标选定 几个单元 格，看状 态栏的合 计。有， 则为数 值，无， 则为文本 。
这两个错
误都是由
于格式不
一致造
成，一个
是按数值
查文本，
一个是按Baidu Nhomakorabea
文本查数
100
#N/A 值
对于此类
错误，可
以修改查
找值、数
据区的格
式，使之
统一；也
可以在公
200
数 据 含 空 格 等
李四军
表二
100
A
因此，这 是一个数 组公式， 需要按 Ctrl+Shi ft+Enter 输入 如果数据 区比较 大，数组 公式会很 慢；不如 直接使用 替换将数 据区的空 格替换后 使用普通 公式。
#N/A
该错误由 于查询字 符“李四 军”后含 不可见字 符（非空 格），使 用trim、 clean、 替换空格 都不能去 除。 由网页复 制、其他 程序转出 的表格， 经常会出 现这类字 符，CODE 可以测试 其并非空 格 对于这类 不可见字 符，可以 先复制该 字符，然 后替换该 字符为空 白
该错误是
由于查询
表表四中
“李四军
”后含空
格，在精
确匹配查
找时不完
李四军 #N/A 全匹配
对于
Table_ar
ray中的
空格，也
可以使用
200 trim。
这个公式
中的
Table_ar
ray已不
是直接引
用单元格
区域，而
是引用对
$B$87:$C
数
$88运算
据
trim后的
含
内存数组
空
格
等
B18第四 个字符码
一
元格中的
致
值仍是数
值形式,
需要激活
（双击）
才会真正
转为文本
。
单元格很
多时，采
用逐个激
活的方法
肯定会累
死。可以
采用分列
或选择性
粘贴的方
法：
当某列数 据需要全 部转换 时，采用 分列是个 好办法。 它可以将 文本转为 数值，也 可将数值 转为文本 。
分列位于 菜单栏— —数据— —分列 由文本转 数值也可 以 复制一空 白单元 格，选定 需转换的 数据区， 选择性粘 贴——加
返回
近似匹配 查找时出 现，是由 于查找值 小于数据 区的最小 值。 如果 vlookup 的参数产 生错误， vlooukp 也返回相 同错误， 那不在本 讨论范围 内。
精确匹配 查找时出 现#N/A错 误
首先检查 引用范围 是否正 确，查询 值是否在 数据区的 第一列。
当确定引 用正确、 不应出现 #N/A（即 应该查找 到值） 时，检查 是否存在 空格或其 它字符、 格式是否 一致
#N/A 式中处理
这个公式 将数值 100用&连 接空字符 串""，强 制转为文 A本 这个公式 将文本 200用*1 运算，强 制转为数 B值
要注意的 是采用修 改格式的 方法，仅 仅将单元 格格式更 改还不 行：
数 据 格 式 不 一 致
例如对于
数
包含数值
据
的常规单
格
元格将格
式
式改为文
不
本后，单