Google 表格中 XLOOKUP 函数的完整指南

最后更新： 2026年13月07日
作者： 阿尔贝托·纳瓦罗

  • 熟练掌握执行精确搜索、近似搜索和通配符搜索的高级语法。
  • 能够管理动态结果范围和反向搜索（自下而上）。
  • 数据清洗技术及其与辅助函数结合进行局部搜索。
Google 表格中 XLOOKUP 函数的完整指南

如果你曾经因为查找列不在左侧而对经典的 VLOOKUP 函数感到困惑， XLOOKUP 函数可以极大地简化你的公式。该函数在 Excel 中称为 XLOOKUP，在 Google Sheets 中也有提供，不过在这种情况下，您必须输入其英文名称。

它的主要优势在于，它允许您在行或列中搜索值，并返回来自不同范围的信息，无论该范围位于左侧还是右侧。此外，它还提供不同的匹配和搜索模式，使您能够查找列表中数据点的精确值、近似值、部分值，甚至是最后一个匹配项。

基本结构和操作

XLOOKUP 函数的基本操作

首先，必须了解公式的构成方式。其完整语法如下： XLOOKUP(clave_búsqueda; intervalo_búsqueda; intervalo_resultado; valor_no_encontrado; modo_coincidencia; modo_búsqueda).

前三个论点表明 你想查找哪个值？应该在哪里查找？结果包含在哪个区间内？其余功能均为可选，允许您在未找到匹配项时显示自定义消息、选择匹配类型以及决定列表的遍历方向。

如何使用 XLOOKUP 函数在 Excel 和 Google Sheets 表格中查找数据
相关文章：
如何使用 XLOOKUP 函数在 Excel 和 Google Sheets 表格中查找数据

例如，公式 =XLOOKUP(E2;A2:A100;C2:C100;"No encontrado") 它会在 A 列中查找单元格 E2 的内容，并返回 C 列中对应的值。如果找不到匹配项，则会显示指定的消息，而不是报错。

独家内容 - 点击这里  如何在 Google 表格中链接图像

与 VLOOKUP 不同，这里 搜索区间和结果区间是相互独立的。这样，您就可以在 E 列中搜索代码，并返回 A 列中关联的名称。如果结果范围包含多个列，XLOOKUP 可以将匹配行中的所有值作为数组返回。

匹配选项和搜索方向

对应于 匹配模式 这决定了当找不到所需精确值时公式应该如何处理。如果留空或设置为 0，XLOOKUP 函数将查找完全匹配的值。

值 1 返回完全匹配或次高值，而 -1 返回完全匹配或次低值。这些选项在处理比率、分数范围、价格类别或分级表格时非常有用。

值 2 激活 通配符匹配模式在此模式下，您可以使用星号表示任意字符集，使用问号表示单个字符。

您还可以使用搜索模式来控制顺序。值为 1 时，遍历区间从头到尾；值为 -1 时，则反转方向，允许您找到端点。 值的最后一次出现。 例如：

=XLOOKUP(E2;A2:A100;C2:C100;"No encontrado";0;-1)

选项 2 和 -2 使用二分查找，对于非常大的范围可能速度更快。但是，它们要求区间已按升序或降序正确排序。如果数据未按此顺序排序，则结果可能不正确。

独家内容 - 点击这里  如何在人工智能助手之间迁移数据和内存

处理文本的高级技巧

Google 表格中处理文本的高级技巧

当直接在公式中搜索文本时，必须将其用引号括起来，例如： "Cliente 2"您还可以使用对包含该术语的单元格的引用，这样您就可以在不编辑公式的情况下更改搜索。

如果您需要查询完整的密钥列表，可以将 XLOOKUP 函数与以下函数结合使用： MAP 和 LAMBDA。 例如：

=MAP(E2:E10;LAMBDA(clave;XLOOKUP(clave;A2:A100;B2:B100;"No encontrado")))

该公式对 E2:E10 范围内的每个单元格执行独立搜索，并显示所有相应的结果。

默认情况下，XLOOKUP 函数不区分大小写。要执行严格区分大小写的搜索，您可以…… 将 XLOOKUP 函数与 EQUAL 函数结合使用:

=XLOOKUP(VERDADERO;IGUAL(A2:A100;E2);B2:B100;"No encontrado")

在这种情况下，IGUAL 生成一个真值和假值的数组，而 XLOOKUP 返回第一个完全匹配的结果，并区分大小写字母。

部分搜索和数据清洗

如果你只了解部分内容，你可以求助于…… 使用星号的通配符以下公式查找包含 E2 单元格中输入的文本的单元格，无论该文本位于哪个位置：

=XLOOKUP("*"&E2&"*";A2:A100;B2:B100;"No encontrado";2)

如需更个性化的搜索，您还可以将 XLOOKUP 与 ISNUMBER 以及 FIND 或 LOOKUP 函数结合使用。FIND 函数不区分大小写，而 LOOKUP 函数区分大小写。

意外添加的空格也可能导致两个看似相同的文本不匹配。在这种情况下，您可以使用以下方法清理数据： 函数空格这样就消除了单词之间的前导空格、尾随空格和不必要的重复。

独家内容 - 点击这里  打印队列卡住了：这意味着什么以及如何正确清除它

在大型表格中，通常最好创建一个包含更正后文本的辅助列，而不是在每个查找表中对整个范围应用 `SPACE` 子句。这样可以简化公式，并减少电子表格的工作量。

如果您需要在短语中查找完整的单词，并避免匹配较长的单词片段，最好使用 REGEXMATCH 创建条件，而不是在文本中添加人为空格。

当发生多个巧合事件时会发生什么？

当范围包含多个具有相同键的行时，XLOOKUP 默认返回 1。 他发现的第一个巧合如果要检索最后一个数据，必须使用 -1 作为搜索模式，以便公式从末尾开始遍历数据。

但是，XLOOKUP 函数只能返回一个位置。如果您需要检索所有具有相同值的行，则更合适的函数是 FILTER。例如：

=FILTRAR(B2:D100;A2:A100=E2)

此公式将显示 A 列内容与 E2 中输入的键匹配的所有记录。

掌握 XLOOKUP 函数需要理解其基本语法和局限性。它是一个非常实用的工具，可以替代许多传统的 VLOOKUP 公式，支持任意方向的搜索、检索最后一个匹配项以及处理部分文本。即便如此，当分析需要多个匹配项或更复杂的条件时，仍然需要使用 FILTER、MAP 或 REGEXMATCH 等函数。