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能够执行所谓的二分查找。

然而，二分查找的问题（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可以毫不费力地实现目的。

阅读全文