解释

VLOOKUP可能是Excel中最流行的函数，也是日常使用中最有用的函数之一。

VLOOKUP帮助我们查找表中的值，并返回相应的值。

VLOOKUP在现实生活中的一个很好的例子是我们手机上的“联系人”应用程序：

我们查找朋友的名字，应用程序会返回其号码。这正是VLOOKUP所做的！

语法

=VLOOKUP（查找值、表格数组、列索引编号、[范围锁定])

查找值(lookup_value)–我们正在查找的内容–这可能是文本、数字或单个单元格引用

表格数组(table_array)–我们将在其中查找值及其相应结果的范围。请注意，范围必须从包含值的列开始，并且应该包含结果所在的列。

列索引编号（col_index_num）–要返回结果的列号是多少？该数字应相对于table_array中选定范围内的第一列。

范围锁定[range_lookup]–应使用哪种范围查找方法。0是默认值，因此您应该始终键入0（或FALSE），这意味着“完全匹配”–转到与我要查找的值完全匹配的值。1代表“近似匹配”，大多数情况下不应使用它，因此我们现在将跳过它。

tip: 带[ ] 的表示可选

实例——让我们来看看身份证号码为#646的人的年龄：

lookup_value–我们键入了646。我们还可以引用包含数字646的单元格。

table_array–这是我们执行查找的地方。我们的表从B列开始，因为这是包含ID号的列。我们可以看到，我们的表也包含Age列，因为我们希望从中返回Age。

col_index_num–我们键入了2，因为C列的相对位置是2，如果我们认为表从B列开始。

[range_lookup]–我们正在寻找完全匹配，因此我们键入0。

所以这里发生的情况是，函数转到范围B1:C5中的表，在B列中查找值646，转到该表中的第二列（C列），并从中返回相应的值–72。

请注意，如果我们试图返回Name列而不是Age列，除非我们更改了列的位置，否则我们将无法使用VLOOKUP返回。这是因为A列在B列之前，B列必须是table_array中的第一列，因为它包含ID号。在这种情况下，建议使用INDEX MATCH，或者使用强大的XLOOKUP函数。

VLOOKUP 返回结果中为什么会有#N/A?

为什么我们收到Dani的#N/A错误？

#N/A错误表示找不到您要查找的值。

但是等等，第14排有一个Dani！他为Netflix工作！

我们仍然没有找到任何东西的原因是因为“Dani”前面有一个空格。

Excel将空白视为一个字符，因此基本上这两个字符是不同的：因此我们需要删除空格后（或者用TRIM函数去移除不必要的空格）再用VLOOKUP函数。

XLOOKUP函数说明

XLOOKUP函数用于查找范围中的值，并返回相应的值。

XLOOKUP是一个超级功能，它结合了各种流行Excel函数的最佳功能，如VLOOKUP、INDEX MATCH、IFERROR/IFNA、HLOOKUP等！

以下是使用XLOOKUP成为Excel中最佳函数之一的一些独有功能：

实例

语法

=XLOOKUP（查找值、查找数组、返回数组、[if_not_fond]、[match_mode]、[search_mode]）

lookup_value–我们要寻找的值是什么？

lookup_array–我们应该在哪里查找值？

return_array–从何处返回相应的值？这可能是一列、一行甚至一个范围——这意味着我们可以为每个匹配返回多个单元格。请注意，与VLOOKUP不同，与INDEX MATCH类似，return_array可以位于查找数组之前

[if_not_fond]–可选参数–如果找不到值，返回什么。我们可以键入值、文本（例如：“value not found！”），甚至可以使用单元格引用/函数。

[match_mode]–可选参数–XLOOKUP具有多种匹配模式：

0–精确匹配–返回精确匹配–这是默认选项。

-1–如果未找到值，则返回下一个最小项目。

1–如果未找到值，则返回下一个最大项目。

2–通配符匹配，使用？，*和~符号。除非我们使用此参数，否则我们无法执行通配符搜索（与VLOOKUP相反，在VLOOKU中，我们可以在不使用任何特殊参数的情况下执行通配符搜索 )

[search_mode]–可选参数–应使用哪种搜索模式：

1–从第一个到最后一个搜索阵列（例如，在列中–从上到下搜索）–这是默认选项。

-1–从阵列中的最后一项搜索到第一项（例如，在列中从下到上搜索）。

此外，我们可以使用二进制搜索算法执行二进制搜索：

2–按升序进行二进制搜索–确保lookup_array按升序排序，以获得正确的结果。

-2–按降序进行二进制搜索–确保lookup_array按降序排序，以获得正确的结果。

实例练习

练习#1–XLOOKUP的基本用法

练习#2–返回最后一个匹配值

使用search_mode参数从数据中返回最后一个匹配值！

以下是一些可能的解决方案-

所有解决方案都依赖于在搜索模式中使用-1（最后一个参数）：

方法#1：

黄色=XLOOKUP（B14，A6:A12，C6:C12，，，-1）

这里我们将“未找到”和“匹配模式”保持为空。

方法#2：

黄色=XLOOKUP（B14，A6:A12，C6:C12，“未找到”，，-1）

这里我们在if_not_found参数中使用“未找到”消息

方法#3：

黄色=XLOOKUP（B14，A6:A12，C6:C12，，0，-1）

使用匹配模式0表示使用完全匹配（默认行为）

练习#3–返回所有匹配的内容

现在，让我们尝试使用XLOOKUP&Excel的动态数组功能返回所有匹配的内容！

公式：

=XLOOKUP（B10，C4:C8，A4:E8）

在此示例中，我们要求在单元格B10中查找车牌，

然后我们要求查找范围C4:C8中的值。

最后，我们可以通过高亮范围A4:E8，因此如果匹配，Excel将返回所有行！

使用Excel动态数组，这允许返回多个值！

#练习4–返回“找不到值”

现在，让我们尝试返回一条自定义消息，以防找不到请求的值。这取代了使用IFERROR/IFNA公式的需要。

#练习5–水平查找（Horizontal Lookup）

我们可以使用XLOOKUP执行水平查找，就像在HLOOKUP函数中一样：

我们可以轻松地进行水平查找（类似于HLOOKUP）。

我们使用与垂直查找相同的公式。

我们只需要使用水平范围选择而不是垂直范围。

练习#6–与XLOOKUP大致匹配

如果找不到特定的数字或日期，使用近似匹配如何？VLOOKUP允许我们对下一个最小值使用近似匹配，XLOOKUP则允许我们找到下一个最大值和下一个最小值！

解决方案：

要使用近似匹配，我们可以在match_mode参数中键入-1或1。

-1将返回下一个最小匹配（1月3日）

1将返回下一场最大的比赛（1月5日）。

解释

INDEX MATCH是一个查找函数，许多人认为它优于VLOOKUP函数

INDEX MATCH虽然被认为是“一个”函数，但实际上是两个Excel函数的组合——INDEX和MATCH

实例

为什么要使用INDEX MATCH而不是VLOOKUP？

在某些情况下，VLOOKUP确实存在一些问题：

当表数组中要返回的值在查找列之前时，您是否尝试过使用VLOOKUP？如果您确实尝试过，那么您就会知道，在不操纵数据的情况下使用VLOOKUP是不可能的：

当删除表数组中的一列或添加新列时，VLOOKUP的响应不好。这可能会导致错误的结果，甚至#REF错误！

您是否尝试过VLOOKUP一个包含50列的表数组？在处理这种大小的数组时，很容易迷路。

INDEX MATCH的性能被认为优于VLOOKUP的性能，尤其是在大型表中。

您是否总是更喜欢使用INDEX MATCH而不是VLOOKUP？

好吧，答案是否定的。尽管我们很喜欢INDEX MATCH，但它的逻辑有时是反直觉的，一些用户很难理解这些功能是如何协同工作的。

对于大多数用户来说，VLOOKUP工作得很好，而且更容易理解。然而，当面对复杂的大型表时，INDEX MATCH绝对是一个更好的选择。

语法

因此，在您确信INDEX MATCH绝对是Excel中最好的函数之后，下面是我们如何使用它：

=INDEX（数组，MATCH（查找值，查找数组，[MATCH_type]）

让我们了解一下功能组合背后的想法：

首先，我们使用MATCH函数查找列（lookup_array）中的值（lookup_value）。函数返回匹配值的位置。我们在[match_type]中使用0以确保获得完全匹配！

然后，我们使用从MATCH函数接收的位置，并在INDEX函数中使用该位置，从结果列（数组）返回相应的值。

下面是INDEX MATCH工作原理的很好的例子：

解释

INDEX函数用于从一系列单元格中检索值。例如，返回范围A1:A8中的第6项。

它最常用作INDEX MATCH函数组合的一部分，这是深受喜爱但有点愚蠢的VLOOKUP函数的替代方案

语法

=INDEX（array，row_num，[column_num]）

array–这是我们想要检索项目的范围。范围通常是一列，但也可以是一行，甚至是多行和多列的范围。

row_num–这是我们将从中检索值的相对行号。

[column_num]–这是我们将从中检索值的相对列编号。这是一个可选的参数，这意味着如果我们在单个列范围（如A1:A7）上使用INDEX函数，则根本不需要使用此参数。

解释

MATCH函数是一个函数，当我们想找到某个值在一行或一列中的位置时，使用它。位置用相对数表示。

例如，在{“A”、“B”、“C”}的数组中，“B”的位置是2。

MATCH通常用作INDEX MATCH函数组合的一部分。

语法

=MATCH（lookup_value，lookup_array，[MATCH_type]）

lookup_value–我们正在寻找的值

lookup_array–要查找值的范围–可以是行或列（例如：A1:A7或B3-E3）

[match_type]–此参数的选项为：-1、0或1。我们将在99%的时间内使用0的参数，因为这意味着我们需要精确匹配-1和1将导致函数查找近似匹配，并且在查找近似数值匹配时非常有用。除非您知道自己在做什么，否则不要在此处使用–1/1值。

请注意，尽管[match_type]被视为可选，但我们强烈建议使用此参数，并在使用match时键入0，因为此参数为空时的默认值为1，这可能会导致match函数无法正常工作。

解释

如果出现Excel错误，IFERROR Excel函数将返回自定义错误消息。

Excel错误可能是以下之一：

#N/A–未找到值

#DIV/0–除以零

#REF–单元格/范围参考错误。示例：公式引用了删除的行。

#NAME–无效名称错误，通常发生在公式名称拼写错误时。

#VALUE–值错误（当公式使用错误时可能发生）

对于普通用户来说，这些错误消息没有任何意义，因此IFERROR可以通过添加自定义文本来解释错误，帮助用户了解问题的原因。

当与VLOOKUP结合使用时，IFERROR非常受欢迎

语法

=IFERROR（value，value_if_error）

value–我们要检查它是否错误的值。

value_if_error–如果值错误，则返回文本/单元格。

下面是一个VLOOKUP与IFERROR组合的示例，在出现错误时返回“Value not found”：

=IFERROR（vlookup（a1，b1:c4，2,0），“未找到值”）