目录
INDEX函数 - 语法和用法
MATCH函数 - 语法和用法
如何在Excel中使用INDEX MATCH函数
INDEX MATCH与VLOOKUP的比较
使用INDEX MATCH代替VLOOKUP的4个主要原因
Excel INDEX MATCH - 公式示例
从右到左查找的INDEX MATCH公式
使用INDEX MATCH MATCH在行和列中查找
此公式的工作原理
Excel INDEX MATCH查找多个条件
这些公式的工作原理
Excel INDEX MATCH与AVERAGE、MAX、MIN
结合MAX的INDEX MATCH
结合MIN的INDEX MATCH
结合AVERAGE的INDEX MATCH
结合IFNA / IFERROR使用INDEX MATCH
练习工作簿下载
首页 软件教程 办公软件 Excel索引匹配与Vlookup-公式示例

Excel索引匹配与Vlookup-公式示例

May 16, 2025 am 09:22 AM

本教程展示了如何在Excel中使用INDEX和MATCH函数,以及它为什么比VLOOKUP更优越。

在最近的几篇文章中,我们努力向初学者解释了VLOOKUP函数的基础知识,并为高级用户提供了更复杂的VLOOKUP公式示例。现在,我将尝试不仅仅是劝您不要使用VLOOKUP,而是至少展示一种在Excel中进行垂直查找的替代方法。

“为什么我需要这个?”您可能会问。因为VLOOKUP有许多限制,在许多情况下可能会阻止您获得期望的结果。另一方面,INDEX MATCH组合更灵活,具有许多出色的功能,使其在许多方面优于VLOOKUP。

Excel INDEX MATCH vs. VLOOKUP - formula examples

Excel INDEX和MATCH函数 - 基础知识 --------------------------------------------

由于本教程的目的是展示如何通过结合使用INDEX和MATCH函数来在Excel中进行替代的垂直查找,我们不会详细讨论它们的语法和用法。我们只会覆盖理解总体概念所需的最低限度,然后深入探讨公式示例,这些示例揭示了使用INDEX MATCH代替VLOOKUP的所有优势。

INDEX函数 - 语法和用法

Excel的INDEX函数根据您指定的行和列号从数组中返回一个值。INDEX函数的语法非常简单:

INDEX(array, row_num, [column_num]) 以下是对每个参数的简单解释:

  • array - 您想要从中返回值的单元格范围。
  • row_num - 您想要从中返回值的数组中的行号。如果省略,则需要column_num。
  • column_num - 您想要从中返回值的数组中的列号。如果省略,则需要row_num。

有关更多信息,请参阅Excel INDEX函数。

这是一个最简单的INDEX公式示例:

=INDEX(A1:C10,2,3)

该公式在A1到C10单元格中搜索,并返回第2行第3列的单元格值,即C2单元格。

非常简单,对吧?然而,在处理真实数据时,您几乎永远不会知道想要的行和列号,这就是MATCH函数派上用场的地方。

MATCH函数 - 语法和用法

Excel的MATCH函数在单元格范围内搜索查找值,并返回该值在范围中的相对位置

MATCH函数的语法如下:

MATCH(lookup_value, lookup_array, [match_type]) - lookup_value - 您正在查找的数字或文本值。

  • lookup_array - 正在搜索的单元格范围。
  • match_type - 指定返回精确匹配还是最接近的匹配:
    • 1或省略(默认) - 近似匹配(下一个较小的)。
    • 0 - 精确匹配。在INDEX/MATCH组合中,您几乎总是需要精确匹配,因此您将MATCH函数的第三个参数设置为0。
    • -1 - 近似匹配(下一个较大的)。

例如,如果范围B1:B3包含值“New-York”、“Paris”、“London”,下面的公式返回数字3,因为“London”是范围中的第三个条目:

=MATCH("London",B1:B3,0)

有关更多信息,请参阅Excel MATCH函数。

乍一看,MATCH函数的用处似乎值得怀疑。谁会在意值在范围中的位置?我们真正想知道的是值本身。

让我提醒您,查找值的相对位置(即行和列号)正是您需要提供给INDEX函数的row_numcolumn_num参数的。您记得,Excel INDEX可以找到给定行和列交汇处的值,但它无法确定您想要的具体行和列。

如何在Excel中使用INDEX MATCH函数

现在您已经了解了基础知识,我相信您已经开始理解MATCH和INDEX是如何一起工作的。简而言之,INDEX通过列和行号查找查找值,而MATCH提供这些号码。就是这样!

对于垂直查找,您只使用MATCH函数来确定行号,并直接将列范围提供给INDEX:

INDEX(要从中返回值的列, MATCH(查找值, 要查找的列, 0)) 仍然难以理解吗?通过一个示例可能更容易理解。假设您有一份国家首都及其人口的列表:

Excel INDEX MATCH vs. VLOOKUP - formula examples

要查找某个首都的人口,比如日本的首都,请使用以下INDEX MATCH公式:

=INDEX(C2:C10, MATCH("Japan", A2:A10, 0))

现在,让我们分析这个公式的每个组成部分实际上做了什么:

  • MATCH函数在范围A2:A10中搜索查找值“Japan”,并返回数字3,因为“Japan”是查找数组中的第三个。
  • 行号直接传递给INDEX的row_num参数,指导它从该行返回一个值。

因此,上面的公式变成一个简单的INDEX(C2:C,3),表示在C2到C10单元格中搜索并从该范围的第3个单元格中提取值,即C4,因为我们从第二行开始计数。

不想在公式中硬编码城市吗?在某个单元格中输入它,比如F1,将单元格引用提供给MATCH,您将得到一个动态查找公式:

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

Excel INDEX MATCH vs. VLOOKUP - formula examples

重要提示! INDEX的array参数中的行数应与MATCH的lookup_array参数中的行数匹配,否则公式将产生错误结果。

等等……为什么我们不简单地使用以下Vlookup公式?浪费时间试图弄清楚Excel MATCH INDEX的奥秘有什么意义?

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

在这种情况下,完全没有意义 :) 这个简单的示例仅用于演示目的,以便您了解INDEX和MATCH函数如何一起工作。以下示例将向您展示这种组合的真正力量,它可以轻松应对许多复杂场景,而VLOOKUP则会遇到困难。

提示:

  • 在Excel 365和Excel 2021中,您可以使用更现代的INDEX XMATCH公式。
  • 对于Google Sheets,请参阅本文中的INDEX MATCH公式示例。

INDEX MATCH与VLOOKUP的比较

在决定使用哪个函数进行垂直查找时,大多数Excel专家都认为INDEX MATCH远优于VLOOKUP。然而,许多人仍然坚持使用VLOOKUP,首先是因为它更简单,其次是因为他们没有完全理解使用Excel中的INDEX MATCH公式的所有好处。没有这种理解,没有人愿意花时间学习更复杂的语法。

下面,我将指出MATCH INDEX相对于VLOOKUP的主要优势,您可以决定它是否值得加入您的Excel工具库。

使用INDEX MATCH代替VLOOKUP的4个主要原因

  1. 从右到左查找。 任何有经验的用户都知道,VLOOKUP无法向左查找,这意味着您的查找值始终应位于表的最左列。INDEX MATCH可以轻松进行左侧查找!以下示例展示了它的实际操作:如何在Excel中查找左侧的值。
  2. 安全地插入或删除列。 当从查找表中删除或添加新列时,VLOOKUP公式会损坏或提供错误的结果,因为VLOOKUP的语法要求指定您想要从中提取数据的列的索引号。自然,当您添加或删除列时,索引号会发生变化。使用INDEX MATCH,您指定返回列范围,而不是索引号。因此,您可以自由地插入和删除任意数量的列,而无需担心更新每个相关的公式。
  3. 查找值的大小无限制。 使用VLOOKUP函数时,您的查找条件的总长度不能超过255个字符,否则您将得到#VALUE!错误。因此,如果您的数据集包含长字符串,INDEX MATCH是唯一可行的解决方案。
  4. 更高的处理速度。 如果您的表格相对较小,Excel性能方面几乎不会有显著差异。但是,如果您的工作表包含数百或数千行,以及相应的数百或数千个公式,MATCH INDEX将比VLOOKUP运行得更快,因为Excel只需处理查找和返回列,而不是整个表数组。如果您的工作簿包含像VLOOKUP和SUM这样的复杂数组公式,VLOOKUP对Excel性能的影响可能会特别明显。关键在于检查数组中的每个值都需要单独调用VLOOKUP函数。因此,您的数组包含的值越多,您的工作簿中的数组公式越多,Excel的性能就越慢。

要了解INDEX MATCH与XLOOKUP之间的细微差别,请探索本指南中的深入分析:Excel XLOOKUP与INDEX MATCH。

Excel INDEX MATCH - 公式示例

了解了学习MATCH INDEX函数的理由,让我们进入最有趣的部分,看看如何将理论知识应用于实践。

从右到左查找的INDEX MATCH公式

如前所述,VLOOKUP无法向左查找。因此,除非您的查找值位于最左列,否则Vlookup公式几乎不可能为您带来想要的结果。Excel中的INDEX MATCH函数更具多功能性,并且并不真正关心查找和返回列的位置。

对于这个示例,我们将在示例表的左侧添加Rank列,并尝试找出俄罗斯首都莫斯科的人口排名。

在G1中输入查找值,使用以下公式在C2:C10中搜索并从A2:A10中返回相应的值:

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

Excel INDEX MATCH vs. VLOOKUP - formula examples

提示。如果您计划将INDEX MATCH公式用于多个单元格,请务必使用绝对单元格引用(如$A$2:$A$10和$C$2:$C$10)锁定两个范围,以免在复制公式时发生变形。

使用INDEX MATCH MATCH在行和列中查找

在上面的示例中,我们使用INDEX MATCH作为经典VLOOKUP的替代品,从预定义的单列范围中返回值。但是,如果您需要在多个行和列中查找呢?换句话说,如果您想执行所谓的矩阵双向查找呢?

这听起来可能很棘手,但公式与基本的Excel INDEX MATCH函数非常相似,只有一个区别。猜猜是什么?

简单地说,使用两个MATCH函数 - 一个获取行号,另一个获取列号。我祝贺那些猜对的人 :)

INDEX(array, MATCH(vlookup value, column to look up against, 0), MATCH(hlookup value, row to look up against, 0)) 现在,请查看下面的表格,让我们构建一个INDEX MATCH MATCH公式来查找给定国家在给定年份的人口(以百万计)。

在G1中输入目标国家(vlookup值),在G2中输入目标年份(hlookup值),公式如下所示:

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

Excel INDEX MATCH vs. VLOOKUP - formula examples

此公式的工作原理

每当您需要理解一个复杂的Excel公式时,请将其分解成更小的部分,看看每个单独的函数做了什么:

MATCH(G1,A2:A11,0) - 在A2:A11中搜索G1单元格中的值(“China”)并返回其位置,即2。

MATCH(G2,B1:D1,0)) - 在B1:D1中搜索以获取G2单元格中值(“2015”)的位置,即3。

上述行和列号传递给INDEX函数的相应参数:

INDEX(B2:D11, 2, 3)

结果,您在B2:D11范围内的第2行和第3列的交叉点处获得一个值,即D3单元格中的值。简单吗?是的!

Excel INDEX MATCH查找多个条件

如果您有机会阅读我们的Excel VLOOKUP教程,您可能已经测试了一个带有多个条件的Vlookup公式。然而,该方法的一个显著限制是需要添加一个辅助列。好消息是,Excel的INDEX MATCH函数也可以查找两个或更多条件,而无需修改或重组源数据!

以下是带有多个条件的通用INDEX MATCH公式:

{=INDEX(return_range, MATCH(1, (criteria1=range1) (criteria2=range2*), 0))} 注意。这是一个数组公式,必须使用Ctrl Shift Enter快捷键完成。

在下面的示例表中,假设您想根据两个条件,CustomerProduct查找金额。

以下INDEX MATCH公式效果很好:

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

其中C2:C10是要从中返回值的范围,F1是criteria1,A2:A10是与criteria1进行比较的范围,F2是criteria2,B2:B10是与criteria2进行比较的范围。

记得通过按Ctrl Shift Enter正确输入公式,Excel会自动用花括号括起公式,如截图所示:

Excel INDEX MATCH vs. VLOOKUP - formula examples

如果您宁愿不在工作表中使用数组公式,请在公式中添加另一个INDEX函数,并用通常的Enter键完成:

Excel INDEX MATCH vs. VLOOKUP - formula examples

这些公式的工作原理

这些公式使用与基本INDEX MATCH函数相同的方法,通过单列查找。为了评估多个条件,您创建两个或多个表示每个单独条件的匹配和非匹配的TRUE和FALSE值数组,然后将这些数组的相应元素相乘。乘法操作将TRUE和FALSE转换为1和0,并生成一个数组,其中1对应于满足所有条件的行。MATCH函数以查找值1查找数组中的第一个“1”,并将其位置传递给INDEX,INDEX从指定列中返回该行中的值。

非数组公式依赖于INDEX函数处理数组的原生能力。第二个INDEX配置为0 row_num,因此它将整个列数组传递给MATCH。

这是公式逻辑的高级解释。有关完整细节,请参阅Excel INDEX MATCH与多个条件。

Excel INDEX MATCH与AVERAGE、MAX、MIN

Microsoft Excel有专门的函数来查找范围内的最小、最大和平均值。但是,如果您需要从与这些值相关联的另一个单元格中获取值呢?在这种情况下,请将MAX、MIN或AVERAGE函数与INDEX MATCH结合使用。

结合MAX的INDEX MATCH

要查找列D中的最大值并从同一行中的列C返回一个值,请使用此公式:

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

结合MIN的INDEX MATCH

要定位列D中的最小值并从列C中提取关联的值,请使用以下公式:

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

结合AVERAGE的INDEX MATCH

要计算D2:D10中最接近平均值的值并从列C中获取相应的值,请使用以下公式:

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

根据您的数据如何组织,向MATCH函数的第三个参数(match_type)提供1或-1:

  • 如果您的查找列(在我们的例子中是列D)按升序排序,请输入1。公式将计算小于或等于平均值的最大值。
  • 如果您的查找列按降序排序,请输入-1。公式将计算大于或等于平均值的最小值。
  • 如果您的查找数组包含一个恰好等于平均值的值,您可以输入0以进行精确匹配。不需要排序。

在我们的示例中,列D中的人口按降序排序,因此我们使用-1作为匹配类型。结果,我们得到“Tokyo”,因为其人口(13,189,000)是最接近大于平均值(12,269,006)的匹配。

Excel INDEX MATCH vs. VLOOKUP - formula examples

您可能很好奇,VLOOKUP也可以执行此类计算,但作为数组公式:VLOOKUP与AVERAGE、MAX、MIN。

结合IFNA / IFERROR使用INDEX MATCH

正如您可能已经注意到,如果Excel中的INDEX MATCH公式找不到查找值,它会产生一个#N/A错误。如果您希望用更有意义的内容替换标准错误符号,请将您的INDEX MATCH公式包装在IFNA函数中。例如:

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

现在,如果有人输入查找表中不存在的值,公式将明确告知用户未找到匹配:

Excel INDEX MATCH vs. VLOOKUP - formula examples

如果您希望捕获所有错误,而不仅仅是#N/A,请改用IFERROR函数而不是IFNA:

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

请记住,在许多情况下,掩盖所有错误可能是不明智的,因为它们会提醒您公式中可能存在的故障。

这就是如何在Excel中使用INDEX和MATCH。我希望我们的公式示例对您有帮助,并期待下周在我们的博客上见到您!

练习工作簿下载

Excel INDEX MATCH示例(.xlsx文件)

以上是Excel索引匹配与Vlookup-公式示例的详细内容。更多信息请关注PHP中文网其他相关文章!

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热门文章

Rimworld Odyssey温度指南和Gravtech
1 个月前 By Jack chen
初学者的Rimworld指南:奥德赛
1 个月前 By Jack chen
PHP变量范围解释了
4 周前 By 百草
撰写PHP评论的提示
3 周前 By 百草
在PHP中评论代码
3 周前 By 百草

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

热门话题

Laravel 教程
1604
29
PHP教程
1509
276
如何在Windows PC上屏幕截图:Windows 10和11 如何在Windows PC上屏幕截图:Windows 10和11 Jul 23, 2025 am 09:24 AM

想要在PC上屏幕截图很常见。如果您不使用第三方工具,则可以手动进行。最明显的方法是按下PRT SC按钮/或打印Scrn按钮(打印屏幕键),该按钮将抓住整个PC屏幕。你做

如何在Word中的特定页面上启动页面编号 如何在Word中的特定页面上启动页面编号 Jul 17, 2025 am 02:30 AM

要在Word文档中从特定页面开始页码,请先插入分节符,再取消节链接,最后设置起始页码。具体步骤为:1.在目标页点击“布局”>“分隔符”>“下一页”分节符;2.双击前一节的页脚,取消勾选“链接到前一节”;3.进入新节,插入页码并设置起始数字(通常为1)。注意常见错误如未取消链接、误放分节符或手动删除页码导致不一致,操作时需仔细按步骤执行。

如何在团队视频通话中模糊我的背景? 如何在团队视频通话中模糊我的背景? Jul 16, 2025 am 03:47 AM

在Teams视频通话中模糊背景的方法如下:1.确保设备支持虚拟背景功能,需使用Windows10或11系统、最新版Teams及支持硬件加速的摄像头;2.在会议中点击“三个点”→“应用背景效果”并选择“模糊”即可实时虚化背景;3.若无法使用内置功能,可尝试第三方软件、手动设置物理背景或使用带AI功能的外接摄像头。整个过程简单,但需注意系统版本和硬件兼容性问题。

如何绘制Word文档 如何绘制Word文档 Jul 16, 2025 am 03:45 AM

在Word文档中绘图的方法主要有三种:使用“插入形状”工具、利用“绘图”面板进行手写输入、以及在插入图片后叠加绘制。首先点击“插入”→“形状”,可绘制线条、矩形、圆形等图形,并支持组合与样式调整;其次通过“绘图”选项卡,可用触控笔或鼠标选择笔型、颜色及橡皮擦等工具进行自然书写或标记;最后可在插入图片后,在图片上使用形状或墨迹工具进行标注,从而突出重点信息。

如何将图片插入Excel单元格 如何将图片插入Excel单元格 Jul 14, 2025 am 02:45 AM

在Excel中插入图片到单元格需手动调整位置和大小,并非直接嵌入。首先点击“插入”>“图片”,选择文件后拖动至目标单元格并调整大小;其次若需图片随单元格移动或缩放,右键选择“大小与属性”,勾选“随单元格改变位置和大小”;最后批量插入时可复制已设置好的图片并替换新文件。注意事项包括避免拉伸失真、设置合适行高列宽、检查打印显示及兼容性问题。

如何将图片插入Excel中的单元格 如何将图片插入Excel中的单元格 Jul 21, 2025 am 12:09 AM

在Excel中将图片嵌入单元格需设置位置属性和调整单元格大小。首先插入图片后右键选择“大小和属性”,勾选“随单元格改变位置和大小”;其次调整单元格行高或列宽适配图片,或裁剪图片保持比例;最后可用“选择性粘贴”中的“作为图片(填充单元格)”实现背景填充效果。

如何在Excel中的列中获取最后一个值 如何在Excel中的列中获取最后一个值 Jul 26, 2025 am 08:03 AM

在Excel中获取某一列的最后一个值,可根据数据特点选用不同方法:1.使用LOOKUP函数快速查找最后一个非空值,适用于数据中间可能有空行的情况,公式为=LOOKUP(2,1/(A:A""),A:A);2.使用INDEX COUNTA组合处理连续数据,适用于无空行的数据列,公式为=INDEX(A:A,COUNTA(A:A));3.使用INDEX MATCH组合获取最后一个数字值,适用于仅含数字的数据列,公式为=INDEX(A:A,MATCH(9.99E 307,A:A))。此

如何在Excel中的每个印刷页面上重复标题行 如何在Excel中的每个印刷页面上重复标题行 Jul 20, 2025 am 12:55 AM

在Excel中设置重复标题行的方法如下:1.打开Excel文件并进入需要打印的工作表;2.点击“页面布局”选项卡中的“打印标题”按钮;3.在“工作表”标签下选择“顶端标题行”,输入如$1:$1;4.点击“确定”保存设置。若标题跨多行,可选择如$1:$2,左端列重复则设置“左端标题列”,如$A:$A。常见问题包括表格内容不足跨页、标题行格式未锁定或分页符设置不当,可通过多输入数据、检查格式或调整分页预览解决。快速访问可通过快捷键Alt P S T实现。

See all articles