Excel索引匹配与Vlookup-公式示例
本教程展示了如何在Excel中使用INDEX和MATCH函数,以及它为什么比VLOOKUP更优越。
在最近的几篇文章中,我们努力向初学者解释了VLOOKUP函数的基础知识,并为高级用户提供了更复杂的VLOOKUP公式示例。现在,我将尝试不仅仅是劝您不要使用VLOOKUP,而是至少展示一种在Excel中进行垂直查找的替代方法。
“为什么我需要这个?”您可能会问。因为VLOOKUP有许多限制,在许多情况下可能会阻止您获得期望的结果。另一方面,INDEX MATCH组合更灵活,具有许多出色的功能,使其在许多方面优于VLOOKUP。
由于本教程的目的是展示如何通过结合使用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_num和column_num参数的。您记得,Excel INDEX可以找到给定行和列交汇处的值,但它无法确定您想要的具体行和列。
如何在Excel中使用INDEX MATCH函数
现在您已经了解了基础知识,我相信您已经开始理解MATCH和INDEX是如何一起工作的。简而言之,INDEX通过列和行号查找查找值,而MATCH提供这些号码。就是这样!
对于垂直查找,您只使用MATCH函数来确定行号,并直接将列范围提供给INDEX:
INDEX(要从中返回值的列, MATCH(查找值, 要查找的列, 0)) 仍然难以理解吗?通过一个示例可能更容易理解。假设您有一份国家首都及其人口的列表:
要查找某个首都的人口,比如日本的首都,请使用以下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))
重要提示! 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个主要原因
- 从右到左查找。 任何有经验的用户都知道,VLOOKUP无法向左查找,这意味着您的查找值始终应位于表的最左列。INDEX MATCH可以轻松进行左侧查找!以下示例展示了它的实际操作:如何在Excel中查找左侧的值。
- 安全地插入或删除列。 当从查找表中删除或添加新列时,VLOOKUP公式会损坏或提供错误的结果,因为VLOOKUP的语法要求指定您想要从中提取数据的列的索引号。自然,当您添加或删除列时,索引号会发生变化。使用INDEX MATCH,您指定返回列范围,而不是索引号。因此,您可以自由地插入和删除任意数量的列,而无需担心更新每个相关的公式。
- 查找值的大小无限制。 使用VLOOKUP函数时,您的查找条件的总长度不能超过255个字符,否则您将得到#VALUE!错误。因此,如果您的数据集包含长字符串,INDEX MATCH是唯一可行的解决方案。
- 更高的处理速度。 如果您的表格相对较小,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))
提示。如果您计划将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公式时,请将其分解成更小的部分,看看每个单独的函数做了什么:
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快捷键完成。
在下面的示例表中,假设您想根据两个条件,Customer和Product查找金额。
以下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会自动用花括号括起公式,如截图所示:
如果您宁愿不在工作表中使用数组公式,请在公式中添加另一个INDEX函数,并用通常的Enter键完成:
这些公式的工作原理
这些公式使用与基本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)的匹配。
您可能很好奇,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")
现在,如果有人输入查找表中不存在的值,公式将明确告知用户未找到匹配:
如果您希望捕获所有错误,而不仅仅是#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中文网其他相关文章!

热AI工具

Undress AI Tool
免费脱衣服图片

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

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

Clothoff.io
AI脱衣机

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

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

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

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

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

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

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

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

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