Excel VLOOKUP函数完全教程：从基础到高级实战
文章目录
[Excel VLOOKUP函数完全教程：从基础到高级实战](#Excel VLOOKUP函数完全教程：从基础到高级实战)
一、VLOOKUP基础：语法与核心参数
[1. 函数语法](#1. 函数语法)
[2. 参数详解](#2. 参数详解)
[3. 关键规则（必看）](#3. 关键规则（必看）)
二、基础VLOOKUP实战：9个高频场景
场景1：精确匹配（最常用）
场景2：近似匹配（用于范围判断）
场景3：区分大小写的查找
场景4：从右到左查找（反向查找）
场景5：查找第2个/第n个匹配值
场景6：通配符部分匹配
场景7：跨工作表查找
场景8：跨工作簿查找
场景9：替换0或#N/A错误值
三、高级VLOOKUP技巧：9个进阶场景
场景1：双向查找（行+列同时匹配）
场景2：多条件匹配（两个及以上条件）
场景3：返回多个匹配值（垂直/水平）
场景4：返回匹配值的整行数据
场景5：嵌套VLOOKUP（跨多表查找）
场景6：检查值是否存在于另一列
场景7：VLOOKUP并求和（行/列）
场景8：根据关键列合并两个表
场景9：跨多个工作表匹配
四、VLOOKUP匹配值保留格式：4个实用技巧
技巧1：保留单元格颜色和字体格式
技巧2：保留日期格式
技巧3：保留单元格批注
技巧4：处理"数字存储为文本"的匹配
五、常见问题与注意事项
VLOOKUP（Vertical Lookup，垂直查找）是Excel中最实用的函数之一，可快速在数据区域的左侧列查找目标值，并返回同一行中指定列的结果。本教程基于实战场景，从函数基础到高级技巧，覆盖初学者到进阶用户的核心需求，包含20+实用示例，帮你彻底掌握VLOOKUP的用法。
一、VLOOKUP基础：语法与核心参数
在学习示例前，必须先理解VLOOKUP的语法结构------这是正确使用函数的前提。
1. 函数语法
VLOOKUP函数的完整公式格式为：
excel
复制代码
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
2. 参数详解
四个参数中，前三个为必需参数 ，最后一个为可选参数，具体作用如下表：
参数名称
作用说明
注意事项
lookup_value
要查找的目标值（可是数字、文本、日期或单元格引用）
必须位于table_array的第一列，否则无法查找
table_array
查找范围（包含"查找列"和"结果列"的数据区域）
建议用绝对引用（如$A$2:$D$7），避免复制公式时范围偏移
col_index_num
结果列在table_array中的"列号"（从左数第1列记为1）
列号不能超过table_array的总列数，否则返回#VALUE!错误
[range_lookup]
逻辑值，控制"精确匹配"或"近似匹配"（默认省略时为近似匹配）
精确匹配填FALSE（或0），近似匹配填TRUE（或1），初学者优先用FALSE
3. 关键规则（必看）
VLOOKUP有3个核心限制，使用前必须牢记：
只能从左到右查找：目标值必须在查找范围的第一列，无法直接从右列查找左列数据（需高级技巧解决）；
不区分大小写：默认将"Apple"和"apple"视为同一值，区分大小写需额外处理；
仅返回第一个匹配值：若存在多个相同目标值，仅返回最上方的第一个结果。
二、基础VLOOKUP实战：9个高频场景
基础示例覆盖日常办公中80%的VLOOKUP使用场景，每个示例均包含"场景描述+数据表格+公式+解析"。
场景1：精确匹配（最常用）
需求 ：根据学生ID（如C1005）查找对应的数学成绩，要求结果完全匹配。
数据表格：
行号
A（ID）
B（姓名）
C（数学）
D（化学）
E（目标ID）
F（结果）
2
A1001
Emily
49
70
C1005
?
3
A1002
James
78
58
4
B1003
Nicol
100
96
5
C1004
Hedy
92
98
6
C1005
Mario
61
79
公式（输入到F2单元格）：
excel
复制代码
=VLOOKUP(E2,$A$2:$D$7,3,FALSE)
解析：
E2：目标值（C1005）；
$A$2:$D$7：查找范围（ID列到化学列，绝对引用避免偏移）；
3：结果列是"数学"（在范围中从左数第3列）；
FALSE：精确匹配，确保只有ID完全一致时才返回结果。
结果 ：F2单元格返回61（Mario的数学成绩）；若目标ID不存在，返回#N/A错误。
场景2：近似匹配（用于范围判断）
需求 ：根据订单量（如60、268）查找对应的折扣，无精确订单量时返回"小于目标值的最大折扣"。
数据表格（需按"订单量"升序排序！）：
行号
A（订单量）
B（折扣）
C（目标订单量）
D（结果折扣）
2
1
5%
60
?
3
20
10%
268
?
4
50
15%
685
?
5
100
22%
6
200
30%
7
300
45%
公式（输入到D2单元格）：
excel
复制代码
=VLOOKUP(C2,$A$2:$B$9,2,TRUE)
解析：
TRUE：近似匹配，需满足"查找范围第一列（订单量）升序排序"；
示例：目标订单量60，无精确匹配，返回小于60的最大订单量（50）对应的折扣15%。
注意：近似匹配若不排序，结果会混乱甚至错误！
场景3：区分大小写的查找
需求 ：ID列包含大小写（如A1001、a1001），需严格区分大小写查找对应的数学成绩（默认不区分）。
数据表格：
行号
A（ID）
B（姓名）
C（数学）
D（目标ID）
E（结果）
2
A1001
Emily
49
a1001
?
3
b1003
James
78
B1003
?
4
C1004
Nicol
100
d1006
?
5
a1001
Hedy
87
6
B1003
Mario
61
公式 （二选一，需按Ctrl+Shift+Enter确认数组公式）：
excel
复制代码
# 公式1：INDEX+MATCH+EXACT
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(D2,$A$2:$A$10),0))
# 公式2：LOOKUP+EXACT（无需数组确认，Excel 365适用）
=LOOKUP(2,1/EXACT(D2,$A$2:$A$10),$C$2:$C$10)
解析 ：EXACT(D2,$A$2:$A$10)严格对比大小写，返回TRUE/FALSE数组，再通过MATCH找到TRUE的位置，最终用INDEX返回成绩。
场景4：从右到左查找（反向查找）
需求 ：根据"年龄"（右列）查找对应的"姓名"（左列），VLOOKUP默认无法实现，需结合INDEX+MATCH。
数据表格：
行号
A（ID）
B（姓名）
C（性别）
D（年龄）
E（目标年龄）
F（结果姓名）
2
AA-1001
Emily
F
29
21
?
3
BB-1002
James
M
35
38
?
4
CC-1003
Nicol
F
21
5
DD-1004
Hedy
F
56
6
EE-1005
Anny
F
38
公式（输入到F2单元格）：
excel
复制代码
=INDEX($B$2:$B$6,MATCH(E2,$D$2:$D$6,0))
解析：
MATCH(E2,$D$2:$D$6,0)：先找到"目标年龄"在D列的行号（如21在第4行）；
INDEX($B$2:$B$6,行号)：根据行号从B列（姓名）返回结果（Nicol）。
场景5：查找第2个/第n个匹配值
需求 ：同一姓名（如Emily）购买了多门培训课程，需查找她的第2门课程。
数据表格：
行号
A（姓名）
B（课程）
C（目标姓名）
D（目标次数）
E（结果）
2
Emily
Excel
Emily
2
?
3
James
Excel
4
Emily
Powerpoint
5
Hedy
Word
6
Emily
Outlook
公式 （输入到E2，按Ctrl+Shift+Enter确认）：
excel
复制代码
=INDEX($B$2:$B$14,SMALL(IF(C2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),D2))
解析：
IF(C2=$A$2:$A$14,...)：筛选出Emily对应的所有行号，生成行号数组；
SMALL(...,D2)：取数组中第2小的行号（即第2个匹配的位置）；
INDEX：根据行号返回对应课程（Powerpoint）。
修改 ：若要找第3个匹配值，将D2改为3即可。
场景6：通配符部分匹配
需求 ：根据"部分姓名"（如Dave）查找对应的分数，无需输入全名（如Dave Bell）。
数据表格：
行号
A（全名）
B（ID）
C（分数）
D（目标部分名）
E（结果）
2
Jon Bader
A-1001
49
Dave
?
3
Angel Banks
A-1002
78
Carol
?
4
Carol Aviles
B-1003
100
Erica
?
5
Dave Bell
D-1006
85
6
Erica Stone
E-1008
43
公式（输入到E2）：
excel
复制代码
=VLOOKUP(D2&"*",$A$2:$C$11,3,FALSE)
解析：
D2&"*"：通配符*表示"任意字符"，Dave*即"以Dave开头的所有字符串"；
其他通配符用法：
以目标值结尾："*"&D2（如*Banks匹配Angel Banks）；
包含目标值："*"&D2&"*"（如*Bell*匹配Dave Bell）。
场景7：跨工作表查找
需求 ：在"Lookup sheet"中，根据姓名查找"Data sheet"中的分数（两个工作表位于同一工作簿）。
工作表结构：
Data sheet：包含A（姓名）、B（ID）、C（分数）；
Lookup sheet：A（目标姓名）、B（结果分数）。
公式（在Lookup sheet的B2单元格输入）：
excel
复制代码
=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)
解析：
'Data sheet'!$A$2:$C$15：跨工作表引用格式，工作表名带空格需加单引号；
若工作表名无空格（如Datasheet），可简化为Datasheet!$A$2:$C$15。
场景8：跨工作簿查找
需求 ：在"Order price.xlsx"中，根据产品（如Apple）查找"Product list.xlsx"中的成本。
公式（在Order price.xlsx的D2单元格输入）：
excel
复制代码
=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)
解析：
'[Product list.xlsx]Sheet1'!$A$2:$B$6：跨工作簿引用格式，工作簿名用[]包裹；
若"Product list.xlsx"已关闭，公式会自动补充文件路径（如C:\Users\Desktop\[Product list.xlsx]Sheet1'!$A$2:$B$6）。
场景9：替换0或#N/A错误值
需求 ：VLOOKUP返回0（匹配到空白单元格）或#N/A（无匹配）时，显示空白或自定义文本（如"无数据"）。
公式（二选一）：
excel
复制代码
# 1. 匹配到空白返回空白，无匹配返回"无数据"
=IF(ISNA(VLOOKUP(C2,$A$2:$B$10,2,FALSE)),"无数据",IF(VLOOKUP(C2,$A$2:$B$10,2,FALSE)="","",VLOOKUP(C2,$A$2:$B$10,2,FALSE)))
# 2. Excel 2019+简化版（IFERROR+IF）
=IFERROR(IF(VLOOKUP(C2,$A$2:$B$10,2,FALSE)="","",VLOOKUP(C2,$A$2:$B$10,2,FALSE)),"无数据")
解析：
ISNA(...)：判断是否为#N/A错误；
IF(...)=""：判断匹配结果是否为空白，避免返回0。
三、高级VLOOKUP技巧：9个进阶场景
高级技巧解决复杂需求，需结合其他函数（如MATCH、INDEX、SUMPRODUCT），是提升Excel效率的关键。
场景1：双向查找（行+列同时匹配）
需求 ：根据"产品（Peach）"和"季度（Q3）"，查找对应的销售额（需同时匹配行和列）。
数据表格：
行号
A（产品）
B（Q1）
C（Q2）
D（Q3）
E（Q4）
F（目标产品）
G（目标季度）
H（结果）
2
Apple
569
769
987
337
Peach
Q3
?
3
Orange
979
502
556
685
4
Peach
424
832
385
453
公式（输入到H2）：
excel
复制代码
=VLOOKUP(F2,$A$2:$E$7,MATCH(G2,$A$2:$E$2,0),FALSE)
解析：
MATCH(G2,$A$2:$E$2,0)：先找到"Q3"在第2行的列号（第4列）；
VLOOKUP(F2,...)：根据"Peach"找到行，再用MATCH的列号返回Q3销售额（385）。
场景2：多条件匹配（两个及以上条件）
需求 ：根据"产品（T-shirt）"和"尺寸（Medium）"，查找对应的价格（单条件VLOOKUP无法实现）。
数据表格：
行号
A（产品）
B（尺寸）
C（颜色）
D（价格）
E（目标产品）
F（目标尺寸）
G（结果）
2
Hoodie
Large
Black
49
T-shirt
Medium
?
3
T-shirt
Small
Red
20
4
T-shirt
Medium
Red
22
公式（二选一）：
excel
复制代码
# 公式1：LOOKUP（无需数组确认）
=LOOKUP(2,1/($A$2:$A$12=E1)/($B$2:$B$12=F1),$D$2:$D$12)
# 公式2：INDEX+MATCH（兼容性更强）
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=E1)*($B$2:$B$12=F1),0))
解析：
$A$2:$A$12=E1和$B$2:$B$12=F1：两个条件分别筛选产品和尺寸，返回TRUE/FALSE；
1/(...)：将TRUE转为1，FALSE转为错误值，LOOKUP会跳过错误值取最后一个1对应的价格。
场景3：返回多个匹配值（垂直/水平）
需求 ：查找"国家（US）"对应的所有姓名，垂直排列在一列中。
数据表格：
行号
A（国家）
B（城市）
C（姓名）
D（目标国家）
E（结果）
2
US
New York
Carlo
US
?
3
France
Paris
Kevin
?
4
US
Washington
Tom
?
5
US
Washington
Adam
?
公式 （输入到E2，按Ctrl+Shift+Enter，再向下复制）：
excel
复制代码
=IFERROR(INDEX($C$2:$C$14,SMALL(IF($A$2:$A$14=$D$2,ROW($A$2:$A$14)-ROW($A$2)+1),ROW()-1)),"")
解析：
ROW()-1：向下复制时，自动递增取第1、2、3...个匹配值；
IFERROR(..., "")：无更多匹配值时显示空白，避免错误。
场景4：返回匹配值的整行数据
需求 ：根据"产品（T-shirt）"，返回该产品对应的"尺寸、颜色、价格"整行信息。
数据表格：
行号
A（产品）
B（尺寸）
C（颜色）
D（价格）
E（目标产品）
F（尺寸）
G（颜色）
H（价格）
2
Hoodie
Large
Black
49
T-shirt
?
?
?
3
T-shirt
Small
Red
20
4
T-shirt
Large
Orange
25
公式（输入到F2，向右复制到H2）：
excel
复制代码
=VLOOKUP($E$2,$A$1:$D$12,COLUMN(A1),FALSE)
解析：
COLUMN(A1)：向右复制时，自动从1→2→3（对应尺寸、颜色、价格列）；
$E$2：绝对引用目标产品，确保整行查找的是同一产品。
场景5：嵌套VLOOKUP（跨多表查找）
需求 ：先根据"产品（Sweater）"在表1找"销售员（Kevin）"，再在表2找Kevin的"销售额（650）"。
数据表格：
表1（产品-销售员）
A（产品）
B（销售员）
表2（销售员-销售额）
C（销售员）
D（销售额）
结果表
E（产品）
F（销售额）
行2
Hoodie
Jener
行2
Jener
500
行2
Sweater
?
行3
T-shirt
Linda
行3
Linda
480
行3
Trousers
?
行4
Sweater
Kevin
行4
Kevin
650
公式（输入到F2）：
excel
复制代码
=VLOOKUP(VLOOKUP(E2,$A$2:$B$7,2,0),$C$2:$D$7,2,0)
解析：
内层VLOOKUP(E2,$A$2:$B$7,2,0)：根据Sweater找到销售员Kevin；
外层VLOOKUP(内层结果,$C$2:$D$7,2,0)：根据Kevin找到销售额650。
场景6：检查值是否存在于另一列
需求 ：判断"目标姓名"（如Frank）是否在"姓名列表"中，返回"Yes/No"。
数据表格：
行号
A（姓名列表）
B（目标姓名）
C（结果）
2
Jener
Frank
?
3
Linda
Emma
?
4
Kevin
Simone
?
5
Emma
Neela
?
公式（输入到C2）：
excel
复制代码
=IF(ISNA(VLOOKUP(B2,$A$2:$A$10,1,FALSE)),"No","Yes")
解析：
VLOOKUP(B2,$A$2:$A$10,1,FALSE)：在姓名列表中查找目标姓名，无匹配返回#N/A；
ISNA(...)：判断是否为#N/A，是则返回No，否则返回Yes。
场景7：VLOOKUP并求和（行/列）
需求1：求和一行中所有匹配值
根据"产品（Hoodie）"，求和其1-5月的销售额。
公式：
excel
复制代码
=SUM(VLOOKUP(H2,$A$2:$F$9,{2,3,4,5,6},FALSE))
{2,3,4,5,6}：指定求和的列（1-5月），用数组包裹；
SUM(...)：对VLOOKUP返回的多列结果求和。
需求2：求和一列中所有匹配值
根据"月份（Jan）"，求和所有产品1月的销售额。
公式：
excel
复制代码
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=H2))
$B$1:$F$1=H2：筛选出Jan列；
SUMPRODUCT(...)：对筛选后的列求和。
场景8：根据关键列合并两个表
需求 ：根据"产品"这一关键列，将表1的"姓名"和表2的"订单量"合并为一个表。
数据表格：
表1
A（产品）
B（姓名）
表2
C（产品）
D（订单量）
合并表
E（产品）
F（姓名）
G（订单量）
行2
Hoodie
Jener
行2
Jacket
240
行2
Hoodie
Jener
?
行3
T-shirt
Linda
行3
Hoodie
488
行3
T-shirt
Linda
?
公式（输入到G2）：
excel
复制代码
=INDEX($D$2:$D$8,MATCH($E2,$C$2:$C$8,0))
解析 ：MATCH($E2,$C$2:$C$8,0)找到产品在表2的行号，INDEX返回对应订单量。
场景9：跨多个工作表匹配
需求 ：在"汇总表"中，根据ID（如AA1001）查找"表1、表2、表3"中的姓名（ID分别在三个表中）。
公式 （用IFERROR嵌套多个VLOOKUP）：
excel
复制代码
=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$B$6,2,0),IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$6,2,0),VLOOKUP(A2,Sheet3!$A$2:$B$6,2,0)))
解析：先查Sheet1，无结果查Sheet2，最后查Sheet3，避免返回#N/A。
四、VLOOKUP匹配值保留格式：4个实用技巧
VLOOKUP默认只返回值，不保留原单元格格式（如颜色、日期格式、批注），需特殊处理。
技巧1：保留单元格颜色和字体格式
需通过VBA自定义函数实现，步骤如下：
右键工作表标签→查看代码，打开VBA编辑器；
在"工作表模块"粘贴以下代码（处理格式更新）：
vba
复制代码
Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long
Dim xKeys As Long
Dim xDicStr As String
On Error Resume Next
Application.ScreenUpdating = False
xKeys = UBound(xDic.Keys)
If xKeys >= 0 Then
For I = 0 To UBound(xDic.Keys)
xDicStr = xDic.Items(I)
If xDicStr <> "" Then
Range(xDic.Keys(I)).Interior.Color = Range(xDic.Items(I)).Interior.Color
Range(xDic.Keys(I)).Font = Range(xDic.Items(I)).Font
Else
Range(xDic.Keys(I)).Interior.Color = xlNone
End If
Next
Set xDic = Nothing
End If
Application.ScreenUpdating = True
End Sub
插入→模块 ，粘贴以下代码（自定义查找函数）：
vba
复制代码
Public xDic As New Dictionary
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
Dim xFindCell As Range
On Error Resume Next
Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
If xFindCell Is Nothing Then
LookupKeepFormat = ""
xDic.Add Application.Caller.Address, ""
Else
LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
End If
End Function
工具→引用，勾选"Microsoft Scripting Runtime"→确定；
返回工作表，使用自定义函数：
excel
复制代码
=LookupKeepFormat(E2,$A$1:$C$10,3)
技巧2：保留日期格式
VLOOKUP返回日期时可能显示为数字（如44190），需用TEXT函数格式化：
excel
复制代码
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")
"mm/dd/yyyy"：可改为"yyyy-mm-dd"（如2025-10-01）等格式。
技巧3：保留单元格批注
需VBA自定义函数，步骤如下：
按Alt+F11打开VBA编辑器，插入→模块，粘贴代码：
vba
复制代码
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
Application.Volatile
Dim xRet As Variant
Dim xCell As Range
xRet = Application.Match(LookVal, FTable.Columns(1), FType)
If IsError(xRet) Then
VlookupComment = "Not Found"
Else
Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
VlookupComment = xCell.Value
With Application.Caller
If Not .Comment Is Nothing Then .Comment.Delete
If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text
End With
End If
End Function
返回工作表，使用函数：
excel
复制代码
=VlookupComment(D2,$A$2:$B$9,2,FALSE)
技巧4：处理"数字存储为文本"的匹配
若查找值是文本格式（如"1001"），而目标列是数字格式（如1001），会返回#N/A，公式如下：
excel
复制代码
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))
VALUE(D2)：将文本转为数字；
TEXT(D2,0)：将数字转为文本，双向兼容。
五、常见问题与注意事项
为什么返回#N/A？
目标值在查找范围第一列不存在；
数据类型不匹配（如文本vs数字）；
近似匹配时查找范围未升序排序。
为什么返回#VALUE！？
col_index_num超过查找范围的总列数；
公式中存在无效的单元格引用（如引用了合并单元格）。
如何批量修改查找范围？
若数据新增行/列，可将查找范围定义为"表格"（选中数据→按Ctrl+T），公式会自动扩展范围。
通过本教程的基础示例和高级技巧，你可以解决95%以上的Excel数据查找需求。建议从"精确匹配""跨工作表查找"等基础场景开始练习，熟练后再尝试"多条件匹配""嵌套VLOOKUP"等进阶用法，逐步提升Excel效率。