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

2019年10月12日 20:43
为什么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培训，辅导和咨询。