跳至主要内容

如何在 VLOOKUP() 中使用多个条件

掌握在 Excel 中使用 VLOOKUP() 处理多个条件的方法。学习辅助列与 CHOOSE() 等高级技巧。
· 10分钟 阅读

使用 AI 探索

VLOOKUP() 是 Excel 中最常用的函数之一。它可以在表格中查找并从特定列检索数据。此外，它还能帮助合并不同工作表中的数据或查找相关信息。

不过，您是否知道可以扩展 VLOOKUP() 的功能以包含多个条件？当您需要在查找数据时匹配多条条件时，这会非常有用——这在处理电子表格时是很常见的问题。 

本文将介绍如何将 VLOOKUP() 与多个条件结合使用，助您提升工作效率。如果您是 Excel 新手，建议先学习我们的 Introduction to Excel 课程，了解管理表格与应用计算的基础知识。

VLOOKUP() 基础

在进入多条件的 VLOOKUP() 之前，先回顾一下 VLOOKUP() 的基本语法，如下所示： 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

上述公式中：

  • lookup_value 是您要查找的值。可以是数字、文本，或包含查找值的单元格引用。

  • table_array 是包含数据的单元格区域。该区域的第一列应包含查找键。

  • col_index_num 是您希望检索值所在的列在该区域中的列号。 

  • range_lookup 是逻辑值（TRUE 或 FALSE）。如果为 TRUE，VLOOKUP() 假定该区域的第一列按升序排序，并返回最接近的匹配值。

我们通过一个示例来理解。这里有一张员工相关数据表，我想根据员工的工号快速找到某位员工的姓名。为此，我将使用 VLOOKUP()。 

using vlookup formula on employee table.

包含员工详细信息的表格。来源：作者制图。

我先在单元格 F6 中输入一个与表格中数值匹配的值。就我而言，我参考的是 EMP ID 列，并输入 4032。

Entering the look_up value in cell.

在单元格中输入 VLOOKUP() 的查找值。来源：作者制图。

然后，我选中单元格 G6，开始输入公式。

Entering VLOOK_UP in a cell.

在单元格中输入 VLOOKUP() 公式。来源：作者制图。

接着，我选择已输入 4032 的单元格。这是我要从表格中查找的值。 

Select a cell where vlookup value is stored.

输入存放 VLOOKUP() 查找值的单元格编号。来源：作者制图。 


然后，我选择表格区域（A2:D11）。 

Select the range of table containing data in vlook up parameters.

选择包含数据的表格区域。来源：作者制图。

从左侧开始计数，我选择列号 2，因为我要查找 FIRST NAME，而 FIRST NAME 位于第二列。 

Select the number of column to show the results from.

输入要返回结果的列号。来源：作者制图。

将匹配类型输入为 FALSE 以获取精确匹配后，我按下 Enter。您可以看到，VLOOKUP() 找到了 EMP ID 等于 4032 的员工的 FIRST NAME。

Retrieving the desired output.

获取所需结果。来源：作者制图。

如何使用多个条件的 VLOOKUP()

VLOOKUP() 在基本用法下适合简单查找，但当结合多个条件时可能会变得棘手。您可以通过几种技巧来解决这些限制。我们来看看该如何处理。 

借助辅助列的 VLOOKUP() 

当您需要使用带有多个条件的 VLOOKUP() 时，可以通过辅助列把多个条件合并为一个唯一条件。以下例子将进行说明：我想基于日期与产品两项条件，为每位客户找到销售额。 

A table containing date, customer name, product and sales amount of customer.

包含客户详细信息的表格。来源：作者制图。

首先，我创建一个新列，将多条条件连接为一个唯一标识。在我的例子中，会连接三列：Customer Name、Product 和 Date。

Create a HELPER column.

创建“辅助”列。来源：作者制图。

以下是我用于将多列合并为一列的公式：

=B2&"|"&C2&"|"&D2

如您所见，每个列名后都有一个竖线符号。它作为各列数据之间的分隔符，在连接时通常被视为一种良好实践。

Concatenating 3 columns in one cell.

将三列连接在一个单元格中。来源：作者制图。

如果您好奇为何选择了日期列却显示为数字，这是因为日期格式被转换成了数字格式。查找依然能正常工作，但如果您想以更直观的形式查看数据，可以使用 TEXT() 函数。TEXT() 接受两个参数：value 是要转换的值，format_text 是要应用的目标格式。

Text Formula

TEXT 公式。来源：作者制图。

因此，为了在连接时同时格式化日期，我输入以下内容：

=B2&"|"&C2&"|"&TEXT(D2, "DD-MM-YY")

Formatting the date using the TEXT formula.

使用 TEXT() 公式格式化日期。来源：作者制图。

然后，我向下拖动公式，填充新列的所有行。

Copy the formula by dragging it.

通过拖拽复制公式。来源：作者制图。

这样就完成了。三列已成功连接。接下来，我准备查找表，见下图。 

Prepare you lookup table.

准备我的表格。来源：作者制图。

然后，我输入以下公式并按下 Enter。您可以在下方看到结果。 

=VLOOKUP(G6&"|"&H6&"|"&TEXT(I6, "DD-MM-YY"),$A$2:$E$11, 5,0)

Retrieving the sales using Vlookup with multiple criteria.

使用多条件 VLOOKUP 获取销售额。来源：作者制图。

结合 CHOOSE() 的 VLOOKUP() 

CHOOSE() 函数根据指定的索引或位置，从列表中选择并返回一个值。其语法如下：

CHOOSE(index_num, value1, [value2], ...)

在该公式中：

  • index_num 是指定要选择哪一个值的数字。

  • value1, value2, … 是可供选择的值列表。

CHOOSE() 在需要基于多个条件进行查找，或当数据结构无法整齐放入单一表格时非常有用。在这类情况下，CHOOSE() 能创建一个虚拟表，将来自不同来源的列进行组合，或重排列顺序以配合查找需求。我们通过示例来理解。我有如下数据集： 

A table containing students' scores in three quarters

包含学生三个季度成绩的表格。来源：作者制图。

现在，我想获取学生第一季度的分数。因此，我在单元格 F2 中输入以下公式：

=VLOOKUP($E2&"|"&F$1, CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15), 2, FALSE)

Fetch the scores of students from the first quarter

获取学生第一季度的分数。来源：作者制图。

公式的工作原理如下：

  • CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15) 通过合并多个条件创建虚拟辅助列，形成唯一标识。 

  • =VLOOKUP($E2&"|"&F$1, ..., 2, FALSE) 在该虚拟的第一列中查找指定值，并从第二列返回相应的分数。

这样，您无需在工作表上新增实体列，也能完成所需的查找。

VLOOKUP() 多条件的替代方案

虽然 VLOOKUP() 在 Excel 中使用广泛，但这并不是唯一的查找函数。您还可以尝试 INDEX() 与 MATCH() 等替代方案。我们来看看它们如何配合，以实现与多条件 VLOOKUP() 相同的目标。

用 INDEX() 和 MATCH() 实现 VLOOKUP()

INDEX() 与 MATCH() 联合使用可以实现动态列查找。它们不仅不要求数据按升序排序，还能同时处理横向与纵向查找。下面用这份数据集演示：

A table containing a list of employee name, their age, position and location.

一张包含员工数据的表格。来源：作者制图。

基于这些数据，我想查找一位名为 John、25 岁、居住在芝加哥的员工的职位。 

Create a look_up table to find the position of an employee.

创建参考表。来源：作者制图。

现在在 G5 中输入以下公式：

=INDEX(C2:C5,MATCH(1,(G3=A2:A5)*(G4=B2:B5)*(G6=D2:D5),0))

Using INDEX and MATCH functions with multiple criteria to find the position of John

联合使用 INDEX() 与 MATCH()。来源：作者制图。

结语

本文介绍了几种可与 VLOOKUP() 搭配的关键技巧——辅助列、数组公式，以及 INDEX() 与 MATCH() 等更灵活的函数。这些方法能让您更轻松地处理大型数据集，并提升处理数据的信心。

如果您想进一步学习在 Excel 中进行数据分析与可视化，请查看这些课程：Data Analysis in Excel 与 Data Visualization in Excel。我们还有一篇更精炼的教程：Visualizing Data in Excel。

Laiba Siddiqui

作者

Laiba Siddiqui

LinkedInTwitter

我是内容策略专家，热衷于将复杂主题化繁为简。我曾帮助 Splunk、Hackernoon 和 Tiiny Host 等公司为其受众打造兼具吸引力与信息量的内容。

Frequently Asked Questions

VLOOKUP() 能返回多个值吗？

VLOOKUP() 本身无法返回多个值，但您可以使用数组公式或 INDEX()、SMALL()、ROW() 等函数组合来检索多个值。

如何使用 VLOOKUP() 创建双向查找？

您可以将 VLOOKUP() 与 MATCH() 组合，或将 INDEX() 与 MATCH() 组合，以创建双向查找。

如何在 Excel 中执行不区分大小写的查找？

Excel 的 VLOOKUP() 不区分大小写，但您可以使用 EXACT() 等函数进行区分大小写的比较。

有哪些与多条件 VLOOKUP 相关的 Excel 函数？

有多种与 Excel 查找相关的函数：

  1. INDEX 与 MATCH。 这个强大的组合可用于执行多条件查找。INDEX 返回指定区域内某个单元格的值，MATCH 在区域中找到某个值的相对位置。相比 VLOOKUP，它们在处理复杂条件时更灵活。
  2. FILTER。 在较新的 Excel 版本中可用，FILTER 可以返回满足多个条件的结果数组。在处理多条件时，它可能是比 VLOOKUP 更直观且更动态的替代方案。
  3. SUMPRODUCT。 尽管主要用于数学运算，SUMPRODUCT 也可通过相乘对应条件的数组来实现多条件查找，从而检索特定数据点。
  4. XLOOKUP。 这一现代替代函数能更高效地处理多条件，并提供更多灵活性，包括能以任意方向查找并返回结果数组。
  5. AGGREGATE。 该函数可处理数组运算并忽略错误，适用于涉及多条件且需要有效处理错误的高级查找。

主题

Excel数据分析