WPS XLOOKUP()函数用法详解（附带实例）

XLOOKUP() 函数是 VLOOKUP() 函数的升级版，在反向查找匹配、查找值包含通配符、查找匹配相近值时，都可以轻松应对，函数语法格式如下：

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

参数说明如下：

• 查找值（必填项）：值、数组、单元格区域（非整行或整列引用）；
• 查找数组（必填项）：查找单元格区域或数组（单行或单列）；
• 返回数组（必填项）：返回单元格区域或数组（支持多行或多列）；
• 未找到值（可选）：在查找单元格区域或数组匹配不到对应值时返回值；省略时默认返回 #N/A；
• 匹配模式（可选）：
  • 0（默认值） – 精确匹配；
  • -1 – 精确匹配，没有匹配项时返回下一个较小的项；
  • 1 – 精确匹配，没有匹配项时返回下一个较大的项；
  • 2 – 通配符匹配。
• 搜索模式（可选）：
  • 1（默认值） – 正序搜索；
  • -1 – 倒序搜索；
  • 2 – 二分法搜索（参数 2 需升序排序）；
  • -2 – 二分法搜索（参数 2 需降序排序）。

【实例 1】根据姓名查找匹配性别。在 G5 单元格输入公式：

=XLOOKUP(F5:F7,B5:B10,D5:D10)

效果如下图所示：


XLOOKUP() 函数的第 1 个参数引用 F5:F7 单元格区域作为查找值，第 1 个参数支持数组，支持查询匹配对应的多个值，公式计算后可以溢出到指定单元格区域，第 2 个参数引用 B5:B10 单元格区域作为查找数组，第 3 个参数引用 D5:D10 单元格区域作为返回数组，函数即可返回姓名对应的性别。

XLOOKUP() 函数和 VLOOKUP() 函数的主要区别在于，VLOOKUP() 函数查找区域需要指定一个连续的单元格区域，然后通过设置第 3 个参数返回指定的列内容，XLOOKUP() 函数将查找区域和返回值区域参数分为 2 个参数，可以分别设置查找区域和返回值区域，在查找多列内容时，可以更直观地设置引用单元格区域，无须设置返回查找区域的指定的列。

【实例 2】根据工号查找匹配姓名。在 G15 单元格输入公式：

=XLOOKUP(F15:F17,C15:C20,B15:B20)

效果如下图所示：


XLOOKUP() 函数的第 1 个参数引用 F15:F17 单元格区域作为查找值，第 2 个参数引用 C15:C20 单元格区域作为查找区域，第 3 个参数引用 B15:B20 单元格区域作为返回值区域。

可以看到，使用 XLOOKUP() 函数查找匹配时，是可以从右向左匹配的，VLOOKUP() 函数只能从左向右匹配，反向匹配需要使用 IF({1,0}) 数组公式嵌套，所以当遇到反向查找匹配的需求时，使用 XLOOKUP() 函数可以很轻松地解决问题。

【实例 3】匹配不到返回指定值。在 G25 单元格输入公式：

=XLOOKUP(F25:F27,C25:C30,B25:B30,"")

效果如下图所示：


当 XLOOKUP() 函数的第 2 个参数查找区域没有包含查找值时，函数会返回错误值 #N/A，可以使用函数的第 4 个参数将错误值返回指定值。

VLOOKUP() 函数查找匹配时，当函数返回错误值 #N/A 时，需要嵌套使用 IFNA() 函数或 IFERROR() 函数将错误值返回指定值，而通过指定 XLOOKUP() 函数的第 4 个参数，函数即可将错误值返回指定值，无须嵌套 IFNA() 函数或 IFERROR() 函数处理。

【实例 4】匹配后返回多列内容。在 H35 单元格输入公式：

=XLOOKUP(G35,$B$35:$B$40,$C$35:$E$40,"")

如下图所示：


XLOOKUP() 函数的第 1 个参数引用 G35 单元格，第 2 个参数引用 B35:B40 单元格区域，第 3 个参数引用 C35:E40 单元格区域，XLOOKUP() 函数的第 3 个参数是可以引用一个多行多列的单元格区域的，函数匹配后会返回对应行的多列内容。

需要注意的是，XLOOKUP() 函数只能返回单行或单列的数组结果，如果函数的第 3 个参数引用了多列内容，那么函数的第 1 个参数查找值只能指定一个值，输入公式后向下填充公式实现查找多个值。

如果第 1 个参数查找值引用了一个单元格区域，如引用 L35:L37 单元格区域，在 M35 单元格输入公式：

=XLOOKUP(L35:L37,$B$35:$B$40,$C$35:$E$40,"")

效果如下图所示：


可以看到，XLOOKUP() 函数返回的是第 3 个参数引用单元格区域的首列内容，函数是无法同时返回多行多列的。

【实例 5】匹配后返回多列之和。在 H45 单元格输入公式：

=SUM(XLOOKUP(G45,$B$45:$B$50,$C$45:$E$50,0))

效果如下图所示：


在使用 XLOOKUP() 函数返回多列内容后，使用 SUM() 函数对多列内容进行求和，也可嵌套其他函数对 XLOOKUP() 函数返回的多行或多列结果进行二次计算。

【实例 6】匹配包含通配符字符。在 G55 单元格输入任意一个公式：

=VLOOKUP(F55:F57,B55:D60,3,0)
=INDEX(D55:D60,MATCH(F55:F57,B55:B60,0))

效果如下图所示：


在 WPS 表格中*、?、～三个符号为通配符，VLOOKUP()函数和 MATCH() 函数是支持通配符匹配的，所以当查找值包含通配符时，使用 VLOOKUP()、MATCH() 和 INDEX() 函数匹配将无法返回正确结果，可使用 XLOOKUP()函数解决这个问题。在 G63 单元格输入公式：

=XLOOKUP(F63:F65,B63:B68,D63:D68)

效果如下图所示：


XLOOKUP() 函数的第 5 个参数细分了 4 种匹配模式，只有参数值为 2 时，函数才使用通配符模式匹配，当参数省略时，默认使用 0（精确匹配），XLOOKUP() 函数的精确匹配模式会将通配符当正常的字符来匹配。

【实例 7 】查询最近一次到店记录。在 G75 单元格输入公式：

=XLOOKUP(H72,B73:B78,C73:E78,"无记录",0,-1)

结果如下图所示：


使用 VLOOKUP() 函数查找匹配时，当查找区域有多个查找值时，函数会返回第一条出现的对应值，如果需要返回最后一次出现的对应值，则可以使用 XLOOKUP() 函数，将 XLOOKUP() 函数的第 6 个参数搜索模式设置为 –1（倒序搜索）即可返回最后一次出现的对应值。

【实例 8】精确匹配或下一个较小的项。在 F85 单元格输入公式：

=XLOOKUP(G82,D83:D88,B83:D88,,-1)

效果如下图所示：


公司在招标时，会根据规则计算出每家公司的最终得分，然后根据最终得分查询最接近标准分数且不大于标准分数的公司为中标公司，使用 XLOOKUP() 函数查找匹配时，第 5 个参数匹配模式设置为 –1（精确匹配，没有匹配项时返回下一个较小的项），函数在匹配时先使用精确匹配，当精确匹配没有匹配到查找值时，函数会返回一个小于查找值且最接近查找值的对应值。

使用 FILTER()、SORT()、TAKE() 函数可以更直观地看到计算过程。在 F88 单元格输入公式：

=TAKE(SORT(FILTER(B83:D88,D83:D88<=G82),3,-1),1)

效果如下图所示：


使用 FILTER() 函数筛选出小于或等于标准分数的数据，使用 SORT() 函数根据分数对数据降序排序，最后使用 TAKE() 函数返回排序后的第1行的内容即可。

当 XLOOKUP() 函数的第 5 个参数设置为 –1 时，查找匹配逻辑和 FILTER()、SORT()、TAKE() 函数嵌套是相同的，在处理此类查找匹配需求时，使用 XLOOKUP() 函数可以更轻松地解决问题。

【实例 9】精确匹配或下一个较大的项。在 D93 单元格输入公式：

=XLOOKUP(C93:C98,G93:G96,H93:H96,"",1)

效果如下图所示：


公司根据员工的投诉数量来评级，评级规则如图 12 右侧所示，将每个等级区间的上限提取出来作为查找区域，当最后一个等级没有具体的上限时，可以填写一个相对较大的值，如 9999 或 99 999，然后使用 XLOOKUP() 函数进行查找匹配，通过设置函数的第5个参数匹配模式为 1（精确匹配，没有匹配项时返回下一个较大的项），当精确匹配没有匹配到查找值时，函数会返回一个大于查找值且最接近查找值的对应值。

在计算学生体育成绩时，有些科目是根据完成时间来计算得分的，用时越短，得分越高，使用 XLOOKUP() 函数的第 5 个参数，将参数值设置为 1，也可很轻松地计算得分，在 D102 单元格输入公式：

=XLOOKUP(C102:C107,F102:F107,G102:G107,"",1)

效果如下图所示：


在使用 XLOOKUP() 的第 5 个参数时，当参数值设置为 1 或 –1 时，查找区域的数据无须升序或降序处理。函数在查找匹配时对查找区域的顺序是没有要求的。

【实例 10】横向查找匹配，查找最佳科目。在 G112 单元格输入公式：

=XLOOKUP(100,C112:F112,$C$111:$F$111,,-1)

效果如下图所示：


XLOOKUP() 函数的第 1 个参数使用成绩满分作为查找值，第 2 个参数引用“分数”所在的 C112:F112 单元格区域作为查找区域，第 3 个参数引用“科目”所在的 C111:F111 单元格区域，因为公式要向下填充，所以要使用绝对引用锁定“科目”单元格区域，省略第 4 个参数，第 5 个参数值设置为 –1（精确匹配，没有匹配项时返回下一个较大的项），即可查找匹配到最高分数对应的科目。

当有横向查找匹配的需求时，如使用 HLOOKUP() 函数无法实现，如从下向上查找匹配、查找值包含通配符、匹配时搜索模式需要从右向左，使用 XLOOKUP() 函数都可以轻松实现。

当查找值或查找区域的数据量很大时，可以先将查找区域及对应的返回数组区域进行升序或降序排序，在查找匹配时，将 XLOOKUP() 函数的第 6 个参数值设置为 2 或 –2（升序排序设置为 2，降序排序设置为 –2），使用二分法搜索，可以将计算效率提升几十倍，甚至上百倍，XLOOKUP() 函数可以在精确匹配的模式下使用二分法搜索，这是 VLOOKUP()、HLOOKUP()、LOOKUP() 函数无法实现的。

XLOOKUP()函数注意事项

当第 2 个参数的查找数组大小与第 3 个参数的返回数组大小不同时，函数将返回错误值 #VALUE!，在 G5 单元格输入公式：

=XLOOKUP(F5:F7,B5:B10,D5:D11)

效果如下图所示：


XLOOKUP() 函数的第 2 个参数引用 B5:B10 单元格区域，此单元格区域共 6 行，第 3 个参数引用 D5:D11 单元格区域，此单元格区域共7行，两个参数引用的单元格区域行数不同，所以函数返回错误值 #VALUE!。