Excel - VLOOKUP与INDEX / MATCH - 哪个更好？ [英] Excel - VLOOKUP vs. INDEX/MATCH - Which is better?

问题描述

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

I understand how to use each method: VLOOKUP (or HLOOKUP) vs. INDEX/MATCH.

我正在寻找他们之间的差异而不是个人偏好，但主要在以下几个方面：

I'm looking for differences between them not in terms of personal preference, but primarily in the following areas:

1. 有一种方法可以做到对方不能做的事情吗？

1. Is there something that one method can do that the other cannot?

哪一个效率更高（或者取决于具体情况）？

Which one is more efficient in general (or does it depend on the situation)?

使用一种方法与另一种方法的任何其他优点/缺点

Any other advantages/disadvantages to using one method vs. the other

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

NOTE: I am answering my own question here but looking to see if anyone else has other insights I hadn't thought of.

推荐答案

我更喜欢在几乎所有情况下都使用 INDEX / MATCH ，因为它更灵活，有可能更多根据查找表的大小，效率更高。

I prefer to use INDEX/MATCH in practically every situation because it is far more flexible and has the potential to be much more efficient depending on how large the lookup table is.

T.他唯一可以证明使用 VLOOKUP 的时候是非常直接的表，其中列索引号是动态的，尽管在这种情况下， INDEX / MATCH 同样可行。

The only time when I can really justify using VLOOKUP is for very straight-forward tables where the column index number is dynamic, although even in this case, INDEX/MATCH is equally viable.

我将举几个具体的例子下面将演示两种方法之间的详细差异。

I'll give a few specific examples below to demonstrate the detailed differences between the two methods.

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

这可能是 INDEX / MATCH 以及 VLOOKUP 的最大垮台之一。 VLOOKUP 只能向右查找， INDEX / MATCH 可以从任何范围查找，包括必要的不同表格。

This is probably the most obvious advantages to INDEX/MATCH as well as one of the biggest downfalls of VLOOKUP. VLOOKUP can only lookup to the right, INDEX/MATCH can lookup from any range, including different sheets if necessary.

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

The example below cannot be accomplished with VLOOKUP.

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

请考虑以下示例。它可以用任何一种方法完成。

Consider the example below. It can be accomplished with either method.

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

Both of these formulas work fine. However, since the VLOOKUP formula contains a larger range than the INDEX/MATCH formula, it is unnecessarily volatile.

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

If any cell in the range B1:G4 changes, the VLOOKUP formula must recalculate (because B1:G4 is within the range A1:H4) even though changing any cell in B1:G4 will not affect the outcome of the formula. This is not an issue for INDEX/MATCH because its formula does not contain the range B1:G4.

使用固定col_index_number的VLOOKUP是危险的

我看到固定列索引号的主要问题是，如果插入完整列，它将不会更新。请考虑以下示例：

The main issue I see with having a fixed column index number is that it will not update as it should if full columns are inserted. Consider the following example:

此公式有效除非在查找表中插入列，否则很好。在这种情况下，公式将查找它应该位于左侧的值。插入一列后见下面的结果。

This formula works fine unless a column is inserted within the lookup table. In that case, the formula will lookup the value to the left of where it should. See below, result after a column has been inserted.

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

This can actually be alleviated by using the following VLOOKUP formula instead:

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

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

Now H1 will automatically update to I1 if a column is inserted, thus preserving the reference to the same column. However, this is entirely unnecessary because INDEX/MATCH can accomplish this without this problem with the formula below.

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

我意识到这是一个不太可能的情况，但它始终困扰我 VLOOKUP 默认情况下基于固定列索引查找，如果插入列，则不会自动更新。对我来说，似乎只是让 VLOOKUP 功能更加脆弱。

I realize this is an unlikely scenario, but it always bothered me that VLOOKUP by default looks up based on a fixed column index that does not automatically update if columns are inserted. To me, it just seems to make the VLOOKUP function more fragile.

INDEX / MATCH也可以处理变量列索引，但更长的公式

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

If the column index number itself is dynamic, this is really the only case when I think VLOOKUP simplifies things a bit, but again the INDEX/MATCH alternative is just as good, just slightly more confusing. See below examples.

INDEX / MATCH对多次查找更有效

（感谢@jeffreyweir）

如果需要多个查找值单个匹配值，具有匹配值的辅助单元格效率更高。这样，匹配只需要计算一次，而不是每个查找公式计算一次。请参阅下面的示例。

If multiple lookup values are needed for a single match value, it is much more efficient to have a helper cell with the match value. This way, the match only has to be computed once, instead of one for each lookup formula. See example below.

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

This match value can then be used to return the appropriate lookup values. See example below, (formula has been dragged to the right).

本手册拆分匹配值和索引值不是 VLOOKUP 的选项，因为匹配值是<$中的内部变量c $ c> VLOOKUP 且无法访问。

This manual "splitting" of the match value and index values is not an option with VLOOKUP since the match value is an "internal" variable in VLOOKUP and cannot be accessed.

摘要

VLOOKUP 充其量只能与 INDEX / MATCH 并且在某些情况下可以忽略不计。在最坏的情况下， VLOOKUP 比 INDEX / MATCH 。由于这些原因，我通常在几乎所有情况下都喜欢 INDEX / MATCH 。

VLOOKUP is, at best, as good as INDEX/MATCH and admittedly slightly less confusing in some situations. And at worst, VLOOKUP is much more unsafe and volatile than INDEX/MATCH. For these reasons, I generally prefer INDEX/MATCH in practically all situations.

这篇关于Excel - VLOOKUP与INDEX / MATCH - 哪个更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！