跳转到主要内容

​
介绍 XLOOKUP 函数

​
更灵活的查找函数 - XLOOKUP

标签XLOOKUP , XMATCH ,
VLOOKUP , HLOOKUP , LOOKUP , MATCH
在钉钉表格，可以使用 XLOOKUP 函数来实现查找匹配。相比原有的查找函数(LOOKUP, VLOOKUP, HLOOKUP)，XLOOKUP 函数更加灵活、查找速度更快。

​
XLOOKUP 简介

XLOOKUP 函数在“查找范围”中查找“目标值”，查找成功时，根据查找结果在“查找范围”的位置，在“结果范围”取对应位置的数据，作为结果返回。 XLOOKUP 有 6 个参数，依次是：
  • 目标值（必填）
  • 查找范围（必填）
  • 结果范围（必填）
  • 如果没找到目标值时返回的结果
  • 匹配类型
  • 搜索方式
具体的参数说明：在钉钉表格的工具栏公式公式.png —>全部函数，搜索“XLOOKUP”函数

​
“查找范围”和“结果范围”可以在任何位置

原有的查找函数要求“结果范围”在“查找范围”的右侧或下方，在一些场景中可能给您带来困扰。而在 XLOOKUP 函数中，“查找范围”和“结果范围”是独立的参数（第 2、3 参数），摆脱了源数据的结构要求：
image.png
图 1 在范围 B2:B5 中查找“橘子”，查找到的位置是第 2 个， 因此结果是范围 A2:A5 的第 2 个数据“dd231” XLOOKUP 函数没有“查找方向”的概念，要求“查找范围”必须是单行或单列的。根据匹配到的第几行/列，取结果范围的第几个值。如：
image.png
图 2 在范围 A1:D1 中查找“销售单价”，查找到的位置是第 3 个， 因此结果是范围 A3:D3 的第 3 个数据 2.3

​
没有找到匹配值时的默认结果

在未查找到目标值时，原有的查找函数返回 #N/A 错误。而在 XLOOKUP 中，你可以指定默认值（第 4 个参数），当未查找到目标值时，该值作为结果返回：
image.png
图 3 在范围 B2:B5 中未找到“车厘子”，因此函数以第 4 个参数作为结果

​
更细致的查找方式

XLOOKUP 函数的第 5、6 参数分别是“匹配类型”和“搜索方式”，你可以通过组合它们来指定更细致的查找方式。
匹配类型0默认值，只查找与“目标值”完全相等的值。
1优先查找与“目标值”完全相等的值。如果没有，则取**“比目标值大的”最小值**。
-1优先查找与“目标值”完全相等的值。如果没有，则取**“比目标值小的”最大值**。
2“目标值”作为通配符规则进行匹配。
搜索方式1默认值，按“查找范围”中值的顺序，依次进行匹配。
-1按“查找范围”中值的顺序，倒序进行匹配。
2认为“查找范围”中的值已经按升序排序，使用二分搜索算法进行匹配。查找速度更快，但如果“查找范围”未排序，则查找结果可能是错误的。
-2认为“查找范围”中的值已经按降序排序，使用二分搜索算法进行匹配。查找速度更快，但如果“查找范围”未排序，则查找结果可能是错误的。
例如，查找最接近目标销售额的商品：
image.png
图 4 函数配置了第 5 个参数 -1，表示查找 100 或比 100 小的最大值。 在范围 D2:D5 中找到符合要求的数据 77，因此取对应位置的结果“西瓜”返回。

​
进阶！XLOOKUP 的嵌套使用

XLOOKUP 的“结果范围”允许是多行多列，根据查找结果，XLOOKUP 会范围对应行/列的多个结果。根据这一特性，XLOOKUP 函数可以与其他函数搭配，满足更多的场景。如：
image.png
图 5 图 5 中，“Kaola” 位于“查找范围”的第 3 行，因此 XLOOKUP 函数对应地取“结果范围”的第 3 行数据，即“30, 40, 50, 60”。接着，SUM 函数对 XLOOKUP 函数的结果求和，得到最终结果“180”。
image.png
图 6 图 6 中，内层的 XLOOKUP 函数返回了结果“30, 40, 50, 60”，作为“结果范围”返回给外层 XLOOKUP 函数；接着，外层的 XLOOKUP 函数在 B2:E2 范围中查找“第三季度”，找到的位置是第 3 个，因此从“30, 40, 50, 60”中取得结果“50”。

​
扩展阅读

类似地，XMATCH 也拥有更灵活的特性和更快的查找速度，可以替代原有的 MATCH 函数。在钉钉表格的工具栏-公式-全部函数，搜索“XMATCH”函数查看详细的参数说明。
返回钉钉表格目录页