关于VLOOKUP，你必须知道的23件事（下）-英雄云拓展知识分享

16.VLOOKUP+MATCH用于完全动态的列索引

将上面介绍的技能更进一步，可使用MATCH来查找表中列的位置，返回完全动态的列索引号。

有时这被称作双向查找，由于正沿着行和列的方向查找。

一个示例是查找销售人员在指定月份的销售量，或查找从指定的供应商特定产品的价格。举例来看，假定有销售人员每个月的销售量：

图15

VLOOKUP可以容易地查找销售人员，但是没有办法自动处理月名。技能是使用MATCH函数代替静态列索引号。

图16

注意，我们给出的匹配区域包括表中所有列，以便同步VLOOKUP中使用的列号。

=VLOOKUP(H2,saledata,MATCH(H3,months,0),0)

17.VLOOKUP允许使用通配符进行部份匹配

任什么时候候在完全匹配模式下使用VLOOKUP时，都可以在查找值中使用通配符。这看起来违背直觉，但是通配符让你针对部份匹配进行精确匹配。

Excel提供了2个通配符：星号（*）匹配1个或多个字符，问号（？）匹配1个字符。

举例来看，可以直接在单元格中输入星号，然后援用它作为VLOOKUP中的查找值。在图17中，被命名为“val”的单元格H3中输入“Mon*”，将致使VLOOKUP匹配名字“Monet”。

图17

本例中的公式为：

=VLOOKUP(val,dataname,1,0)

如果你愿意，可以调剂VLOOKUP公式使用内置的通配符，以下图18所示，简单地连接单元格H3中的值和通配符。

图18

本例中，在VLOOKUP函数里将查找值和星号连接：

=VLOOKUP(val &”*”,dataname,1,0)

注意，谨慎使用通配符和VLOOKUP。虽然提供了一个容易的办法创建“偷懒的匹配”，但是也容易找到毛病的匹配。

18.可以捕获#N/A毛病并显示友好的消息

在完全匹配模式，当没有找到匹配时VLOOKUP将显示#N/A毛病。一方面，这是有用的，由于明确告知你在查找表中没有匹配。但是，#N/A看起来不是很友好，因此有几种方法可以捕获这个毛病并显示为其他内容。

一旦开始使用VLOOKUP，肯定会遇到#N/A毛病。当VLOOKUP不能找到匹配项时，产生这类毛病。

这是有用的毛病，由于VLOOKUP清楚地告知你不能够找到查找值。本例中，“Latte”在表中不存在，因此VLOOKUP抛出#N/A毛病。

图19

本例中的公式是完全标准的完全匹配：

=VLOOKUP(E4,datab,2,0)

但是，#N/A看起来不是非常友好，因此你可能想要捕获这个毛病并显示更友好的消息。

最容易的方法是将VLOOKUP放在IFERROR函数里面。IFERROR允许捕获任意毛病并返回你选择的结果。要捕获毛病并显示“没有找到”消息来代替该毛病，可以将原来的公式简单地放置在IFERROR里面并设置你想要的结果：

图20

如果找到了查找值，那末没有毛病产生并且VLOOKUP函数返回正常的结果。下面是公式：

=IFERROR(VLOOKUP(E4,datab,2,0),”没有找到“)

19.数字作为文本可能致使匹配毛病

有时，在VLOOKUP中使用的表可能包括以文本情势输入的数字。如果只是将数字作为文本从表中检索，则无关紧要。但是，如果表中的第1列包括以文本情势输入的数字，而查找值不是文本，则会出现#N/A毛病。

下面的例子中，planet表的ids是以文本情势输入的数字，由于查找值是数字3，因此致使VLOOKUP返回毛病：

图21

要解决这个问题，需要确保查找值和表中的第1列都是相同的数据类型（都是数字或都是文本）。

一种方法是将查找列中的值转换为数字。但是，如果不容易控制源表格，也能够调剂VLOOKUP公式来转换查找值为文本，以下面所示经过在查找值后连接””：

=VLOOKUP(idn &””,planets,2,0)

图22

如果没法肯定什么时候会有数字，什么时候有文本，那末可以经过在IFERROR中放置VLOOKUP来处理这两类情况：

=IFERROR(VLOOKUP(idn,planets,2,0),VLOOKUP(idn& “”,planets,2,0))

20.可使用VLOOKUP来替换嵌套的IF语句

VLOOKUP最有趣的用法之一是替换嵌套的IF语句。如果你曾构造过一系列嵌套的IF语句，知道它们工作正常，但它们需要很多括号，也必须注意嵌套的顺序，以避免引入逻辑毛病。

举例来看，嵌套的IF语句的常见用法是针对分数来肯定成绩。下面的示例中，可以看到使用嵌套的IF语句构造的公式可以实现。

图23

完全的IF嵌套公式以下：

=IF(C5<64,”F”,IF(C5<73,”D”,IF(C5<85,”C”,IF(C5<95,”B”,”A”))))

公式工作正常，但是要注意，逻辑关系和实际分数都直接编写进公式。如果因任何缘由修改了分数，需要仔细更新公式，然后将其复制到全部表中。

相比之下，VLOOKUP可使用一个简单的公式来赋值成绩。所需要做的是确保为VLOOKUP设置好成绩表，即按分数排序，且包括括号来处理所有分数。

图24

此时，使用的公式为：

=VLOOKUP(C5,grade,2,TRUE)

这类方法的好处是，逻辑和分数都内置在成绩指标表中。如果有任何改变，可以直接修改更新表，无需编辑公式，VLOOKUP公式将自动更新。

21.VLOOKUP可以仅处理单个条件

针对设计，VLOOKUP只能针对单个条件查找值，该条件作为查找值在表的第1列（查找列）中查找。

这意味着，诸如在“Accounting”中查找姓氏为“Smith”的员工，或针对在单独的列中的名字和姓氏来查找员工，都是不容易的。

但是，有办法来克服这个局限。一种解决方法是创建辅助列，用来连接来自不同列的值来创建查找值，类似多条件。举例来看，想要查找员工的部门和组，但是名字和姓氏在不同的列中，怎样实现同时查找呢？

图25

首先，添加辅助列，将名字和姓氏连接在一起：

图26

然后，让VLOOKUP来使用包括了新列的表，将名字和姓氏连接作为查找值：

图27

最后的VLOOKUP公式将辅助列作为查找列来查找名字和姓氏连在一起的值：

=VLOOKUP(C3&D3,name,4,0)

22.两个VLOOKUPS比一个VLOOKUP更快

这可能看起来很疯狂，但是当你有大量数据并需要履行完全匹配时，可以经过在公式中添加另外一个VLOOKUP来加快VLOOKUP的速度！

背景：假定你有大量的定单数据，例如超过了10000条记录，并且正在使用VLOOKUP来基于定单ID查找定单总数。因此，这种情况下，使用的公式情势以下：

=VLOOKUP(order_id,order_data,5,FALSE)

公式最后的FALSE迫使VLOOKUP履行完全匹配。你需要完全匹配，由于有可能找不到定单号。此时，完全匹配设置将致使VLOOKUP返回#N/A毛病。

问题是完全匹配非常慢，由于Excel必须以线性方式遍历所有值，直至找到匹配或不匹配。

相反，所不同的是，近似匹配相当快，由于Excel能够履行所谓的2分查找。

但是，2分查找的问题（VLOOKUP处于近似匹配模式）是当找不到值时，VLOOKUP可能返回毛病的结果。更糟的是，结果可能看起来完全正常，因此很难发现毛病。

解决方案是在近似匹配模式下使用VLOOKUP两次。第1个实例简单地检查该值是不是真的存在。如果存在，另外一个VLOOKUP运行（一样，在近似匹配模式）来获得想要的数据。如果不是，可以返回你想要的任意值来唆使没有找到结果。

最后的公式情势以下：

=IF(VLOOKUP(order_id,order_data,1,TRUE)=order_id,VLOOKUP(order_id,order_data,5,FALSE),”Missing”)

注意，使用这个技能时，数据必须已排序。这只是一种避免查找值丢失的方法，同时保持快捷查找。

23.INDEX和MATCH组合能够做VLOOKUP能够做的任何事情，乃至更多

INDEX和MATCH组合与VLOOKUP孰优孰劣，在网上有许多争辩。

INDEX+MATCH可以完成VLOOKUP（和HLOOKUP）所能做的所有事情，并且更灵活，但也更复杂。因此，这种情况下，支持INDEX+MATCH的人会说，最好先学习INDEX和MATCH，由于终究会提供给你一个更好的工具集。

反对INDEX+MATCH的观点是需要两个函数，因此对用户来讲，学习和精通更复杂。

如果常常使用Excel，需要学习如何使用INDEX和MATCH，这是一个非常强盛的组合。

但也应当学习VLOOKUP，由于常常会在很多工作表中发现VLOOKUP的使用。在直观的情形下，VLOOKUP可以绝不费力地实现目的。