跳至主要内容

如何在 Excel 中将 XLOOKUP() 与多个条件一起使用

掌握 Excel 强大的 XLOOKUP()，通过拼接与布尔表达式在多条件下查找数据
· 9分钟 阅读

使用 AI 探索

XLOOKUP() 是 Excel 当前在查找函数家族（LOOKUP()、VLOOKUP()、HLOOKUP() 和 XLOOKUP()）中最新的函数，具备诸多优势、更强的功能以及更高的灵活性。

在本教程中，我们将先介绍 XLOOKUP() 在 Excel 中的用途，以及它相较于旧版查找函数的改进；接着了解其基本语法；然后进入重点——如何在多个条件下使用 XLOOKUP()。

如果您需要系统学习 Excel 基础，推荐从对初学者友好的 Introduction to Excel 课程开始。

要点速览

  • XLOOKUP() 是 Excel 最新的查找函数。与 VLOOKUP()、HLOOKUP() 和 LOOKUP() 相比，它更灵活、更快速，并支持多方向搜索和部分匹配。

  • 在多条件搜索时，主要有两种方法：拼接（用 & 连接查找值，如 =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)）或布尔表达式（将条件相乘，如 =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)）。

  • 拼接法更简单、可读性更好；布尔表达式则更灵活。

  • 可选参数可进一步扩展功能：[if_not_found] 可在无匹配时返回自定义文本，[match_mode] 启用近似或通配符匹配，[search_mode] 可反向搜索。

为何在 Excel 中使用 XLOOKUP()

XLOOKUP() 会在某个区域或数组中搜索，并返回首个匹配项对应的结果。如果没有精确匹配且指定了匹配类型，XLOOKUP() 也可返回近似匹配。在诸多方面，XLOOKUP() 均优于 Excel 中的前辈函数（VLOOKUP()、HLOOKUP() 和 LOOKUP()）。

它尤其支持以下能力：

  • 横向与纵向、任意方向的搜索
  • 多重搜索条件
  • 默认精确匹配，同时可进行近似匹配
  • 部分匹配
  • 返回多列与多行
  • 在无匹配时返回自定义文本。

此外，在处理大型数据时，XLOOKUP() 的性能也优于旧有查找函数。

如何在单一条件下使用 XLOOKUP()

先简要回顾 XLOOKUP() 的基本语法：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

其中必需参数为：

  • lookup_value——要搜索的值

  • lookup_array——进行搜索的数组或区域

  • return_array——要返回结果的数组或区域

可选参数为：

  • [if_not_found]——在无匹配时返回的指定文本

  • [match_mode]——匹配类型（精确或近似），以及在无匹配时返回的结果。

  • [search_mode]——搜索模式（正向或反向搜索，或在按升序/降序排序的查找数组上进行二分搜索）。

稍后我们将看到这些可选参数的示例。

想学习或复习更多实用的 Excel 函数与公式，可查看 The 15 Basic Excel Formulas Everyone Needs to Know 教程以及 Excel Formulas Cheat Sheet 速查表。

如何在多个条件下使用 XLOOKUP()

从基本语法可见，XLOOKUP() 默认按单一变量的一个查找值工作。

不过，我们可以将其改造为同时在多个变量上使用，在每个变量中搜索不同的查找值。换言之，我们可以通过应用多个条件，用 XLOOKUP() 来完成搜索。主要有两种方法：拼接与布尔表达式，下面分别介绍。

在展开技术细节前，先看一下我们要使用的 Excel 表：该表包含 10 只猫的相关信息，包括名字、颜色和年龄：

A table providing information about 10 cats, including their names, colors, and ages.

在接下来的示例中，我们将用 XLOOKUP() 通过其中两个特征来查找某只猫的第三个特征。

尽管这个表很简单，在每个案例中我们不用任何函数也能直接看出结果，但此处的重点在于理解 XLOOKUP() 多条件用法的核心原理。掌握这些原理后，您即可轻松扩展到更复杂的真实场景，例如包含两个以上的搜索条件或满足特定约束。

通过拼接实现的多条件 XLOOKUP()

拼接法相当直接：将相应的查找值与查找数组彼此拼接。我们来看它如何工作。

假设我们要找到一只名叫 Nala、白灰相间的猫的年龄。为方便起见，我们把这两个条件放在与数据表同一工作表上：

Searching for the age of a white-and-grey cat called Nala using concatenation.

此时，XLOOKUP() 的公式如下：

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

它将返回 2.5，即首个白灰相间的 Nala 的年龄（我的猫 😺）。请记住，XLOOKUP() 返回的是首个匹配项对应的结果。

为理解上述公式前两个带有与号的组件代表什么，我们可以分别在单元格中运行它们：

=B2:B11&C2:C11

结果是 NalaWhite 和 Grey。

=B2:B11&C2:C11

结果为：

The result of the concatenation of the arrays with cat names and colors.

就是这么简单：我们只是按此前讨论的 XLOOKUP() 基本语法，把相应的查找值与查找数组拼接在一起。

通过布尔表达式实现的多条件 XLOOKUP()

现在，用第二种方法——布尔表达式——来完成同样的任务：查找一只名叫 Nala、白灰相间的猫的年龄。此时 XLOOKUP() 公式如下：

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

如预期，它也会返回 2.5，即首个白灰相间的 Nala 的年龄。

我们来拆解该公式。这里的 1 表示 TRUE，也就是我们在查找数组（公式的第二个组件 (B2:B11=G2)*(C2:C11=G3)）中寻找 TRUE 的位置。

而该组件由两个因子相乘构成，分别检验两个条件：第一个判断名字是否等于目标名字，第二个判断颜色是否等于目标颜色。我们分别在单元格中运行每个因子：

=B2:B11=G2

结果为：

Returning a boolean array displaying which of the cats are called Nala.

=C2:C11=G3

结果为：

Returning a boolean array displaying which of the cats are white-and-grey.

现在运行该组件的完整形式：

=(B2:B11=G2)*(C2:C11=G3)

结果为：

Returning a boolean array displaying which of the cats are called Nala and are white-and-grey.

虽然结果中出现了两个 1（即两个 TRUE），但 XLOOKUP() 会返回与首个匹配项对应的结果。

在多条件场景下使用布尔表达式的方法看起来比拼接法更复杂也更不直观；如果条件数超过两个，这一点会更明显。那为什么不总是用拼接法呢？

原因在于：使用布尔表达式能在多条件 XLOOKUP() 中提供更高的灵活性。下面来看它相对拼接法的优势。

检查 FALSE 值

在上面的公式中，我们通过传入 1 来检查布尔表达式是否为 TRUE：

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

有时我们需要判断布尔表达式是否为 FALSE。例如，想找到表中不是白灰相间 Nala 的第一只猫的年龄。可将公式改为：

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

结果为 1，即第一只不是白灰相间 Nala 的猫的年龄（更准确地说，是一只三花的 Nala，而不是白灰相间）。

使用逻辑运算符

采用布尔表达式时，我们不仅能检查是否相等。比如，想要找到名字为 Nala 且年龄小于 2 岁的猫的颜色：

Searching for the color of a cat called Nala that is younger than 2 y.o. using boolean expressions with logical operators.

此时的 XLOOKUP() 公式为：

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

结果为 Tricolor（三花）。

满足至少一个条件

前面的示例都是要求满足提供的全部条件。在某些场景下，我们可能只需满足至少一个条件。

回到最初的任务——查找一只名叫 Nala、白灰相间的猫的年龄：

Searching for the age of a cat either called Nala or of white-and-grey color using boolean expressions and meeting at least one criterion.

这一次，我们要查找的是名字是 Nala 或 白灰相间的猫的年龄。XLOOKUP() 公式如下：

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

它返回 1，即一只三花 Nala 的年龄。

提醒一下：当我们要求同时满足“名字为 Nala”且“白灰相间”时，公式为：

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

将 * 替换为 +，逻辑就发生了改变。

若您想进一步释放 Excel 的潜能，可考虑实践导向、体系完善的 Excel Fundamentals 能力路径。

多条件 XLOOKUP() 的可选参数

与单条件下的常规用法一样，在多条件场景中我们同样可以使用函数的附加选项，即 [if_not_found]、[match_mode] 和 [search_mode] 这三个可选参数。

下面以简短示例展示在多条件下结合 XLOOKUP() 使用这些参数的方法。为简化起见，示例均采用拼接法。

无匹配时返回指定文本

这里我们要查找一只黑色 Nala 的年龄——该猫在表中并不存在：

Searching for the age of a black cat called Nala that is inexistent in the table using boolean expressions to return a supplied text when no match is found.

此时，我们在 XLOOKUP() 公式中加入可选参数 [if_not_found]，为其指定无匹配时要返回的文本：

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

函数确实返回了指定文本：No cat is found。如果未提供文本，函数会返回 #N/A。

返回近似匹配

比如，我们要查找一只三花 Nala 的年龄，但不确定表中颜色写作 "Tricolor" 还是 "Tricolour"。这时需使用基于通配符的近似匹配，并将 [match_mode] 参数设为 2。

Searching for the age of a tricolor cat called Nala using boolean expressions and an approximate match.

在上表中，我们使用了星号（*）作为通配符，表示任意数量字符（包括 0）。更多通配符可参见 Microsoft Office 的文档。

此处的 XLOOKUP() 公式为：

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

其结果为 1，即表中首个（也是唯一一个）三花 Nala 的年龄。

注意，公式中的参数 2 表示我们要进行近似匹配，而不是查找颜色为 "Tricolo*r" 的 Nala。在本例中，星号匹配到了 “Tricolor”，同样也会匹配 “Tricolour” 或例如 “Tricolooor”。

执行反向搜索

让我们重复最开始的练习——查找白灰相间 Nala 的年龄——但这次从最后一项开始搜索。

Searching for the age of a white-and-grey cat called Nala using boolean expressions and a reverse search.

为此，需要将 [search_mode] 设为 -1，如下：

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

函数返回 7，即从查找数组末尾开始找到的首个白灰相间 Nala 的年龄。

XLOOKUP() 与 INDEX()、MATCH() 的对比

在旧版 Excel 中，如需实现与多条件 XLOOKUP() 类似的功能，通常要组合 INDEX() 与 MATCH()。这里不展开两者的语法，仅对比如何用旧方法 INDEX() + MATCH() 和新方法 XLOOKUP() 来查找首个白灰相间 Nala 的年龄（单元格引用可参考前表）：

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

尽管参数看上去有些相似，但采用 XLOOKUP() 的主要优势包括：

  • 仅用一个函数即可完成，无需函数组合。

  • 在适用时可采用更直观的拼接法。

  • 可使用可选参数（MATCH() 不具备这些参数）。

结论

本教程介绍了 Excel 中 XLOOKUP() 的语法、相较旧版函数的优势，以及如何在多个条件下使用 XLOOKUP()。

更具体地说，我们讨论了两种主要的搜索方式、各自更适用的场景，以及为何布尔表达式方法能提供更高的灵活性。此外，我们还学习了如何在多条件场景中利用可选参数扩展 XLOOKUP() 的功能，以及在旧版 Excel 中如何实现与 XLOOKUP() 类似的搜索。

若要为 Excel 面试做准备，您可以查阅 Top 25 Excel Interview Questions For All Levels 指南，了解面向初级、中级与高级用户的常见 Excel 技术面试题。

Elena Kosourova

作者

Elena Kosourova

LinkedIn

IBM 认证数据科学家（2020）。此前曾在全球油气田担任石油地质学家/地质建模师，拥有 12 年以上国际工作经验。精通 Python、R 和 SQL。专长领域包括：数据清洗、数据处理、数据可视化、数据分析、数据建模、统计、数据叙事、机器学习。具备丰富的数据科学社区管理经验，并长期撰写与审校数据科学及职业发展相关的文章与教程。

常见问题

XLOOKUP() 与 VLOOKUP() 有何区别？

XLOOKUP() 比 VLOOKUP() 更通用。它可横向与纵向搜索，不要求查找列位于最左侧，支持多条件、支持反向搜索，并在大数据集上表现更快。VLOOKUP() 只能纵向搜索，且要求数据以特定顺序排列。

XLOOKUP() 能处理两个以上的条件吗？

可以。XLOOKUP() 能处理超过两个的条件。使用拼接法时，只需增加更多与号：=XLOOKUP(A1&B1&C1, D:D&E:E&F:F, G:G)。使用布尔表达式时，乘上额外条件即可：=XLOOKUP(1, (D:D=A1)*(E:E=B1)*(F:F=C1), G:G)。

多条件场景下，拼接法与布尔表达式哪种更好？

对于简单的精确匹配，拼接法更直观。若需要使用逻辑运算符（>、<、>=、<=）、检查 FALSE 值，或实现“或（OR）”逻辑而非“与（AND）”逻辑，布尔表达式更灵活。请根据具体需求选择。

为何我的 XLOOKUP() 公式返回 #N/A？

#N/A 错误表示未找到匹配项。要更优雅地处理，可使用 [if_not_found] 参数：=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found")。这样将显示您的自定义消息，而非错误。

主题

Excel