我更喜欢使用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
.
使用带有固定 col_index_number 的 VLOOKUP 是危险的
我看到的固定列索引号的主要问题是,如果插入完整的列,它不会像应该的那样更新。考虑以下示例:
除非在查找表中插入列,否则此公式可以正常工作。在这种情况下,公式将在应有位置左侧查找值。请参阅下面,插入列后的结果。
实际上可以通过使用以下方法来缓解这种情况VLOOKUP
公式改为:
= VLOOKUP("s",A1:H4,COLUMN(H1)-COLUMN(A1)+1,FALSE)
Now 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
返回range整个列的,然后使用MAX
找到该范围的最大值。
请参阅下面的示例,其中的公式H4
查找单元格中指定的列名的最大值G4
。这不能通过使用来完成VLOOKUP
alone.
MATCH 不必匹配精确的值
Usually MATCH
与第三个参数一起使用,如下所示0
,意思是“找到完全匹配”。但要根据情况使用-1
or 1
作为第三个参数MATCH
非常有用。
例如,以下公式返回列中最后一行的行号A
包含一个数字:
= MATCH(-1E+300,A:A,-1)
这是因为这个公式是从底部开始的A
列并向顶部移动,并返回该列中的第一行号A
值大于或等于-1E+300(基本上是任何数字)的列。
Then INDEX
可以与此结合使用以返回该单元格中的值。请参见下面的示例。
总之
VLOOKUP
充其量相当于INDEX
/MATCH
不可否认,在某些情况下稍微不那么混乱。最坏的情况是,VLOOKUP
比INDEX
/MATCH
.
另外值得注意的是,如果您想查找range而不是单个值,INDEX
/MATCH
必须使用。VLOOKUP
不能用于查找范围。
由于这些原因,我通常更喜欢INDEX
/MATCH
几乎在所有情况下。