ARTICLE · 986813
Excel 查表别再用 VLOOKUP 了,这个官方升级版我后悔没早用,1秒搞定
今天用xlookup+CHOOSECOLS 函数实现动态匹配，先了解步骤，学会了你也可以一秒匹配。（下面有视频，不懂可以跟着操作哦）
01
这是什么函数
一句话：Excel 365 力推的新一代查表函数
XLOOKUP 是Excel 365 标配的查表函数,目的就是替代 VLOOKUP 那一堆别扭的限制(只能往右查、要数列号、近似匹配要升序等)。
这次用它按员工姓名查「部门+工资」两列——一个公式搞定,而且是从 A 列查 A:D 里的任意列,不再受 VLOOKUP 「查找列必须在返回列左边」的限制。
02
公式怎么写
三个必填 + 三个可选,默认精确匹配
=XLOOKUP(lookup_value,lookup_array,return_array, [if_not_found], [match_mode], [search_mode]) |
lookup_value = 要查的值(姓名 F2);
lookup_array = 在哪列查(A:A);
return_array = 返回什么(本例用 CHOOSECOLS 动态取部门+工资);
if_not_found = 找不到时返回什么(可选);match_mode = 0=精确(默认)、-1/1=近似;
search_mode = 1=从头(默认)、-1=倒着找。
● 对比 VLOOKUP：XLOOKUP 不用算 col_index、默认精确匹配、能往左查、找不到时直接给空——这 4 个痛点全解决了。 |
03
步骤
照下面 4 步做，就能跑通
▍ Step 1 ｜ 看一眼数据布局
左 A:D 是 14 人的「姓名/部门/岗位/工资」4 列;
右 F:H 是要查的结果(姓名/部门/工资 3 列),其中 F2 是要查的姓名。
▍ Step 2 ｜ 用 CHOOSECOLS 动态选返回列
想让返回的「部门+工资」按指定顺序排,而不是 VLOOKUP 那种「从查到的列往右数几列」,可以用 CHOOSECOLS 显式挑列：
=CHOOSECOLS(A:D, 2, 4) |
▍ Step 3 ｜ 在 H2 输入 XLOOKUP 公式
lookup_array 选 A:A(姓名列),return_array 用 Step 2 的 CHOOSECOLS 结果：
=XLOOKUP(F2, A:A, CHOOSECOLS(A:D, 2, 4)) |
▍ Step 4 ｜ 双击填充向下
把 H2 公式双击右下角十字(或拖到 H10),每个人对应一行「部门+工资」自动出结果。H 列会动态溢出成两列(部门/工资)。
04
结果怎么看
H2 溢出成两列(部门 + 工资)
因为 return_array = CHOOSECOLS(A:D, 2, 4) 本身是 2 列,公式会自动溢出成两列——G 列不需要单独写公式,一气呵成。
⚠ 溢出空间要空 H2 公式会自动向右下溢出,确保 H 列和 I 列对应位置没有数据,否则溢出冲突会报 #SPILL!。 |
05
注意事项
用之前,先记住这 5 条
⚠ 仅 Excel 365 / 2021+ 支持 XLOOKUP 是新函数,Excel 2019 及更早版本、WPS 部分版本不支持。Mac 版从 Excel 16.55+、Web 版已全面支持。 |
⚠ 默认精确匹配 XLOOKUP 第 4 参 match_mode 默认 0=精确,不用像 VLOOKUP 那样传 FALSE。要近似匹配才传 1(升序)/ -1(降序)。 |
⚠ return_array 是范围不是列号 第 3 参要写「要返回的列范围」,如 A2:A10 或表达式,而 VLOOKUP 那种「第几列」(数字)在这里行不通。 |
⚠ 溢出方向可控 默认横向溢出(返回多列时向右),纵向溢出(返回多行时向下)。返回范围比 1×1 时会自动撑开。 |
⚠ 找不到可选默认值 第 4 参 if_not_found 写 "未找到" 或 0,会直接显示默认值,不用再 IFERROR 包一层——比 VLOOKUP 干净。 |
—— 查表升级,交给 XLOOKUP 一招搞定 ——8