跳至主要内容

Excel MATCH() 函数：如何在区域中找到某个值的位置

学习如何使用 Excel 的 MATCH() 函数在区域内定位某个值的位置，包括其语法、用例与进阶技巧。
· 8分钟 阅读

使用 AI 探索

在本文中，我将展示与 Excel 其他查找函数相比，MATCH() 函数有哪些独特之处。

MATCH() 的独特之处在于，它不像 VLOOKUP() 或 HLOOKUP() 那样返回实际的值，而是返回某个值所在的位置，在很多场景下这都会非常有用——让我们来看看如何使用它。

快速答案

要使用 MATCH() 函数查找某个值的位置：

  • 单击一个空白单元格作为结果单元格

  • 输入 =MATCH(

  • 然后用引号输入查找值

  • 选择 lookup_array。

  • 输入匹配类型（精确匹配用 0），然后按下Enter。

例如，我在 A2:B6 区域有水果及其销量的列表，我想在该列表中找到 Orange 的位置。为此，我输入以下公式并按下 Enter： 

=MATCH(“Orange”, A2:A6, 0)

这将显示 4，因为 Orange 是列表中的第 4 个水果。

在 Excel 中使用 MATCH 函数。

使用 MATCH() 函数。图片来源：作者。

理解 Excel MATCH()

使用 MATCH() 的方式不止一种，不同的用法会影响结果。让我们更详细地理解它，便于您选择最适合自己的方法。

什么是 MATCH() 函数？

Excel 中的 MATCH() 函数返回某个值在列表中的位置。它不会返回实际的值，而是告诉我们该值位于哪里。当我们需要知道位置而非值本身时（尤其是做动态查找时），它最为好用。

MATCH() 函数的语法

MATCH() 的语法是：

MATCH(lookup_value, lookup_array, [match_type])

其中：

  • lookup_value 是我们要在 lookup_array 中查找的值。

  • lookup_array 是用于查找该值的列表。

  • match_type（可选）指定查找方式。1（默认）返回小于或等于 lookup_value 的最大值（列表必须按升序排序）。0 返回精确匹配。-1 返回大于或等于所查找值的最小值（列表必须按降序排序）。

使用 MATCH() 前需要了解的要点 

在使用 MATCH() 之前，有一些小提示值得了解，可帮助您避免错误并简化操作：

  • MATCH() 不区分大小写，因此 apple、Apple 和 APPLE 被视为相同。

  • 如果处理文本并将 match_type 设为 0，可以使用通配符，例如用 * 表示多个字符，用 ? 表示单个字符。

  • 如果 MATCH() 找不到目标值，会返回 #N/A 错误。

Excel MATCH() 的基础用法示例

了解了 MATCH() 是什么以及如何工作之后，让我们通过几个示例看看如何使用它：

示例 1：查找数字的位置

假设我在单元格 A2:A7 有一串数字，想要找出数字 40 在该列表中的位置。可以像这样使用 MATCH() 函数：

=MATCH(40, A2:A7, 0)

也可以将查找值换成单元格引用。

=MATCH(D1, A2:A7, 0)

看左侧 Excel 行号，您会发现数字 40 在第6行。但结果显示为5。这是因为我们从 A2 开始计数，所以从那里开始计数时，得到的是5 而不是6。

在 Excel 中使用 MATCH 函数查找数字的位置。

使用 MATCH() 函数查找数字的位置。图片来源：作者。

示例 2：查找文本值的位置

我在单元格 A2:A7 有一列球员，在单元格 B2:B7 有他们的得分，按升序排序。我想查找球员Emily 的位置。为此，我的公式如下：

=MATCH(E1,A2:A6, 0)

该公式在 A2:A7 区域中搜索单元格 D1 的值，并返回 3，因为Emily 是列表中的第 3 位。

在 Excel 中使用 MATCH 函数查找文本的位置。

使用 MATCH() 函数查找文本的位置。图片来源：作者。

Excel 中的模糊匹配与通配符匹配

有时我们处理的数据并不完美——可能存在拼写错误、不同写法或格式混乱。在这种情况下，模糊匹配与通配符匹配可以帮助我们清理数据。

模糊匹配 

模糊匹配会在不同列表中找到相似但不完全相同的记录。当存在细微差异或拼写错误（如 Frank 与 Feank）时，这很有用。

我有两个数据集：客户订单列表和会员忠诚度计划。我想检查哪些客户的订单已经在忠诚度计划中，即使因为拼写错误、昵称或格式差异而有所不同。VLOOKUP() 在这里不适用，因为姓名并非完全匹配。

步骤 1：将数据转换为表格

  • 选中客户订单，按下 Ctrl + T。  

  • 选中忠诚度计划会员，按下 Ctrl + T。

确保两个表之间有空间，以保持分离。

步骤 2：将数据加载到 Power Query  

  • 单击客户订单中的任意位置，并将其命名为Orders_Table。  
  • 转到数据 > 获取数据 > 自表/区域。  
  • 关闭并加载，将其加载到 Power Query。  
  • 对忠诚度计划会员重复步骤 1–3，并将其命名为Loyalty_Table ，但这次选择关闭并加载到，并将其加载到同一个 Excel 工作表。

现在，两个表都已加载到 Power Query。

在 Excel 中将表加载到 Power Query 以执行模糊匹配。

将表加载到 Power Query。图片来源：作者。

步骤 3：使用模糊匹配合并表格  

  • 转到数据 > 获取数据 > 合并查询 > 合并查询。  
  • 在合并窗口中，选择第一个表（Orders_Table）和第二个表（Loyalty_Table）   
  • 在两个表中都选择客户名称列。  
  • 勾选使用模糊匹配复选框。  
  • 展开下拉菜单，将相似度阈值设置为0.3（允许拼写错误或昵称等一定差异）。

在 Excel 中使用模糊匹配合并表格。

使用模糊匹配合并表格。图片来源：作者。

Power Query 现在会匹配相似而非完全相同的姓名。  

步骤 4：展开匹配的数据

  • 单击匹配列旁的展开图标。
  • 从Loyalty_Table 中选择客户名称，然后单击确定。  
  • 关闭并加载结果回到 Excel。  

您可以在下图中看到，模糊查找已自动匹配了姓名存在差异的客户。

使用模糊匹配在 Excel 中合并表格。

使用模糊匹配合并的表格。图片来源：作者。

通配符匹配

当我们只知道部分信息时，通配符匹配可以帮助我们找到姓名或数值。在处理类似条目或记忆模糊时尤为有用。我们可以使用两种通配符：

  • * 匹配任意数量的字符（例如，Jo* 可匹配 John 和 Jonathan）。

  • ? 匹配单个字符（例如，J?ck 可匹配 Jack，但不匹配 Jake）。

它们的用法如下：

  • A* 匹配任何以 A 开头的内容。

  • *A 匹配任何以 A 结尾的内容。

  • *A* 匹配任何在任意位置包含 A 的内容。

例如，要找出姓名以e 结尾的人，我使用：

=MATCH("*e", A2:A11, 0)

如下图所示，公式返回 3，因为姓名 Charlie 以e 结尾。

在 Excel 中使用通配符查找位置。

使用通配符查找位置。图片来源：作者。

将 MATCH() 与其他 Excel 函数组合

MATCH() 搭配其他函数一起使用会更强大。它让我们的公式更灵活，也更易维护。让我们来看看。

将 MATCH() 与 INDEX() 结合

MATCH() 常与 INDEX() 配合用于强大的查找。与 VLOOKUP() 只能从左向右查找不同，INDEX() 与 MATCH() 搭配可以实现任意方向的查找。

例如，我有一份球员及其得分的列表，想要找到 Brian 的得分。可使用以下公式：

=INDEX(B2:B11,MATCH("Brian",A2:A11,0))

若使用单元格引用，公式如下：

=INDEX(B2:B11, MATCH(E1, A2:A11, 0))

在 Excel 中组合使用 INDEX() 与 MATCH()。

组合使用 INDEX() 与 MATCH()。图片来源：作者。

在 VLOOKUP() 中用 MATCH() 动态选择列

我们知道 VLOOKUP() 需要手动输入结果所在的列号。如果列发生变化，就必须更新公式。为避免这种情况，我们可以用 MATCH() 自动找到正确的列。

例如，我有一个数据集，想要查找 Emily 所在的团队名称。使用 VLOOKUP() 时，公式如下：

=VLOOKUP(F1, A2:C12, 3, FALSE)

该公式只能从左到右查找，且列号 3 是固定的，如果列发生变化，就需要手动更新公式。

因此，我们可以不在 VLOOKUP() 中硬编码列号，而是像这样组合 MATCH()：

=VLOOKUP(F1, A2:C12, MATCH("Team", A1:C1, 0), FALSE)

在该公式中，MATCH("Team", A1:C1, 0) 会找出哪一列包含Team 并返回列号。随后，VLOOKUP(F1, A2:C4, 3, FALSE) 将从第 3 列提取数据，而不是依赖固定数值。

在 Excel 中组合使用 VLOOKUP() 与 MATCH()。

组合使用 VLOOKUP() 与 MATCH()。图片来源：作者。

结合 EXACT() 实现区分大小写的 MATCH()

默认情况下，MATCH() 忽略大小写，因此 Apple 和 apple 会被视为相同。如果需要区分大小写的搜索，我们需要将 MATCH() 与 EXACT() 函数组合，如下：

=MATCH(TRUE, EXACT(A2:A7, D2), 0)

这是一个数组公式，因此请按 Ctrl + Shift + Enter。 在该公式中，EXACT(A2:A4, "Emily") 会检查每个姓名，仅对完全匹配返回 TRUE。随后 MATCH(TRUE, ...) 会找到第一个 TRUE 并返回其位置。

在 Excel 中组合使用 EXACT() 与 MATCH()。

组合使用 EXACT() 与 MATCH()。图片来源：作者。

结语

MATCH()、通配符与模糊匹配一开始可能有些上手难度，但多练习就能为我们节省大量时间。它们在处理凌乱数据或需要更灵活的查找方式时尤其有用。

如果您想进一步学习，可以查看我们的 Excel 数据分析课程。或浏览我们的 Excel 基础技能路径，夯实基础，提升使用此类函数的信心。

起初可能会犯错，但别着急，多尝试几次就能逐渐熟悉 Excel 的用法。

Laiba Siddiqui

作者

Laiba Siddiqui

LinkedInTwitter

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

Excel 常见问题

我可以比较两列以找出匹配与差异吗？

您可以结合使用 ISNA() 和 MATCH() 函数，检查一个列表中的值是否存在于另一个列表中。

=IF(ISNA(MATCH(B1,A1:A6,0)),"Not Present","Present")

如果在 A 列未找到 B 列中的某个值，公式将返回 "Not in List 1."

使用 `MATCH()` 时如何处理错误？

当 MATCH() 无法找到某个值时，会返回 #N/A 错误。若需用自定义信息替换错误，可将公式包裹在 IFERROR() 外层，如下：

=IFERROR(MATCH("Apple", A1:A5, 0), "Not Found")

该公式会用自定义消息替代错误。

主题

Excel