关于VLOOKUP你必须知道的23件事(第二部分)

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

进一步使用上述技术，您可以使用MATCH来查找该列在表中的位置，并返回完全动态的列索引号。

这有时被称为双向查找，因为它是在行和列的方向上查找。

一个例子是找到销售人员在指定月份的销售额，或者从指定的供应商那里找到特定产品的价格。例如，假设有一个销售人员的月销售量：

图15。

VLOOKUP可以轻松找到销售人员，但是没有办法自动处理月份名称。技巧是使用MATCH函数而不是静态列索引号。

图16

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

=VLOOKUP(H2，saledata，MATCH(H3，月，0)，0)

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

只要在完全匹配模式下使用VLOOKUP，就可以在搜索值中使用通配符。这可能看起来违反直觉，但是通配符允许您根据部分匹配进行精确匹配。

Excel提供了两个通配符：星号(*)匹配一个或多个字符，问号(？)匹配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，数据，2，0)

但是，#N/A似乎不太友好，因此您可能希望捕获此错误并显示更友好的消息。

最简单的方法是将VLOOKUP放在IFERROR函数中。IFERROR允许捕捉任意错误并返回您选择的结果。要捕捉错误并显示消息“未找到”而不是错误，您可以简单地将原始公式放入IFERROR并设置所需的结果：

图20。

如果找到查找值，则不会出现错误，VLOOKUP函数返回正常结果。以下是公式：

=IFERROR(VLOOKUP(E4，数据，2，0)，“未找到”)

19.数字作为文本可能会导致匹配错误。

有时，VLOOKUP中使用的表格可能包含以文本形式输入的数字。如果只是从表中检索数字作为文本，这并不重要。但是，如果表中的第一列包含以文本形式输入的数字，并且搜索值不是文本，则会出现#N/A错误。

在以下示例中，planet表的id是以文本形式输入的数字，由于搜索值是数字3，VLOOKUP返回一个错误：

图21。

为了解决这个问题，有必要确保查找值和表中的第一列是相同的数据类型(两个数字或两个文本)。

一种方法是将查阅列中的值转换为数字。但是，如果不容易控制源表，也可以调整VLOOKUP公式，将搜索值转换为文本，如下图，在搜索值“”后连接：

=VLOOKUP(idn " "，行星，2，0)

图22。

如果您不确定何时会有数字，何时会有文本，您可以通过将VLOOKUP放入IFERROR来处理这两种情况：

=iforr(VLOOKUP(idn，行星，2，0)，VLOOKUP(IDN " "，行星，2，0))

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

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

例如，嵌套IF语句的一个常见用法是根据分数确定等级。在下面的示例中，您可以看到可以实现用嵌套的IF语句构建的公式。

图23。

完整的中频嵌套公式如下：

=IF(C564，“F”，IF(C573，“D”，IF(C585，“C”，IF(C595，“B”，“A”)))

公式工作正常，但需要注意的是，逻辑关系和实际得分是直接写入公式的。如果因为任何原因修改了分数，需要仔细更新公式，然后复制到整个表格中。

相比之下，VLOOKUP可以使用一个简单的公式来分配分数。需要做的是确保为VLOOKUP设置了评分表，即按分数排序，并包含括号来处理所有分数。

图24。

此时，使用的公式是：

=VLOOKUP(C5，等级，2，真)

这种方法的优点是逻辑和分数都内置在性能指标表中。如有变更，可直接修改更新表，VLOOKUP公式自动更新，无需编辑公式。

21.VLOOKUP只能处理一个条件。

根据设计，VLOOKUP只能根据单个条件查找值，这个条件在表的第一列(搜索列)作为搜索值被找到。

这意味着在“会计”中很难找到姓“史密斯”的员工，或者在单独的列中按照名字和姓氏找到员工。

然而，有办法克服这个限制。一种解决方案是创建辅助列，用于连接不同列的值以创建查找值，类似于多个条件。比如你想找员工的部门和组，但是名字和姓氏在不同的列，怎么能同时找到呢？

图25。

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

图26。

然后，让VLOOKUP使用包含新列的表，并将名和姓连接为搜索值：

图27。

最后，的VLOOKUP公式使用辅助列作为搜索列来查找名字和姓氏链接在一起的值：

=VLOOKUP(C3D3，名称，4，0)

22.两个VLOOKUP比一个VLOOKUP快。

这可能看起来很疯狂，但是当你有很多数据，需要执行完美匹配时，你可以通过在公式中添加另一个VLOOKUP来加快VLOOKUP的速度！

背景：假设你有大量的订单数据，比如一万多条记录，你在用VLOOKUP根据订单ID来查找订单总数。因此，使用的公式如下：

=VLOOKUP(order_id，order_data，5，FALSE)

公式的最后一个FALSE强制VLOOKUP执行精确匹配。你需要一个完美的匹配，因为订单号可能找不到。在这种情况下，精确匹配设置将导致VLOOKUP返回#N/A错误。

问题是精确匹配非常慢，因为Excel必须以线性方式遍历所有值，直到找到匹配或不匹配。

相反，近似匹配相当快，因为Excel可以执行所谓的二分搜索法。

然而，二分搜索法(VLOOKUP处于近似匹配模式)的问题是，当没有找到值时，VLOOKUP可能会返回错误的结果。更糟糕的是，结果可能看起来完全正常，所以很难发现错误。

解决方法是在近似匹配模式下使用VLOOKUP两次。第一个示例只是检查该值是否真的存在。如果是这样，另一个VLOOKUP运行(同样，在近似匹配模式下)以获得所需的数据。如果没有，您可以返回任何您想要的值来指示没有找到结果。

最终公式如下：

=IF(VLOOKUP(order_id，order_data，1，TRUE)=order_id，VLOOKUP(order_id，order_data，5，FALSE)，“缺失”)

请注意，使用这种技术时，必须对数据进行排序。这只是一种防止搜索值丢失的方法，同时保持快速搜索。

23.索引和MATCH的结合可以做VLOOKUP能做的任何事情，甚至更多。

关于INDEX和MATCH哪个组合比VLOOKUP更好，网上有很多争议。

Match可以完成VLOOKUP(和HLOOKUP)能做的一切，它更灵活但更复杂。因此，支持INDEX MATCH的人会说，最好先学习INDEX和MATCH，因为它最终会为你提供更好的工具集。

反对INDEX MATCH的理由是它需要两个函数，所以用户学习和掌握起来比较复杂。

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

但是，我们也应该学习VLOOKUP，因为VLOOKUP的用法在很多工作表中经常出现。在直观的情况下，VLOOKUP可以毫不费力地实现其目标。