Excel 匹配功能最常用就是 VLOOKUP 纵向匹配函数,另外还有 INDEX 加 MATCH 双向匹配,新版 365 新增 XLOOKUP 作为升级匹配函数,三种都是表格跨表匹配数据的核心工具,VLOOKUP 适合单条件纵向查找,INDEX MATCH 适合双向查找多条件查找,XLOOKUP 可以替代 VLOOKUP,不用考虑列序号,容错能力更强,很多新手使用匹配功能最容易出错,核心原因是没有理解匹配类型,精确匹配和近似匹配的区别,精确匹配是日常台账核对取值必须使用的模式,近似匹配只适合区间查找,同时 VLOOKUP 本身存在先天限制,查找值必须在选定区域第一列,这个限制也是大家后期转向 INDEX MATCH 或者 XLOOKUP 的主要原因,掌握匹配功能之后,可以实现跨工作表跨工作簿调取数据,比如根据工号从总表匹配姓名部门薪资,是办公表格最高频的操作之一。
VLOOKUP 纵向匹配基础用法
适合单条件精确匹配,是入门首选匹配功能
公式
=VLOOKUP (A2,Sheet2!A:D,3,0)
参数拆解
A2 查找值,也就是用来匹配的关键字,比如工号
Sheet2!A:D 查找的数据区域,跨表匹配就加上工作表名称加感叹号
3 代表返回查找区域里面第 3 列的数据
0 代表精确匹配,这个参数一定要写 0,省略容易变成近似匹配出错
操作步骤
1 点击需要输出匹配结果的单元格
2 在编辑栏输入完整 VLOOKUP 匹配公式,按下回车
3 鼠标放到单元格右下角,出现十字填充柄,下拉批量填充整列
重要要点 查找关键字必须放在所选查找区域的第一列,如果关键字在区域右边,VLOOKUP 就无法正常匹配,这是 VLOOKUP 最大限制
XLOOKUP 新版匹配函数
微软 365 和新版 WPS 支持,属于 VLOOKUP 升级款匹配功能
公式
=XLOOKUP (A2,Sheet2!A:A,Sheet2!C:C,"无匹配")
参数拆解
A2 查找值
Sheet2!A:A 查找列
Sheet2!C:C 要返回结果的列
无匹配 找不到的时候自定义显示文字,替代 N/A 报错
优势说明
不需要选定整片区域,不用数返回第几列,不受查找列必须在最左侧限制,支持横向纵向查找,是现在优先推荐的匹配功能
INDEX 加 MATCH 组合匹配
老版本 Excel 没有 XLOOKUP,就使用这个经典组合匹配功能,支持双向查找
公式
=INDEX (Sheet2!C:C,MATCH (A2,Sheet2!A:A,0))
函数拆解
MATCH 负责根据关键字,在查找列里面定位到所在行号
INDEX 根据 MATCH 返回的行号,取出目标列对应的数据
操作步骤
1 在结果单元格输入组合公式回车
2 下拉填充批量匹配
拓展 可以做成双向匹配,同时匹配行和列,适合二维查询表,行是工号,列是项目,交叉位置取值
多条件匹配功能
单条件匹配不够用的时候,需要同时满足两个条件,比如同时匹配部门加姓名
XLOOKUP 多条件写法
=XLOOKUP (1,(Sheet2!A:A=A2)(Sheet2!B:B=B2),Sheet2!C:C,"无匹配")
原理 两个条件同时成立相乘结果等于 1,以此完成双条件匹配
老版本可以使用 SUMPRODUCT 组合实现多条件匹配,适合没有动态数组的旧版 Excel
横向匹配 HLOOKUP
和 VLOOKUP 对应,VLOOKUP 是纵向按行查找,HLOOKUP 是横向按列查找,适合首行作为关键字的横向数据表
公式
=HLOOKUP (A2,A1:D10,3,0)
参数含义
A2 查找值
A1:D10 查找区域
3 返回区域里面第 3 行数据
0 精确匹配
日常使用频率低于 VLOOKUP,只有横向排布的表格才会用到这个匹配功能
函数对照说明
| 匹配函数 | 查找方向 | 版本要求 | 核心特点 |
|---|---|---|---|
| VLOOKUP | 纵向 | 全部版本 | 简单易学,查找列必须在最左侧 |
| HLOOKUP | 横向 | 全部版本 | 横向表格专用 |
| INDEX MATCH | 双向 | 全部版本 | 灵活,无 VLOOKUP 列位置限制 |
| XLOOKUP | 纵向横向 | 365 新版 | 功能最强,支持自定义报错 |
高频踩坑要点
第一 精确匹配最后一个参数必须写 0,写成 1 近似匹配会出现错误取值,是匹配功能最常见错误
第二 查找关键字存在前后空格,肉眼看不见,会导致匹配失败,需要用 TRIM 函数清除空格再匹配
第三 跨表匹配的时候工作表名称带空格,名称需要加上单引号,否则公式报错
第四 数据类型不一致,一边是文本数字,另一边是数值,就算数字一样,匹配功能也会判定不匹配
第五 下拉填充查找区域没有锁定绝对引用,区域偏移,后续行匹配结果错乱
【常见问题】
问题 1 excel 匹配功能 VLOOKUP 返回 N/A 是什么原因
回答 1 excel 匹配功能 VLOOKUP 出现 N/A,代表没有找到匹配关键字,常见原因是关键字存在隐藏空格,两边数据类型不一致,或者查找区域选错,使用 TRIM 清理空格之后再测试 excel 匹配功能。
问题 2 excel 匹配功能 VLOOKUP 能不能从右往左匹配
回答 2 excel 匹配功能 VLOOKUP 原生不能从右往左匹配,因为查找关键字必须在区域第一列,想要实现从右往左匹配功能,改用 INDEX MATCH 组合公式或者新版 XLOOKUP。
问题 3 excel 匹配功能多条件匹配怎么设置
回答 3 excel 匹配功能需要多条件匹配,可以使用 XLOOKUP 搭配相乘条件写法,同时满足多个筛选条件,旧版 Excel 可以使用 SUMPRODUCT 实现多条件 excel 匹配功能。
问题 4 excel 匹配功能 VLOOKUP 和 XLOOKUP 该选哪个
回答 4 excel 匹配功能如果是新版 365,优先选择 XLOOKUP,不需要数列序号,支持自定义无匹配提示,老版本 Excel 只能继续使用 VLOOKUP 或者 INDEX MATCH 匹配功能。
【免责申明】
本文内容基于微软 Excel 通用函数编写,新版 WPS 表格兼容 XLOOKUP,旧版本不支持动态数组相关匹配函数,公式仅用于表格数据查询学习参考,正式业务使用前建议测试重复值空格数据类型等边界情况。
