当前位置: 首页 > Excel > Excel常用函数 > ExcelExcel技巧 > Excel中match与vlookup运用-INDEX函数和MATCH怎么用?_Excel学习网

Excel中match与vlookup运用-INDEX函数和MATCH怎么用?_Excel学习网

发布时间:2020年09月27日 07:37:01 来源: 点击量:632

【摘要】为什么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:

Excel中match与vlookup运用-INDEX函数和MATCH怎么用?_Excel学习网

(您可以 在此处下载此工作簿。)

这是我们正在使用的三个功能:

= 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))

Excel中match与vlookup运用-INDEX函数和MATCH怎么用?_Excel学习网

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培训,辅导和咨询。

分享到: 编辑:wangmin

就业培训申请领取
您的姓名
您的电话
意向课程
点击领取

环球青藤

官方QQ

扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群

绑定手机号

应《中华人民共和国网络安全法》加强实名认证机制要求,同时为更加全面的体验产品服务,烦请您绑定手机号.

预约成功

本直播为付费学员的直播课节

请您购买课程后再预约

环球青藤移动课堂APP 直播、听课。职达未来!

安卓版

下载

iPhone版

下载
环球青藤官方微信服务平台

刷题看课 APP下载

免费直播 一键购课

代报名等人工服务

课程咨询 学员服务 公众号

扫描关注微信公众号

APP

扫描下载APP

返回顶部