Excel自动匹配数据，主要用VLOOKUP、XLOOKUP、INDEX加MATCH、FILTER、Power Query。若数据在一张或两张小表里，用查找函数即可；若需要多表整理、一对多返回、定期更新，用Power Query更稳。这样能根据订单号、学号、员工编号等共同字段，把来源表的信息带到目标表，适合考勤、销售、库存、人员台账。
为什么能自动匹配
Excel自动匹配依靠共同字段，例如订单号、学号、员工编号。函数或查询会用目标表里的字段值，去来源表里找对应行，再把要带出的列返回到当前行。字段值格式整齐，例如文本、数字、日期统一，结果会更整齐。
先准备数据
- 1、给每张表的第一行设置清晰标题。
- 2、保留共同字段，例如“订单号”“学号”“员工编号”。
- 3、把来源表和目标表放在同一工作簿或明确名称区域。
- 4、规范格式，文本统一、日期统一、编号保持相同类型。
方法选择清单
| 方法 | 适合场景 | 示例公式 | 要点 |
|---|---|---|---|
| VLOOKUP | 左右结构、单表返回一列 | =VLOOKUP(A2,来源!A:D,2,0) | 第2列返回，0精确匹配 |
| XLOOKUP | 新版Excel、左右都可用 | =XLOOKUP(A2,来源!A:A,来源!B:B) | 可指定返回文字 |
| INDEX加MATCH | 旧版Excel、左右都可用 | =INDEX(来源!B:B,MATCH(A2,来源!A:A,0)) | MATCH定位行号，INDEX取数 |
| FILTER | 一对多返回多行 | =FILTER(来源!B:B,来源!A:A=A2) | 适合一个编号对应多条记录 |
| Power Query | 多表合并、定期刷新 | 获取数据，合并查询 | 适合较大规模数据 |
Power Query自动匹配步骤
- 1、选中来源表，点击“数据”-“从表格/区域”。
- 2、选中目标表，点击“数据”-“从表格/区域”。
- 3、点击“合并查询”或“合并查询为”。
- 4、选择两张表的共同字段，例如订单号。
- 5、选择匹配类型，例如单条、全部匹配。全部匹配会返回多行，用扩展。
- 6、勾选需要带出的字段。
- 7、点击“关闭并上载到”，选择现有工作表或新工作表。
函数操作步骤
VLOOKUP
- 1、在目标表新增列第2行输入 =VLOOKUP(A2,来源!A:D,2,0)。
- 2、把A2换成共同字段单元格。
- 3、把来源!A:D换成来源表包含共同字段和目标字段区域，共同字段在第1列。
- 4、把2换成目标字段在区域里的第几列。
- 5、把0保持精确匹配。
- 6、向下填充。
XLOOKUP
- 1、输入 =XLOOKUP(A2,来源!A:A,来源!B:B)。
- 2、第1参数共同字段，第2来源查找区域，第3来源返回区域。
- 3、可指定文字时，可用第4参数填写“结果文字”。
- 4、向下填充。
INDEX加MATCH
- 1、输入 =INDEX(来源!B:B,MATCH(A2,来源!A:A,0))。
- 2、MATCH找行号，INDEX取列值。
- 3、向下填充。
FILTER
- 1、输入 =FILTER(来源!B:B,来源!A:A=A2)。
- 2、适合一个编号对应多条记录。
- 3、若需返回多列，把来源!B:B换成多列区域，例如来源!B:D。
结果确认
- 1、用条件格式给已匹配单元格加颜色。条件可写 =COUNTIF(来源!A:A,A2)>0，然后加绿色。
- 2、用数据验证把目标字段做成下拉选择。
- 3、用透视表按共同字段汇总，检查合计。
- 4、需要定期更新时，保留函数或刷新Power Query查询。
选择建议
- • 一张小表：用VLOOKUP。
- • 左右表：用XLOOKUP或INDEX加MATCH。
- • 多条对应：用FILTER。
- • 多表、定期刷新：用Power Query。
- • 只要合计：用数据透视表或SUMIFS。
免责声明：本文为通用Excel操作说明，请根据实际业务字段与团队规范选择合适方法，并保持数据格式整洁。