在日常的数据处理工作当中，经常需要对成百上千行的数据进行查询，掌握这几个函数，让你在数据查询中游刃有余。

本系列将详细介绍六个最常用的查找引用函数：VLOOKUP、HLOOKUP、XLOOKUP、LOOKUP、INDEX、MATCH，通过通俗易懂的案例，帮助你理解它们各自的特点和适用场景。本系列共四期，本期为第二期，介绍XLOOKUP与LOOKUP函数。

点击此处查看案例文件。

3️⃣XLOOKUP——新一代的查询函数，一个函数搞定所有查询

要说VLOOKUP是Excel函数圈的"老戏骨"，那XLOOKUP绝对是当之无愧的"顶流新星"。作为WPS表格和Excel2021及以上版本推出的新函数，它彻底解决了VLOOKUP的诸多痛点，一个函数就能搞定各种查询场景。

它的基本语法是这样的：

=XLOOKUP(查找值,查找数组,返回数组,[未找到值],[匹配模式],[搜索模式])

参数详解：

1. 查找值——找什么：你要查找的值。

2. 查找数组——在哪儿找：你要搜索的列或行区域。

3. 返回数组——返回什么：你要返回的数据所在区域，与查找数组区域大小相同即可。

4. [未找到值]——找不到怎么办（可选）：自定义找不到时显示的内容。

5. [匹配模式]——怎么匹配（可选）：

0：精确匹配（默认）。若找不到返回[未找到值]/返回错误值#N/A。

1：近似匹配。精确匹配或下一个较大的项。也就是返回大于或等于查找值的最小值，要求升序排列。

-1：近似匹配。精确匹配或下一个较小的项。也就是返回小于或等于查找值的最大值，要求降序排列。

2：通配符匹配。

6. [搜索模式]——怎么搜（可选）：

1：从第一项开始顺序搜（默认）

-1：从最后一项开始倒序搜（反向搜索）

2：二分搜索（升序排列）

-2：二分搜索（降序排列）

XLOOKUP精确匹配

还以学生成绩表为例，这次通过姓名查询成绩，如下图：

A9单元格公式：

=XLOOKUP($B$9,$B$2:$B$5,A$2:A$5,"未找到")

公式拆解：

• 找什么：$B$9（混合引用，锁定B9单元格）

• 在哪找：$B$2:$B$5（姓名所在列）

• 返回什么：A$2:A$5（学号列，锁定2到5行，横向填充自动更改列，纵向填充锁定）

• 找不到怎么办：如果学号不存在，显示“未找到”，而不是#N/A

看到没？XLOOKUP比VLOOKUP简洁多了！不需要数第几列，不需要担心查找列是不是第一列，而且自带IFERROR功能，找不到也能优雅显示。

继续看一下销售数据的例子，输入公式如下图：

可以看出XLOOKUP也能查找横向数据。

再回头看看上一个例子，在使用VLOOKUP时，如果我想返回成绩，需要在每一个成绩下都写一遍公式，且列序数都要相应修改，如果使用XLOOKUP呢，看下图：

当我输入的返回数组是整个成绩所在的区域$C$2:$E$5时，返回结果自动的往后填写了，这是XLOOKUP比VLOOKUP更方便的用法：返回多列/行结果。对于连续的区域可以一次性返回多列数据，无需逐个单元格编写公式，极大地提升了工作效率。横向查找也类似，返回的数据会往下自动填写。

使用XLOOKUP时需注意：

1. 查找数组必须为单行或单列，不能是多行多列区域。

2. 返回数组的行数或列数必须与查找数组一致：

• 纵向查找：两个数组行数必须相同

• 横向查找：两个数组列数必须相同

3. 版本兼容性：XLOOKUP仅支持WPS2021以上版本和Excel2021以上版本，旧版本无法使用

XLOOKUP多条件查询

有时候需要根据多个条件查找数据，比如既要匹配学号又要匹配科目。传统方法需要借助INDEX+MATCH的复杂组合，但XLOOKUP可以轻松搞定，如下图：

C9单元格公式：

=XLOOKUP(A9&B9,$A$2:$A$5&$B$2:$B$5,$C$2:$E$5)

公式拆解：

• 找什么：A9&B9（把学号和姓名拼接成一个新值）

• 在哪儿找：$A$2:$A$5&$B$2:$B$5（把学号列和姓名列也拼接起来）

• 返回什么：$C$2:$E$5（返回对应的所有成绩）

这个技巧叫做“拼接查找法”，可以把任意多个条件拼接成一个唯一标识，实现多条件查询。

当我将公式填充到C10单元格时，出现了#N/A错误，是因为学号1002与姓名图图组合起来的新条件，在原表中没有，虽然两个数据在原表中都存在，但并不在一行，因此返回了错误值。

XLOOKUP近似匹配

还记得VLOOKUP做区间查找时要求的“首列升序”和“只写下限”吗？XLOOKUP给了我们更多选择。还是以上期的分数评级表举例，要根据分数找等级，如下图：

B2单元格公式：

=XLOOKUP($A2,$D$2:$D$5,$E$2:$E$5,,-1)

注意最后一个参数-1的含义：查找小于或等于查找值的最大值。也就是说：

查找79分时，在[0,60,80,90]中找≤79的最大值，找到60，返回“及格”；

查找85分时，找≤85的最大值，找到80，返回“良好”。

如果你想用1，那就得把对照表改为成绩上限，如下图：

查找79分时，找≥79的最小值，找到79，返回“及格”；

查找85分时，找≥85的最小值，找到89，返回“良好”。

XLOOKUP通配符查找

当你想查找包含特定关键词的内容时，可以用匹配模式2（通配符匹配）：

就以前面产品列表为例，查询红色的产品：

A8单元格公式：

=XLOOKUP("*红色*",$B$2:$B$5,$B$2:$B$5,"无",2)

公式拆解：

• 找什么："*红色*"（前后可以有任意字符，只要包含“红色”）

• 在哪找：$B$2:$B$5（在产品名称列中搜索）

• 返回什么：$B$2:$B$5（返回找到的产品名称）

• 找不到怎么办："无"（如果没有红色产品，显示“无”）

• 匹配条件：2（启用通配符匹配）

返回结果：红色连衣裙（返回第一个匹配项）

对比VLOOKUP函数，同样的也是只返回一个结果，这里返回了第一个匹配项，那如果我是想找到最后一个可以实现吗？当然可以！

XLOOKUP反向搜索

有时候数据表中可能有重复值，默认情况下XLOOKUP返回第一个找到的结果。如果你想返回最后一个，可以用搜索模式-1：

图中公式的第六个参数为-1，表示从最后一项开始往前搜索，返回找到的第一个值，因此最终找到的结果为红色T恤。

有宝宝就要问了，最后一个参数还提醒我可以输入2或者-2，这个有什么用？

XLOOKUP二分搜索

二分搜索（也叫二分查找、折半查找）是一种高效的搜索算法，XLOOKUP的第六参数[搜索模式]中设置为2或-2时，就会启用这种搜索方式。

通俗理解：就像玩“猜数字”游戏。想象一下你在猜一个1-1000之间的数字，对方只告诉你“大了”或“小了”：

• 普通查找（顺序搜索）：就像从1开始一个一个往上猜，最坏情况需要猜1000次

• 二分搜索：每次都从中间值入手，一次就能排除一半的数字

具体工作原理：每次从数据范围的正中间取值比较，如果目标值大于中间值，就舍弃前半部分，只在后半部分继续查找；反之则舍弃后半部分。每比较一次，查找范围就缩小一半，查找效率呈指数级提升。

为了直观展示二分搜索的强大，我们设计了一个真实场景：

案例背景：Unicode为全世界所有文字分配了唯一的数字编码，理论上共有1,114,112个码点。我们创建了一个包含 1,048,574行 数据（接近Excel最大行数）的编码对照表，左侧是编码数字，右侧是对应的符号。

测试过程：在表格中输入Unicode编码，XLOOKUP函数自动在左侧数据表中查询对应的符号。下图是未经加速处理的真实操作演示：点击此处获取源文件

可以看出使用顺序搜索时会有很明显的卡顿，而用二分搜索时几乎是回车的同时就显示结果。

注意：二分搜索的高效建立在严格的前提之上——查找列必须按升序（用2）或降序（用-2）正确排序。

4️⃣LOOKUP——向量查询函数，查询界的老掌门

要说VLOOKUP是当红小生，XLOOKUP是顶流新星，那LOOKUP绝对是查询函数圈的元老级人物。在Excel上古时代，当VLOOKUP还没出生的时候，LOOKUP就已经扛起了数据查询的大旗。

虽然如今风头被后辈们抢去不少，但这位"老掌门"依然有自己独特的看家本领——忽略错误值、查找最后一个匹配项、处理合并单元格，这些绝技连XLOOKUP都要甘拜下风。先来看看LOOKUP的三个参数吧：

=LOOKUP(查找值,查找向量,[返回向量])

参数详解：

1. 查找值——找什么：你要查找的值。

2. 查找向量——在哪儿找：你想要查找的区域，只能为一行或一列且必须按升序排序。

3. 返回向量——返回什么：有查找向量区域相同大小的单行或单列区域，如果不写，则返回查找向量中的值。

LOOKUP向量查询

还以分数评级表举例，要根据分数找等级，如下图：

B2单元格公式：

=LOOKUP($A2,$D$2:$D$5,$E$2:$E$5)

公式拆解：

• 找什么：$A2（分数）

• 在哪儿找：$D$2:$D$5（分数下限列，必须升序排列）

• 返回什么：$E$2:$E$5（查找到的分数对应的等级）

整个查找过程与VLOOKUP函数的近似匹配是一致的，区别在于VLOOKUP需要指定返回区域中的第几列，而LOOKUP直接指定返回区域即可，无需数列数，语法更简洁直观。此外，VLOOKUP要求查找值必须在区域的第一列，而LOOKUP的查找向量和返回向量是独立的，可以分别指定，因此可以实现向左查询。

LOOKUP数组形式

在计算成绩的等级时，面对几百个学生数据，等级仅仅只有4种，为了评级专门创建一个评级表，不仅显得臃肿突兀，万一这张表不小心被删掉，辛辛苦苦算出的等级也会瞬间付之东流。那有没有什么方法可以不用写等级表直接显示最终结果呢？

当然有，LOOKUP的数组形式！

如下图，在写公式的时候，我们在函数内插入一个常量数组，无需额外创建表格区域。

B2单元格公式：

=LOOKUP($A2,{0,60,80,90;"不及格","及格","良好","优秀"})

公式拆解：

• 查找值：$A2（学生的分数）

• 常量数组：{0,60,80,90; "不及格","及格","良好","优秀"}

• {}：花括号表示这是一个常量数组

• ,：逗号用于区分同一行中的不同元素

• ;：分号代表换行，分隔数组的不同行

这个数组的结构是两行四列：

第一行：0,60,80,90（分数下限，必须升序排列）

第二行："不及格","及格","良好","优秀"（对应的等级）

最终返回的结果就是各个等级，使用数组形式省去了辅助表的创建，让公式更独立完整。

前面介绍的三个查询函数（VLOOKUP、HLOOKUP、XLOOKUP）也可以使用数组形式，但建议只在查询表较少时使用，这里不再重复介绍。

下期预告：INDEX与MATCH函数