Excel中match与vlookup运用-INDEX函数和MATCH怎么用?_Excel学习网
【摘要】为什么INDEX-MATCH比Excel中的VLOOKUP或HLOOKUP更好当您要在Excel中查找数据时,切勿使用名称为 lookup的工作表函数。这就是为什么Excel的
为什么INDEX-MATCH比Excel中的VLOOKUP或HLOOKUP更好
当您要在Excel中查找数据时,切勿使用名称为“ lookup”的工作表函数。这就是为什么
Excel的 VLOOKUP 函数比INDEX - MATCH 函数更受欢迎 ,这可能是因为当Excel用户需要查找数据时,才可以选择“查找”功能。
这太糟糕了,因为……
1. INDEX-MATCH比Excel的“查找”功能灵活得多。
2.在最坏的情况下,INDEX-MATCH比VLOOKUP快一点;在最佳状态下,INDEX-MATCH的速度快了很多倍。
我只能想到您应该使用VLOOKUP的两个原因(或HLOOKUP,它做同样的事情,只是侧身)。首先,您可能不知道如何使用INDEX-MATCH。如果这是问题所在,希望这篇文章对您有所帮助。
其次,您可能正在与精通VLOOKUP但对INDEX-MATCH一无所知的Excel初学者一起工作。如果这是问题所在,现在是时候为他们指明正确的方向。只需向他们发送此帖子的链接即可!
VLOOKUP和INDEX-MATCH快速入门
下图在AC列中显示了一个小型数据库,其余列中同时使用了VLOOKUP和INDEX-MATCH。为了使公式更易于讨论,我分配了以下范围名称:
MyData = Sheet1!$ A $ 3:$ C $ 6
SKU = Sheet1!$ A $ 3:$ A $ 6
Desc = Sheet1!$ B $ 3:$ B $ 6
Sales = Sheet1!$ C $ 3:$ C $ 6
这是Sheet1:
(您可以 在此处下载此工作簿。)
这是我们正在使用的三个功能:
= VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
= INDEX(range,row_index_num,column_index_num)
= MATCH(lookup_value,lookup_array,match_type)
当我们同时使用 INDEX和 MATCH时,MATCH函数将查找lookup_value的行索引或列索引号,然后将此信息传递给 INDEX函数,该函数将返回我们实际需要的信息。
这是函数的第一行:
G3:= VLOOKUP(F3,MyData,3)
H3:= INDEX(Sales,MATCH(F3,SKU))
VLOOKUP依赖于对整个数据库MyData的引用。它在MyData的最左列中查找单元格F3中的值,并从MyData的第3列返回结果。由于省略了range_lookup值,因此VLOOKUP使用近似匹配,这要求数据按升序排序。
MATCH在SKU列中搜索单元格F3中的值,并返回找到该项目的行索引号。(由于省略了match_type,因此MATCH在这种情况下也会执行近似匹配。)然后INDEX函数从Sales列返回指定行索引值的值。
那么,如果我们寻找不存在的SKU怎么办?
G4:= VLOOKUP(F4,MyData,3)
H4:= INDEX(Sales,MATCH(F4,SKU))
在这里,两个函数仍使用近似匹配。因此,由于SKU A-25在数据库中不存在,因此两者都将针对我们想要的值返回最佳猜测……这种猜测在这种情况下是不正确的。
那么,如果我们需要完全匹配并且缺少商品怎么办?
G5:= VLOOKUP(F5,MyData,3,FALSE)
H5:= INDEX(Sales,MATCH(F5,SKU,0))
在这里,添加到VLOOKUP和MATCH的最后一个参数告诉函数返回精确匹配。这两个版本均返回#N / A,因为我们的数据库中不存在SKU A-25。
在这两种情况下,与近似匹配相比,使用完全匹配都具有优点和缺点。优点是不需要对SKU数据进行排序,并且如果未找到lookup_value,则会收到一条错误消息(通常是一件好事)。缺点是近似匹配比精确匹配计算得更快。(但是,如果我们不小心的话,这种方法可以更快地为我们提供不良数据。)
最后,假设我们要在数据库中搜索商品说明,然后返回SKU?
G6:(此单元显示为灰色,因为我们无法使用VLOOKUP来完成此任务。)
H6:= INDEX(SKU,MATCH(F6,Desc,0))
在这里,单元格H6说明了INDEX-MATCH方法的功能。使用INDEX-MATCH,我们可以搜索数据库中的任何列,并从任何列返回数据。但是使用VLOOKUP,我们只能在多像元范围的最左列中搜索。
几年前,我写了一篇文章,比较了VLOOKUP和INDEX-MATCH,即 Excel的VLOOKUP与INDEX-MATCH函数。
INDEX-MATCH比VLOOKUP快
多年来,人们普遍认为VLOOKUP的计算速度比INDEX-MATCH更快。因为这在我的电子表格中似乎并不正确,所以几年前我针对此主题进行了各种测试。关于这些测试的三部分系列文章的第一篇可以在“ 使用Excel VBA来测试报告计算时间”中找到。该系列包括一个工作簿,您可以下载该工作簿以执行自己的测试。
这是我发现的结果:
对于未排序的数据,VLOOKUP和INDEX-MATCH具有大约相同的计算时间。也就是说,INDEX-MATCH仅快3%。
通过排序数据和近似匹配,INDEX-MATCH比VLOOKUP快30%。
利用排序的数据和快速的技术来查找精确匹配,INDEX-MATCH比VLOOKUP快约13%。
但是,此外,您可以使用INDEX-MATCH技术的版本来计算MUCH的速度比使用VLOOKUP更快。这是如何做:
假设您有一个大表,其中包含许多产品信息列。并假设您要在表中查找特定的SKU,并从表中的各个列中返回有关该SKU的信息。
如果使用VLOOKUP,则必须为所需的每一列信息查找相同的SKU。这些重复的查找需要很长时间才能执行。
但是,如果您使用INDEX-MATCH方法,则可以设置一个MATCH公式,该公式仅返回您感兴趣的产品的行索引号。然后,您可以使用任意数量的INDEX公式,通过单个MATCH公式从单元格获取行索引号。MATCH和VLOOKUP大约需要相同的时间来计算。但是INDEX几乎立即可用。因此,如果您要为一个SKU返回十个项目,则VLOOKUP方法将比INDEX-MATCH方法花费大约十倍的时间。
简而言之,使用INDEX-MATCH比VLOOKUP更灵活,更快捷。试试吧。你会喜欢的。
采取下一步
您可以通过两种方式执行下一步。首先,您可以 在此处下载完整的工作簿,以便轻松使用这些想法。
其次,如果您想将INDEX-MATCH公式添加到报告和分析中,我可以通过三种方式为您提供帮助。要了解更多信息,请参阅 Excel培训,辅导和咨询。
就业培训申请领取
环球青藤
官方QQ群扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群
刷题看课 APP下载
免费直播 一键购课
代报名等人工服务