2.4.1　“大海捞针”——VLOOKUP

有这样一个任务：从公司总通讯录中匹配出需要拜访客户的电话信息。

设想一下，如果手动查找，应该怎么做？如图2-70所示，我们会经过四个过程：①明确找谁的电话→②去哪里找这个人名→③找到人名之后查看他的电话→④把找到的电话记录到指定位置。

用VLOOKUP函数查找匹配的思路和我们手动查找的思路是一样的，只不过要比后者快很多，如果我们能充分理解这一过程，那么学习VLOOKUP函数会轻松许多。

1. 函数语法详解

VLOOKUP函数共有4个参数，其意义如图2-71所示，前三个参数是必需的，最后一个参数可以省略。详细的函数语法图解如图2-72所示，接下来对每一个参数进行详细讲解。

参数1：查找目标（lookup_value），它就是为了告诉Excel，要把谁找出来。

注意：①查找目标不要和返回值搞混了，就像查字典，查找目标是你要查的单词，返回值是查出来的这个单词的意思。②查找目标与查找范围的第一列的格式设置必须一致。

参数2：查找范围（table_array），指定了查找目标，如果没有告诉Excel从哪里查找，Excel就会罢工。这个参数就是要告诉Excel在哪个范围中进行查找。具体要怎么指定区域呢？这里也是极易出错的地方，给定的第二个参数查找范围要符合以下条件才不会出错：

• 查找目标一定要在该区域的第一列。换句话说，一定要把包含查找目标的列作为框选区域的第一列，比如图2-72中的黄色区域。

• 该区域中一定要包含返回值所在的列，比如，要返回姓名对应的电话，则需要把“电话”列包含到查找范围中。

参数3：返回值的列数（col_index_num），它是想要返回的值在查找范围中所在的列数。

注意：列数不是工作表中的列数，而是查找区域的第几列。

参数4：精确或模糊查找（range_lookup），最后一个参数是决定函数精确和模糊查找的关键。精确即查找到完全一样的数据才算找到，用0或FALSE表示；模糊即包含的意思，用1或TRUE表示。

2. 实例应用

案例1：使用精确查找查询匹配信息。

最常见的情况是精确查找，即只有当查找区域中存在与查找值完全相同的数据，函数才能正确返回查询结果，如图2-73所示，设置VLOOKUP用以返回“刹车盘”的价格，即$85.73。

①参数1：D13是lookup_value，即要查找的值，在本例中指的是“部件价格”。

②参数2：是B2到E11（在表中以黄色突出显示）的区域，即查找目标所在的区域。

③参数3：设置的是3，就是在参数2中包含返回值的列号。在本例中，部件价格在查询区域的第三列。

④参数4：填写FALSE或者0都是使用精确查找，因此返回值将是精确匹配项。

案例2：动态返回不同列的值。

如果需要同时查找人员的性别和成绩，即一次要返回多列信息，利用VLOOKUP函数能够实现吗？

VLOOKUP函数的第三个参数是查找返回值所在的列数，如果需要查找返回多列，这个列数值需要一个个地更改，比如返回第2列的，参数设置为2，如果需要返回第3列的，就需要把值改为3……

列数不多的情况当然可以手动修改，如果是几十列、几百列呢？能不能让第3个参数随着函数的位置不同，自动变化？即向后复制时自动变为2、3、4、5……这是解决问题的关键。

我们需要引入一个新的函数：COLUMN函数，它可以返回指定单元格的列数，比如：

=COLUMN（A1）返回值1（A1所在的列为第一列）

=COLUMN（B3）返回值2（B3所在的列为第二列）

因此，可以使用COLUMN函数将VLOOKUP函数的第三个参数设置为动态，如图2-74所示，在G3单元格输入公式=VLOOKUP($F3,$B$2:$D$13,COLUMN(B3),0)，然后分别向右、向下进行复制填充。

①参数1：使用混合引用$F3，其中，列前边有$符号，意味着列是绝对引用，行是相对引用。这样就能实现在向右复制时，确保查找的数据一直在F列；而向下复制时，又可实现行递增变化（$F3→$F4→$F5），从而实现查找不同的数据。

②COLUMN(B3)：第三个参数起始值需要设定为2（因为性别在查找区域中处于第二列），向右复制时需要递增。这里使用CLOUMN函数生成了一个变量，使问题迎刃而解。

这样，公式=VLOOKUP($F3,$B$2:$D$13,COLUMN(B3),0)向右复制时，参数3会依次由初始值2变为3、4……，从而实现返回不同列的值。

案例3：使用模糊查找评定成绩等级。

VLOOKUP函数的最后一个参数设置为0（FALSE），是精确查找，精确查找比较容易理解，实际工作中的用处也比较常见；如果设置为1（True），是模糊查找。如何应用呢？首先我们需要了解VLOOKUP函数模糊查找的两个重要规则。

• 规则一：查找区域（即VLOOKUP函数的参数2）的第一列一定要从小到大排序（数字是从小到大排序，字符按照首字母升序排序）。

• 规则二：模糊查找，给定一个无法精确匹配的数值，它会找到和它最接近但比它小的那个值。

这两个规则使VLOOKUP函数在某些问题中可以替代IF函数解决条件判断问题，而且当需要判断的层级较多时，使用VLOOKUP比IF更简洁，比如使用VLOOKUP函数来实现成绩等级的判定，如图2-75示，根据右侧的等级判定标准，求出左侧每一个成绩的等级。

因成绩判定标准是对一个成绩区间起效的。也就是说，只要是在某个区间内的成绩，它们属于同一个等级，这就是典型的模糊匹配，但是VLOOKUP函数无法识别成绩区间。因此，需要对查找区域进行变形。

Step1：添加辅助列“判定基准”，如图2-76所示的F列。

判定基准是每个成绩区间中的最低分。为什么要这样设置判断基准呢？因为根据VLOOKUP函数模糊查找的第二个规则“如果无法精确匹配，VLOOKUP函数会匹配一个比查找目标小，且最接近它的数值”，这样设置之后，判定基准比“落在对应判定区间中的所有成绩”小，且最接近这个成绩，因此符合模糊查找的规则二。

Step2：由于“判定基准”和“等级”两列组成的区域将作为VLOOKUP函数的第二个参数，即查找区域。根据模糊查找规则一，需要对查找区域第一列从小到大进行排序，使其确保是升序排序。

Step3：正确设置函数的各个参数，在C2单元格中输入公式=VLOOKUP(B2, $F$2:$G$6,2,1)，然后向下复制到所有的计算区域，如图2-76所示。

实现原理说明：例如，在图2-76中，对于星爷的成绩94，在查找区域（$F$2:$G$6）的首列中并不存在，因为是模糊查找，因此将匹配比94小但最接近它的数值，它就是判定基准中的90。因此，大于90的成绩都会被判定为A等级。