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:
有没有一种方法可以做而另一种方法做不到的事情?
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.
唯一一次我可以真正证明使用 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在B1:G4范围内code>A1:H4) 即使更改 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 的一个选项，因为匹配值是 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.
INDEX/MATCH 可以查找一个范围，允许另一个操作
例如，假设您想根据列名查找列中的最大值.
Let's say for example you want to find a max value in a column based on the column name.
你可以先用MATCH找到合适的列，然后INDEX返回整列的range，然后用MAX 找到该范围的最大值.
You can first use MATCH to find the appropriate column, then INDEX to return the range of that entire column, then use MAX to find the max of that range.
见下例，H4中的公式查找G4单元格中指定列名的最大值.这不能单独使用 VLOOKUP 来完成.
See example below, the formula in H4 looks up the max value of the column name specified in cell G4. This cannot be accomplished using VLOOKUP alone.
MATCH 不必匹配精确值
通常 MATCH 与第三个参数 0 一起使用，意思是找到完全匹配".但是根据情况，使用 -1 或 1 作为 MATCH 的第三个参数会非常有用.
Usually MATCH is used with the third argument as 0, meaning "find an exact match". But depending on the situation, using -1 or 1 as the third argument of MATCH can be very useful.
例如，以下公式返回 A 列中包含数字的最后一行的行号:
For example, the following formula returns the row number of the last row in column A that contains a number:
= MATCH(-1E+300,A:A,-1)
这是因为这个公式从 A 列的底部开始向顶部移动，并返回 A 列中的第一个行号，其中值大于等于-1E+300(基本上是任意数).
This is because this formula starts from the bottom of the A column and works its way toward the top, and returns the first row number in the A column where the value is greater than or equal to -1E+300 (which is basically any number).
然后 INDEX 可以与此结合使用以返回该单元格中的值.请参阅下面的示例.
Then INDEX can be used in combination with this to return the value in that cell. See example below.
总结
VLOOKUP 充其量与 INDEX/MATCH 一样好，而且在某些情况下，无可否认的混乱程度要低一些.最糟糕的是，VLOOKUP 比 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.
另外值得注意的是，如果您想查找范围而不是单个值，则必须使用INDEX/MATCH.VLOOKUP 不能用于查找范围.
Also worth noting that if you want to look up a range instead of a single value, INDEX/MATCH must be used. VLOOKUP cannot be used to look up a range.
出于这些原因，我通常在几乎所有情况下都更喜欢 INDEX/MATCH.
For these reasons, I generally prefer INDEX/MATCH in practically all situations.
这篇关于Excel - VLOOKUP 与 INDEX/MATCH - 哪个更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
