Excel 常用函数:VLOOKUP 与 XLOOKUP 实战
在 Excel 里「根据一个值查另一个值」是最常见的需求:按工号查姓名、按产品 ID 查价格、按日期查销量。VLOOKUP 和 XLOOKUP 是两大主力函数,本文讲清它们的用法、区别和坑。
VLOOKUP 基础语法
=VLOOKUP(查找值, 查找区域, 返回列号, 精确匹配)
举个例子:有一张产品表,A 列是产品 ID、B 列是名称、C 列是单价。要按 ID 查名称:
=VLOOKUP(F2, A:C, 2, FALSE)
含义:拿 F2 的值,在 A 到 C 列里找,找到后返回第 2 列(名称列)的值,FALSE 表示精确匹配。最后一个参数务必写 FALSE(或 0),写 TRUE 是近似匹配,容易查到错误结果。
VLOOKUP 的三大局限
- 只能向右查找:返回列必须在查找列右侧。想「按名称反查 ID」(向左查)做不到,得调整列顺序。
- 列号用数字易错:返回第 3 列写 3,一旦中间插入或删除一列,数字就错位,公式静默返回错误值,很难发现。
- 找不到返回 #N/A:不处理就满表红字,要用
IFERROR包一层。
XLOOKUP:更现代的选择
=XLOOKUP(查找值, 查找列, 返回列, 找不到时的返回值)
同样查产品名称:
=XLOOKUP(F2, A:A, B:B, "无此产品")
XLOOKUP 解决了 VLOOKUP 的所有痛点:查找列和返回列分开写,向左向右都行;列引用用整列字母,增删列不影响;找不到时直接返回你指定的值,不用再套 IFERROR。
进阶:多条件与通配符
XLOOKUP 还能用 & 拼接多列做组合查找,或配合通配符 *、? 做模糊匹配。VLOOKUP 要实现这些得嵌套数组公式,复杂得多。
方法对比一览
| 维度 | VLOOKUP | XLOOKUP |
|---|---|---|
| 查找方向 | 仅向右 | 左右双向 |
| 列引用 | 数字(易错位) | 整列(稳定) |
| 找不到 | #N/A | 可自定义 |
| 多条件 | 难 | 简单拼接 |
| 版本要求 | 全版本 | Office 365 / 2021+ |
常见问题
- VLOOKUP 查不到但数据明明在:多半是查找列有隐藏空格,用
TRIM清洗;或数据类型不一致(文本数字 vs 数值)。 - 公式报 #REF:返回列号超过了区域列数,检查列号。
- XLOOKUP 不可用:旧版 Excel 没有,降级用 INDEX + MATCH。
实战建议
新表优先用 XLOOKUP;维护老表或要兼容旧版才用 VLOOKUP。两者都建议套 IFERROR 做兜底:=IFERROR(VLOOKUP(...), "")。
工具承接
查完的数据要给程序用?把 Excel 表转 JSON 最方便,在线工具一键导出,浏览器本地处理不泄露数据。