10个例子让你的VLOOKUP函数应用从初学者变成大师(一)

VLOOKUP函数是很多Excel用户最喜欢、最常用的函数之一，所以介绍VLOOKUP函数使用技巧的文章很多。我们学习了VLOOKUP函数的语法和应用。在Excel公式和函数美之前的系列文章中，我们详细讨论了VLOOKUP函数的四个参数。

掌握VLOOKUP函数的使用是Excel必备技能之一。这里有10个例子来进一步巩固使用VLOOKUP功能的技巧。

摘要

VLOOKUP函数最擅长在列中查找匹配的数据。如果找到匹配的数据，则从找到的数据所在行右侧的指定列中获取数据。

例1:找出郭靖的数学成绩。

如图1所示，最左边一栏是学生的名字，B到E栏是不同科目的分数。

图1

现在，我需要从上面的数据中找到郭靖的数学成绩。公式是：

=VLOOKUP(“郭靖”，3:澳元10，2，0澳元)。

该公式有四个参数：

《郭靖》——该找的值。

$A$3:$E$10——单元格范围内查找。请注意，Excel会在最左侧的列中搜索要查找的值。在这个例子中，郭靖这个名字出现在A3:A10中。

23354一旦找到郭靖，它将位于该区域的第二列，并返回与郭靖同一行的值。值2指定在区域的第二列中找到结果。

03354告诉VLOOKUP函数只查找完全匹配的。

使用上面的例子来演示VLOOKUP函数是如何工作的。

首先，在区域最左边一列查找郭靖，从上到下查找，发现这个值存储在单元格A7中。

图2。

找到该值后，您将转到右侧的第二列并获取其中的值。

图3。

具有相同结构的公式可以用来获得任何学生在任何学科的分数。

例如，要找到杨康的化学分数，公式是：

=VLOOKUP(“杨康”，3:澳元10，4，0澳元)。

图4

在上面的示例中，搜索值(学生姓名)在公式中用引号括起来，或者您可以使用包含搜索值的单元格引用。使用单元格引用创建动态公式。

例如，如果将学生的姓名放在单元格中，并使用公式来查找学生的数学成绩，则当学生的姓名被修改时，搜索结果将自动更新。

图5

如果在最左边的列中找不到搜索值，则返回错误值#N/A。

示例2:双向查找。

在示例1中，列值是“硬编码”的，2用作列索引值，因此公式总是返回数学分数。

如果搜索值和列索引值都是动态的，如下图6所示，VLOOKUP函数在修改学生姓名或科目时会得到相应的分数。

图6

要创建双向查找公式，需要使列也是动态的。这样，当用户修改账户时，公式会自动获得正确的列，例如，数学是第二列，物理是第三列。

此时，需要使用MATCH函数作为列参数，公式为：

=VLOOKUP(A14，3:澳元$E$10，MATCH(B13，2:澳元$E$2，0)，0)

MATCH(B13，$A$2:$E$2，0)用作公式中的列值。MATCH函数接受一个帐户作为查找值(单元格B13)，并返回该值在A2:E2中的位置。因此，如果你寻找数学，它返回2。

示例3:使用下拉列表作为查找值。

在上面的例子中，我们手动输入数据，这既耗时又容易出错，尤其是当有很多搜索值时。

一个好方法是创建一个查找值列表，然后简单地从列表中选择。

图7

单元格B14中的公式仍然是：

=VLOOKUP(A14，3:澳元$E$10，MATCH(B13，2:澳元$E$2，0)，0)

查阅值在下拉列表中，下拉列表是使用Excel的数据有效性功能创建的。选择单元格A14，点击“数据——数据有效性”，在“数据有效性”对话框中设置为“序列”。源选自单元格区域A3:A10。以同样的方式设置单元格B13的下拉列表。

示例4:三向查找。

在示例2中，使用了包含不同学科学生分数的查找表，这是使用两个变量(学生姓名和学科名称)双向搜索学生分数的示例。

现在，假设学生一年有三次不同的考试：单元测试、期中测试和期末测试。然后，三向搜索就是从指定的考试中获取学生指定科目的分数。如下图8所示。

图8

在图8的例子中，VLOOKUP函数可以找到三个不同的表(单元测试、期中测试和期末测试)，并返回其中一个学生某一科目的分数。

单元格H4中的公式为：

=VLOOKUP(G4，CHOOSE(IF(H2=“单元测试”，1，IF(H2=“期中测试”，2，3))，$ a $3: $ e $7，$ a $11: $ e $15，$ a $1933。

使用公式CHOE函数确定要引用的表。公式中的选择函数是：

CHOOSE(IF(H2=“单元测试”，1，IF(H2=“期中测试”，2，3))，$ a $3: $ e $7，$ a $11: $ e $15，$ a $19: $ e $23。

第一个参数是IF(H2=“单元测试”，1，IF(H2=“期中测试”，2，3))，检查单元格H2中的值，返回各种待选测试表对应的值。如果是“单元测试”，则返回1，CHOOSE函数返回单元格区域$ a $ 3: $ e $ 7；如果是“期中测试”，则返回2，否则返回3，这分别对应于单元格区域$ a $1，133，360 $ e $15和$ a $1，933，360 $ e $23。

示例5:获取列表末尾的值。

您可以创建一个VLOOKUP公式来获取列表中最后一个位置的数值。

Excel中可以使用的最大正数是9.99999999999E307，这意味着VLOOKUP函数中的最大查找次数也是这个数。它几乎不涉及这么大的数字的计算，但它可以用来得到列表中的最后一个数字。

如图9所示，单元格区域A1:A14中有一组数字，你想得到列表中的最后一个数字，即1514。

图9

公式是：

=VLOOKUP(9.99999999999999E 307，1:澳元14.1澳元，TRUE)

请注意，公式使用近似匹配，列表没有排序。

以下是使用近似匹配的VLOOKUP函数的工作原理。VLOOKUP函数从上到下搜索最左边的列：

如果找到完全匹配，则返回该值。

如果找到高于搜索值的值，则返回该值所在单元格上方单元格中的值。

如果搜索值大于列表中的所有值，则返回最后一个值。

由于9.99999999999E307是Excel中可以使用的最大数字，当该数字用作搜索值时，将从列表中返回最后一个数字。

同样的原理可以用于返回列表中的最后一个文本项。如图10所示。

图10。

公式是：

=VLOOKUP(“zzz”，1:澳元8.1澳元，TRUE)

Excel会查找所有名称，由于zzz比任何文本都大，所以它会返回列表中的最后一个文本项。