14.2 常用查找与引用函数(1)VLOOKUP

常用查找与引用函数是指日常数据处理与编辑中被广泛使用的查找与引用函数，这些函数能实现定位数据位置、返回特定查找值对应数据的功能，也经常与其他各类函数嵌套用。此类函数主要包括MATCH、INDEX、VLOOKUP、HLOOKUP、LOOKUP、OFFSET、INDIRECT和CHOOSE函数等。

一、 使用VLOOKUP函数从左到右查询数据

VLOOKUP函数是使用频率非常高的查询与引用函数之一,函数名称中的“V”表示“垂直的”。VLOOKUP函数可以实现返回查找值所在单元格区域或数组中对应的其他字段数据的功能。函数语法如下。

VLOOKUP (lookup_value,table_array,col_index_num, [range_lookup])

第一参数lookup_value是要在单元格区域或数组的第一列中查找的值。 如果查询区域中多个符合条件的是查找值，VLOOKUP函数只能返回第一个查找值对应的结果。如果没有符合条件的查找值，VLOOKUP函数将返回错误值#N/A。

第二参数table_array是需要查询的单元格区域或数组。第一参数的查找值应该位于第二参数单元格区域或数组的首列。

例如，如果第一参数查找值为员工号，第二参数单元格区域或数组首列必须包含员工号信息，否则公式将返回错误值。

第三参数col_index_num 用于指定返回查询区域中第几列的值，该参数如果超出待查询区域的总列数，VLOOKUP函数将返回错误值#REF！，如果小于1返回错误值#VALUE！

第四参数range_lookup决定函数的查找方式，如果为0或FASLE，则为精确匹配方式，而且支持无序查找；如果为TRUE或被省略，则使用近似匹配方式，同时要求查询区域的列按升序排序。

例 VLOOKUP函数返回错误值示例

下图所示为VLOOKUP函数返回错误值的常见示例。

针对第一参数查找值在第二参数首列无精确匹配值的情况，若VLOOKUP函数各参数使用均正确，可以用IFERROR等函数或条件格式屏蔽错误值。

针对第三参数大于第二参数总列数和小于1的情况，根据实际情况修改第三参数。

针对第一参数查找值为数值格式，第二参数首列中为文本型数值的情况，可以用分列、选择性粘贴等方式将第二参数首列的文本型数值转换为数值格式，或者将第一参强制转换为文本，如VLOOKUP(D5&”“，A：B，2，)。

针对第一参数查找值为文本型数值(包括文本型存储的日期)，第二参数首列中为数值的情况，可以用分列等方式将第二参数首列的数值转换为文本型数值，或者将第一参数强制转换为数值，如VLOOKUP(0+D6,A:B,2,)

针对查找值或查找区域首列含有不可见字符的情况，如源数据为系统导出数据或网上复制的数据，可以用TRIM函数、CLEAN函数、分列功能和查找与替换功能将不可见字符清除或替换为空。

利用相对引用和绝对引用技巧，VLOOKUP函数可以一次性返回多列结果，而不用针对列返回值分别输入公式。

例 查询并返回多列结果

第三参数“COLUMN（B1）”返回2，VLOOKUP函数返回查询区城第2列员工“部门”字段信息，向右复制时变为“COLUMN（C1）” 返回3，返回查询区城第3列员工“职务”字段信息。

例 通配符查找

下图中A列为姓名，要求查找E列包含姓的关键字并返回对应相关信息。

在F2单元格中使用以下公式，并向下复制到G3,H3单元格。

=VLOOKUP($E2&"*",$A:$C,COLUMN(A1),)

$E2&"*"：表示以E2单元格开头，后接任意字符的内容。

VLOOKUP函数的查询值要求必须位于查询区域中的首列，因此，默认情况下VLOOKUP函数只能实现从左到右的查询。如果被查找值不在查询区域的首列时，可以先将目标数据进行特殊的转换，再使用VLOOKUP函数来实现此类查询。

例 逆向查询

如下图所示,A~D列为员工信息表，员工姓名在第2列。要求根据F列的职务，在G列返回员工的姓名。

=VLOOKUP(F2,CHOOSE({1,2},D2:D10,B2:B10),2,0)

CHOOSE函数的作用是根据第一参数指定的数值，在参数列表中返回对应位置的引用或内容。本例中第一参数使用常量数组{1,2}，目的是将查询值所在的D列和返回值所在的B列整合成一个新的两列多行的内存数组。

生成的内存数组符合VLOOKUP函数的查询值必须处于查询区域首列的要求。VLOOKUP函数以职务作为查询条件，在内存数组中查询并返回对应的姓名信息，从而实现了逆向查询的目的。

实际应用中，借助简单的辅助列VLOOKUP函数可以返回多个符合条件的记录。

例 返回多个符合条件的记录

下图中的C~D列为员工信息表，需要根据F2单元格指定的职务，在G列查询并返回符合该职务的所有员工姓名。

首先，把A列作为辅助列。然后，在A2单元格中输入以下公式，向下复制到A10单元格，A列每个数字第一次出现时对应的C列的值即为要查询的职务。

=COUNTIF($D$2:D2,$F$2)

公式从上到下依次判断F2单元格的职务在D列出现的次数。

B列的写法是另一种方法判断F2单元格的职务在D列出现的次数。

=(D2=$F$2)+B1，向下复制到B10单元格，这个公式挺好理解的，这里就不做解释了。

辅助列设置完毕后，在G2单元格中输入以下公式，并向下复制填充至单元格返回空文本。

=IFERROR(VLOOKUP(ROW(B1),B:C,2,0),"")

公式向下复制时，“ROW(B1)”部分依次变为ROW(B2)、ROW(B3)···即1~n的递增序列。VLOOKUP函数使用1~n的递增序列作为查询值，使用B:C列作为查询区域，以精确匹配的方式即可返回与之相对应的D列的所有姓名。

当ROW函数返回结果大于B列中的最大的数字时，VLOOKUP函数会因为查询不到结而返回错误值#N/A。IFERROR函数用于屏蔽VLOOKUP返回的错误值，使之返回空文本。

例 双条件查询

上图所示的是某公司员工信息表，不同部门人员有同名的情况。要求根据姓名和部门两个条件在H2单元格中返回对应人员的职务。

在H2单元格中输入以下数组公式，按 <Ctrl+shift+Enter> 组合键

{=VLOOKUP(F2&G2,IF({1,0},B2:B10&C2:C10,D2:D10),2,)}

" F2&G2”部分表示使用连接符“&”将姓名和部门合并成字符串，以此作为VLOOKUP函数的查询条件。

IF({1,0},B2:B10&C2:C10部分表示先将B列和C列进行连接，再使用IF({1,0}的方式，构造出姓名部门在前、职务在后的两列多行的内存数组

VLOOKUP函数在IF函数构造出的内存数组首列中查询“肖*仓储部”的位置，返回对应的职务信息，结果为“主管”。

当然你也可在A列构造一个辅助列，将姓名和部门连起来组成一个新字段，这样VLOOKUP函数利用姓名部门这一列时就应用了两个条件，利用这种方法可以构造多个条件的查找。

例 判断考核等级

下图所示的是员工考核成绩表的部分内容，F3:G6单元格区域是考核等级对照表首列已按成绩升序排序，要求在D列根据考核成绩查询出对应的等级。

在D2单元格中输入以下公式，并向下复制到D11单元格。

=VLOOKUP(C2,F$3:G$6,2)

VLOOKUP函数第四参数被省略，在近似匹配模式下返回查询值的精确匹配值或似匹配值。如果找不到精确匹配值，则返回小于查询值的最大值。

C2单元格的成绩79在对照表中未列出，因此Excel在F列中查找小于79的最大值60进行匹配，并返回G列对应的等级“合格”。

使用近似匹配时，查询区城的首列必须按升序排序，否期无法得到正确的结果。