办公软件 > Excel > 正文

Excel VLookUp函数的使用方法,含逆向查找、一对多查找和近似匹配实例

亮术网 2019-05-11 本网原创

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所示:

Excel VLookUp函数的使用方法,含逆向查找、一对多查找和近似匹配实例

图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所示:

Excel VLookUp函数省略参数 Range_LookUp 的实例

图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所示:

Excel VLookUp函数把参数 Range_LookUp 设置为 False 返回 #N/A 的实例

图3

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

 

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

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

Excel VLookUp函数公式缺少引号返回名称错误值 #NAME? 实例

图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所示:

Excel VLookUp函数在查找值中使用通配符问号(?)

图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所示:

Excel VLookUp函数在查找值中使用通配符星号(*)

图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所示:

Excel VLookUp函数参数查找区域 Table_Array 中有前后空格的实例

图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所示:

Excel VLookUp函数近似匹配实例

图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所示:

Excel VLookUp函数逆向查找实例

图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所示:

Excel VLookUp函数一对多查找实例

图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函数组合查找等,这些内容将在后续文章中介绍。