excel - Excel-VLOOKUP与INDEX/MATCH-哪个更好？

Question

我了解如何使用每种方法：VLOOKUP（或HLOOKUP）vs. INDEX / MATCH。

我在寻找它们之间的差异不是在个人喜好方面，而是主要在以下方面：


有一种方法可以做到而另一种方法不能做到的吗？
一般而言，哪一个效率更高（或者取决于情况）？
使用一种方法相对于另一种方法的任何其他优点/缺点


注意：我在这里回答自己的问题，但希望看看是否还有其他人有我没想到的其他见解。

Answer 1

我更喜欢在几乎每种情况下使用INDEX / MATCH，因为它要灵活得多，并且有可能根据查询表的大小而变得更有效率。

我真正可以证明使用VLOOKUP的唯一时间是针对列索引号是动态的非常简单的表，尽管即使在这种情况下，INDEX / MATCH也同样可行。

我将在下面给出一些具体示例，以说明这两种方法之间的详细区别。



INDEX / MATCH可以向左查找（或您想要的其他任何位置）

这可能是INDEX / MATCH的最明显优势，也是VLOOKUP最大的失败之一。 VLOOKUP只能在右侧查找，INDEX / MATCH可以从任何范围查找，如有必要，还可以查找不同的工作表。

以下示例无法使用VLOOKUP完成。





INDEX / MATCH有可能使用较小的像元范围（从而提高效率）

考虑下面的示例。可以使用任何一种方法来完成。





这两个公式都可以正常工作。但是，由于VLOOKUP公式包含的范围比INDEX / MATCH公式大，因此它的挥发是不必要的。

如果范围B1:G4中的任何单元格发生更改，即使更改VLOOKUP中的任何单元格不会影响公式的结果，B1:G4公式也必须重新计算（因为A1:H4在范围B1:G4之内）。对于INDEX / MATCH，这不是问题，因为其公式不包含范围B1:G4。



将VLOOKUP与固定的col_index_number一起使用很危险

我看到具有固定的列索引号的主要问题是，如果插入完整的列，它不会像应该更新的那样更新。考虑以下示例：



除非在查询表中插入一列，否则此公式可以正常工作。在这种情况下，公式将在该值的左侧查找该值。参见下文，插入一列后的结果。



实际上，可以通过使用以下VLOOKUP公式来缓解此问题：

= VLOOKUP("s",A1:H4,COLUMN(H1)-COLUMN(A1)+1,FALSE)

现在，如果插入一列， H1将自动更新为 I1，从而保留对同一列的引用。但是，这完全没有必要，因为 INDEX / MATCH可以通过以下公式完成此操作而不会出现此问题。

= INDEX(H1:H4,MATCH("s",A1:A4,0))

我意识到这是不太可能发生的情况，但是默认情况下 VLOOKUP总是基于固定的列索引查找，这总是困扰我，如果插入列，列索引不会自动更新。对我来说，这似乎使 VLOOKUP函数更加脆弱。



INDEX / MATCH也可以处理可变列索引，但是公式较长

如果列索引号本身是动态的，那实际上是我认为 VLOOKUP简化一些事情的唯一情况，但是 INDEX / MATCH替代方案同样好，只是有点混乱。请参阅以下示例。







INDEX / MATCH对于多次查找更有效

（感谢@jeffreyweir）

如果单个匹配值需要多个查找值，则使用具有匹配值的辅助单元会更加有效。这样，匹配只需要计算一次，而不用为每个查找公式计算一次。请参见下面的示例。



然后，可以使用该匹配值返回适当的查找值。参见下面的示例（公式已被拖动到右侧）。



由于匹配值是 VLOOKUP中的“内部”变量，并且无法访问，因此对于 VLOOKUP而言，此手册“拆分”匹配值和索引值不是选项。



INDEX / MATCH可以查找范围，允许进行其他操作

例如，假设您要根据列名在列中找到最大值。

您可以先使用 MATCH查找适当的列，然后使用 INDEX返回整个列的范围，然后使用 MAX查找该范围的最大值。

参见下面的示例， H4中的公式查找在单元格 G4中指定的列名称的最大值。单独使用 VLOOKUP不能实现此目的。





MATCH不必匹配精确值

通常，将 MATCH与第三个参数一起用作 0，表示“查找完全匹配”。但是根据情况，使用 -1或 1作为 MATCH的第三个参数可能非常有用。

例如，以下公式返回列 A中包含数字的最后一行的行号：

= MATCH(-1E+300,A:A,-1)

这是因为此公式从 A列的底部开始并朝顶部移动，并返回 A列中的第一行编号，其中该值大于或等于-1E + 300（基本上是任何数字）。

然后，可以将 INDEX与此结合使用以返回该单元格中的值。请参见下面的示例。





综上所述

VLOOKUP充其量与 INDEX / MATCH一样好，并且在某些情况下，令人困惑的混乱程度稍低。而且最糟糕的是， VLOOKUP比 INDEX / MATCH更不安全且易失。

还值得注意的是，如果要查找范围而不是单个值，则必须使用 INDEX / MATCH。 VLOOKUP不能用于查找范围。

由于这些原因，在几乎所有情况下，我通常更喜欢 INDEX / MATCH。