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,旧版本不支持动态数组相关匹配函数,公式仅用于表格数据查询学习参考,正式业务使用前建议测试重复值空格数据类型等边界情况。