Excel VLookUp函数的使用办法，含逆向搜索、一对多搜索与近似匹配案例

VLookUp函数是 Excel 中用于按行查找指定值的函数；它至少要有三个参数，第四个参数为可选项，用于确定匹配选项（精确匹配或近似匹配），如果省略，默认为近似匹配。VLookUp函数涉及的查找情况比较多，如果设置不正确可能返回多种错误值。

VLookUp函数的使用方法特别多，本篇将先介绍它的语法、基本使用方法和常用方法，其中基本使用方法介绍了 6 个实例，常用方法介绍了 3 个实例，实例包含多种返回错误值、在查找值中使用通配符问题（?）和星号（*）、逆向查找、近似匹配和一对多查找实例。

一、Excel VLookUp函数语法

1、表达式：VLOOKUP(LookUp_Value, Table_Array, Col_Index_Num, [Range_LookUp])

中文表达式：VLOOKUP(查找值, 查找区域, 返回列号, [匹配选项])

2、说明

（1）、Col_Index_Num 以指定区域左边一列为第一列，例如指定区域为 B2:D8，则以 B 列为第一列，若把 Col_Index_Num 设置为 2，则返回 C 列的值。

（2）Range_LookUp 为可选参数，它共有两个选项，一个是 True，另一个是 False，True 为近似匹配，False 为精确匹配；如果省略 Range_LookUp，默认选择 True。

（3）常见返回错误

A、如果把 Range_LookUp 设置为 True（近似匹配），需要对第一列排序，否则可能返回错误值；如果把 Range_LookUp 设置为 True，LookUp_Value 比 Table_Array 中第一列的最小值小，将返回错误值 #N/A；如果把 Range_LookUp 设置为 False，若返回错误值 #N/A，表示Table_Array 中没有查找值。

B、如果 Col_Index_Num 大于 Table_Array 的列数，将返回引用错误值 #REF!。

C、如果 Table_Array 小于 1，将返回值错误 #VALUE!。

D、如果公式缺少引号，通常返回名称错误值 #NAME?。

（4）查找数字或日期时，不能把选中区域第一列设置为文本，否则可能返回不正确的值。

（5）如果把 Range_LookUp 设置为 False（精确匹配）且 LookUp_Value 为文本，则可在 LookUp_Value 中使用通配符问号（?）和星号（*）；问号表示任意一个字符，星号表示一个或多个字符；如果要查找问号或星号，需要在它们前面加转义字符 ~，例如查找问题，应该这样表示 ~?。

（6）在 Table_Array 中不能有前后空格、单双引号和非打印字符，如果有这些字符，应该用函数或替换的方法把它们去掉，否则可能返回意外值。

二、Excel VLookUp函数的使用方法及实例

（一）省略参数 Range_LookUp 的实例

1、假如要查找“粉红衬衫”的“销量”。选中 A11 单元格，把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11，按回车，返回查找结果 892；操作过程步骤，如图1所示：

图1

2、公式说明：

A、A11 是公式 =VLOOKUP(A11,B2:E8,4,) 中的查找值，B2:E8 为查找区域，4 为返回列号（即以 B 列为第一列，返回第四列，即 E 列的值），公式省略了第四个参数 Range_LookUp，默认选择 True（近似匹配）；公式的意思是：在 B2:E8 这片单元格区域中查找 A11，在 B6 找到后，返回 B6 在 E 列的对应值 E6，即返回 892。

B、如果省略第四个参数 Range_LookUp，第三个参数后既可省略逗号（,）又可加上逗号（,）。

（二）把参数 Range_LookUp 设置为 True 需要对第一列排序和 LookUp_Value 小于第一列最小值返回 #N/A 的实例

1、双击 B11 单元格，把公式 =VLOOKUP(A11,D2:E8,2,TRUE) 复制到 B11，按回车，返回错误值 #N/A；框选 D2:D8，选择“数据”选项卡，单击“升序”图标，打开“排序提醒”窗口，选择“扩展选定区域”，单击“确定”，则每行按“价格”“升序”按序，B11 中的值随即变为 892；选中 A11 单元格，输入 36，单击一下 B11，则 B11 中的值变为 错误值 #N/A；操作过程步骤，如图2所示：

图2

2、公式说明：

A、公式 =VLOOKUP(A11,D2:E8,2,TRUE) 把参数 Range_LookUp 设置为 True，未对“价格”排序之前，返回错误值 #N/A（A11 中的 38.8 是 D 列中的最小值），对“价格”按“升序”排序后，能返回正确的查找值 892，说明把参数 Range_LookUp 设置为近似匹配时，需要对选中区域的第一列排序才能确保返回正确的查找值。

B、即使对第一列排序，如果查找值（36）小于第一列最小值（38.8），同样会返回错误值 #N/A。

（三）把参数 Range_LookUp 设置为 False 返回 #N/A 的实例

1、双击 B11 单元格，把公式 =VLOOKUP(A11,D2:E8,2,FALSE) 复制到 B11，按回车，返回错误值 #N/A；操作过程步骤，如图3所示：

图3

2、把参数 Range_LookUp 设置为 False（精确匹配）时，不管有没有对选中区域的第一列排序，如果没有找到值，都返回错误值 #N/A。

（四）公式缺少引号返回名称错误值 #NAME? 实例

1、双击 B11 单元格，把公式 =VLOOKUP(“黑色T恤”,B2:E8,4,FALSE) 复制到 B11，按回车，返回名称错误值 #NAME?；双击 B11，把公式中的全角双引号改为半角双引号，按回车，返回查找值 982；操作过程步骤，如图4所示：

图4

2、全角双引号不能用于把公式的文本括起来，如果查找文本中有全角双引号，外面还要加半角双引号，例如 =VLOOKUP("“黑色T恤”",B2:E8,4,FALSE)。

（五）在查找值 LookUp_Value 中使用通配符问号（?）或星号（*）的实例

（1）在查找值中使用通配符问号（?）

1、假如查找“产品名称”以“粉红”开头且后面只有两个字的销量和查找 ? 对应的销量。双击 B11 单元格，把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11，按回车，返回查找结果 892；双击 A11 单元格，把内容改为 ~?，单击一下 B11，B11 中的数值变为 982；操作过程步骤，如图5所示：

图5

2、说明：

A、公式 =VLOOKUP(A11,B2:E8,4,) 中 A11（粉红??）表示以“粉红”开头且后面只有两个字的文本，它正好是“粉红衬衫”，B3 和 B6 的服装名称也以“粉红”开头，但“粉红”后不止两个字，所以不满足条件。

B、把 A11 中的内容改为 ~?，表示要查找问号，恰好返回 ? 对应的销量 982。Excel 中 ~ 显示为 - 是字体原因，换种字体（如宋体）即显示正常。

（2）在查找值中使用通配符星号（*）

1、假如查找“产品名称”以“粉红”开头的、以“T恤”结尾和以“白色”开头且以“衬衫”结尾的销量。双击 B10 单元格，把公式 =VLOOKUP("粉红*",B2:E8,4,) 复制到 B10，按回车，返回查找结果 329；双击 B10，把查找值 "粉红*" 改为 "*T恤"，按回车，返回查找结果 638；双击 B10，把查找值 "*T恤" 改为 "白色*衬衫"，按回车，返回查找结果 897；操作过程步骤，如图6所示：

图6

2、说明：

公式 =VLOOKUP("粉红*",B2:E8,4,) 中 "粉红*" 表示查找以“粉红”开头的服装、*T恤" 表示查找以任意字符开头且以“T恤”结尾的服装、白色*衬衫"表示查找以“白色”开头且以“衬衫”结尾的服装。

（六）参数 Table_Array 中有前后空格的实例

1、假如要查找 A11 中的“绿色T恤”。双击 B11 单元格，把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11，按回车，返回错误值 #N/A；双击 B11，把公式改为 =VLOOKUP(A11,TRIM(B2:E8),4,)，按 Ctrl + Shift + 回车，返回查找值 528；操作过程步骤，如图7所示：

图7

2、公式说明：

A、B4 中的内容就是“绿色T恤”，但公式 =VLOOKUP(A11,B2:E8,4,) 返回错误值 #N/A，而加去掉文本前后空格的函数 Trim 后，能返回正确的值，说明 B4 中有空格。

B、=VLOOKUP(A11,TRIM(B2:E8),4,) 是数组公式，所以需要按 Ctrl + Shift + 回车，因为 B2:E8 以数组形式返回 B2:E8 中的所有值，然后用Trim函数逐个把空格去掉。

三、Excel VLookUp函数的使用方法常用实例

（一）近似匹配

1、假如要根据平均分查找对应学生的评定。选中 J9 单元格，输入 =a4，按回车，返回“黄月语”；双击 K9，把公式 =VLOOKUP(H4,J3:K6,2) 复制到 K9，按回车，返回“黄月语”的评定“良”；操作过程步骤，如图8所示：

图8

2、公式说明：

A、公式 =VLOOKUP(H4,J3:K6,2) 意思是在 J3:K6（评定表）中查找“黄月语”的平均分（H4）对应的评定，由于 H4 中的数字为 86，评定表中并没有这个值，因此只能用近似匹配（即省略参数 Range_LookUp 或把它设置为 True），从而选出 80 到 89 分段的评定。

B、提示：评定表中的分数必须按“升序”排序，否则可能返回不正确的值。

（二）逆向查找

1、假如要根据“产品名称”查找对应的“编号”。双击 B11 单元格，把公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 复制到 B11，按回车，返回“编号”NS-286；操作过程步骤，如图9所示：

图9

2、公式 =VLOOKUP(A11,CHOOSE({2,1},A2:A8,B2:B8),2) 说明：

A、{2,1} 是Choose函数数组形式的用于指定返回哪个值的索引号，A2:A8 和 B2:B8 是返回值，数组中的 2 代表 A2:A8 中的值，1 代表 B2:B8 中的值；执行时，首先从数组中取出 2，然后从 B2:B8 中取出 B2（即“白色纯棉T恤”）和从 A2:A8 中取出 A2（即“WS-580”），最后把这两个元素组成数组第一行的元素，即 "白色纯棉T恤","WS-580";；第二次从 B2:B8 中取出 B3（即“粉红长袖衬衫”）和从 A2:A8 中取出 A3（即“WS-560”），组成数组第二个元素 "粉红长袖衬衫","WS-560";；最后返回数组 {"白色纯棉T恤","WS-580";"粉红长袖衬衫","WS-560";"绿色T恤","WS-585";"粉红衬衫","WS-581";"粉红短袖衬衫","WS-561";"黑色T恤","NS-286";"白色长袖衬衫","NS-832"}，这样就把“产品名称”放到了“编号”的前面。

B、则公式变为  =VLOOKUP(A11,{"白色纯棉T恤","WS-580";"粉红长袖衬衫","WS-560";"绿色T恤","WS-585";"粉红衬衫","WS-581";"粉红短袖衬衫","WS-561";黑色T恤","NS-286";"白色长袖衬衫","NS-832"},2)，找到 A11（黑色T恤）后，返回第 2 列，恰好是 NS-286。

（三）一对多查找

1、假如要查找指定“部门”（销售部）的所有员工姓名。双击 F2 单元格，输入部门“销售部”，双击 A2，把公式 =(C2=$F$2)+A1 复制到 A2，按回车，返回 1，选中 A2，单击 A2 右下角的单元格填充柄，则 A3 到 A8 全用相应数字填充；双击 G2，把公式 =IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"") 复制到 G2，按回车，返回查找结果“林语彤”，选中 G2，把鼠标移到 G2 右下角的单元格填充柄上，按住左键，往下拖，一直拖到最后一行，则返回“销售部”的所有员工；再双击 F2，把“销售”改为“财务”，按回车，返回所有“财务部”的员工姓名；操作过程步骤，如图10所示：

图10

2、公式说明：

（1）=(C2=$F$2)+A1

A、公式 =(C2=$F$2)+A1 用于把属于同一部门的员工进行递增编号，例如属于“销售部”的三个员工被编号为 1、2、3，属于“财务部”的两个员工被编号为 2、3。

B、C2 表示对列和行都是相对引用，往下拖时，会变为 C3、C4、……；往右拖时，会变为 D2、D3、……。$F$2 表示对列和行都是绝对引用，往下或往右拖时，F2 不会变。

C、C2=$F$2 是一个条件，即 C2 如果等于 F2，则返回 True，否则返回 False，C2 和 F2 都为“销售部”，所以返回 True。则公式 =(C2=$F$2)+A1 变 =TRUE+A1，计算时 TRUE 被转为 1，A1 为空，默认返回 0，所以 =TRUE+A1 返回 1。

D、往下拖到 A3，C2 变为 C3，A1 变为 A2，所以 A3 的公式为 =(C3=$F$2)+A2；C3 的值为“行政部”，不等于 F2，所以返回 False，A2 的值为 1，因此公式变为 =False+1，计算时由于 False 被转为 0，所以 =False+1 返回 1。

（2）=IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"")

A、ROW(A1) 返回 A1 的行号 1；A1:C$8 表示 A1 到 C8 这片单元格区域，往下拖时，A1 会变为 A2、A3、……，C$8 则不会变；公式在 G2 时，A1:C$8 为 A1:C8；公式在 G3 时，A1:C$8 为 A2:C8，其它的以此类推。

B、公式在 G2 时，VLOOKUP(ROW(A1),A1:C$8,2,0) 变为 VLOOKUP(1,A1:C8,2,0)，即在 A1:C8 中查找 1，找到的是 A2 中的 1，然后返回第 2 列对应的“林语彤”；第 4 个参数 0 表示近似匹配。

C、公式在 G3 时，VLOOKUP(ROW(A1),A1:C$8,2,0) 变为 VLOOKUP(ROW(A2),A2:C$8,2,0)，即 VLOOKUP(2,A2:C8,2,0)，查找值变为 2 且 A1 被排除查找范围，在 A4 中找到 2，返回第 2 列对应的“黄子辛”；公式在 G4 时，查找值变为 3，A2 被排除查找范围，其它的以此类推。

D、IFERROR 用于返回错误值时的处理，如果 VLOOKUP(ROW(A1),A1:C$8,2,0) 返回错误值，则返回空，否则返回 VLOOKUP(ROW(A1),A1:C$8,2,0) 的返回值。

除以上VLookUp函数的使用方法外，VLookUp函数还可以用If函数组合多条件查找、查找重复项、与Match函数组合查找等，这些内容将在后续文章中介绍。

……