被VLOOKUP折磨过吗?XLOOKUP值不值得换,一次说清
干过财务或者数据整理的人,十个里有九个被VLOOKUP折磨过。公式写对了结果不对,查不到就满屏#N/A,稍微动一下表格列位置整张表全废。微软2019年出了个新函数XLOOKUP,号称VLOOKUP的升级版,但到底强在哪、什么时候该换、什么时候没必要换,很多人没搞清楚。
VLOOKUP的三个硬伤
只能从左往右查是VLOOKUP最反人类的设计。查找值必须放在选中区域的第一列,想查左边的数据?对不起,做不到。现实工作中的表格哪个不是乱七八糟的顺序?姓名在第三列、部门在第一列,这种布局太常见了,很多人只能先把列挪过来再用公式,繁琐又容易出错。1
列序号写死是另一个大坑。写好公式后如果领导说"D列没用删了",整个表格直接#REF!报错。数据表有几十列时,每次都要从左往右数第几列,数错一个数字整张表全废。有人形容这叫"埋雷",迟早要炸。2
XLOOKUP强在哪
查找方向完全自由是XLOOKUP最核心的升级。想查"姓名"对应的"工号",姓名在B列、工号在A列,直接写=XLOOKUP("张三",B:B,A:A)就行,不用调换任何列的顺序。查找列和返回列完全独立分开选,谁在左谁在右根本不重要。3
内置容错参数让公式更清爽。查不到时VLOOKUP返回#N/A报错,必须外套IFERROR才能显示"未找到";XLOOKUP直接在第四参数写上查不到显示什么,一行搞定。语法也从四个参数精简到三个必填参数,精确匹配是默认行为,不用记FALSE或者0。4
| 对比维度 | VLOOKUP | XLOOKUP |
|---|---|---|
| 查找方向 | 只能从左往右 | 任意方向 |
| 列序号 | 数字索引(易错) | 直接引用列(稳定) |
| 插入/删除列 | 公式易崩 | 自动适应 |
| 返回多列 | 需写多个公式 | 一次返回整行 |
| 错误处理 | 需嵌套IFERROR | 内置参数 |
| 默认匹配模式 | 近似匹配(需指定) | 精确匹配(默认) |
XLOOKUP还支持一次返回多列数据。比如根据订单号查客户名、金额、日期,VLOOKUP要写三个公式各改列号,XLOOKUP一个公式选B:D三列,结果直接溢出到右边三个单元格。另外通过第六参数设置搜索方向,还能从后往前找,找出符合条件的最后一条记录,VLOOKUP完全做不到这点。5
什么时候继续用VLOOKUP
版本限制是第一道坎。XLOOKUP只在Excel 365、Excel 2021以及新版WPS中可用,Excel 2019及更早版本根本没有这个函数。如果需要跟使用旧版Excel的同事共享文件,VLOOKUP仍然是更安全的选择,否则对方打开文件看到的全是#NAME?报错。6
公司模板、客户模板如果已经大量使用VLOOKUP,为了保持兼容性没必要全部替换。新手学习时从VLOOKUP入手也没问题,语法相对简单、资料更多,能满足大多数基础查找需求。简单说就是:能用VLOOKUP解决的就别折腾了,遇到真正棘手的场景再考虑换工具。5
对于多条件查找,XLOOKUP的优势也很明显。用&符号直接连接多个条件,不用像VLOOKUP那样搭辅助列或者写复杂的数组公式。但要注意,XLOOKUP返回多列数据依赖"动态数组"功能,低版本Excel不支持。2
当前文章无评论,是时候发表评论了