Excel查找方法大全与公式解析

前置说明

查找与引用是Excel中最常用的功能之一，广泛应用于数据匹配、信息查询、表格关联等场景。本文汇总了Excel中所有常用的查找方法，包括VLOOKUP、INDEX+MATCH、XLOOKUP等经典函数，并对核心公式进行逐段详细解析，帮助你深入理解原理、灵活运用。

本文示例中，查找值所在单元格为F2，查找区域为A:D列（A列为查找列，D列为返回列），实际使用时请根据数据范围修改单元格引用。

一、VLOOKUP函数（最经典的纵向查找）

VLOOKUP是Excel中最知名、最常用的查找函数，用于在表格的首列查找指定的值，并返回该行中指定列的内容。V是Vertical（纵向）的缩写。

语法结构

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

参数详解

1. lookup_value：查找值，要在表格第一列中查找的内容

2. table_array：查找区域，包含查找列和返回列的整个数据区域

3. col_index_num：返回列号，要返回的数据在查找区域中的第几列

4. range_lookup：匹配方式，可选。TRUE或1为近似匹配（默认），FALSE或0为精确匹配

基础用法（精确匹配）

=VLOOKUP(F2,A:D,4,0)

公式逐段解析

F2（查找值）

要查找的内容，即我们知道什么、用什么去查。例如要根据姓名查成绩，查找值就是姓名。

A:D（查找区域）

整个数据表格的范围。VLOOKUP有一个重要特点：它只能在区域的「第一列」中查找，然后返回同一行中其他列的值。这也是VLOOKUP最大的局限性——查找列必须在最左边。

4（返回列号）

要返回的数据在查找区域中是第几列。注意：是相对于查找区域的列号，不是工作表的绝对列号。例如查找区域是A:D，A是第1列、B是第2列、C是第3列、D是第4列。

0（精确匹配）

最后一个参数决定匹配方式：

○ 0或FALSE：精确匹配。找不到完全相同的值时返回#N/A错误。大多数查找场景都用精确匹配。

○ 1或TRUE（省略时默认）：近似匹配。查找列必须升序排列，找到小于等于查找值的最大值。常用于分段查找（如税率、成绩等级）。

【注意】强烈建议精确匹配时明确写0，不要省略最后一个参数。省略时默认是近似匹配，如果查找列没有升序排列，结果会出错且很难发现。

近似匹配（分段查找）

=VLOOKUP(F2,A:B,2,1)

近似匹配适用于分段判断的场景，例如根据分数判断等级、根据销售额计算提成比例等。使用近似匹配时，查找列（A列）必须按升序排列，否则结果不正确。

原理：VLOOKUP从上往下找，找到最后一个小于等于查找值的位置，返回对应结果。例如分数等级表：0-59不及格、60-69及格、70-79良好、80-100优秀。查找75分时，找到70（小于等于75的最大值），返回「良好」。

VLOOKUP的局限性

● 只能从左向右查找，查找列必须在最左边。要从右向左查找需要用其他方法（如INDEX+MATCH）

● 插入或删除列时，返回列号需要手动修改，容易出错

● 查找列中有重复值时，只返回第一个匹配的结果

● 大数据量时计算较慢（因为是数组运算）

二、HLOOKUP函数（横向查找）

HLOOKUP与VLOOKUP类似，只是查找方向不同。H是Horizontal（横向）的缩写，它在表格的第一行查找指定的值，返回该列中指定行的内容。

语法结构

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

参数说明

● lookup_value：要在第一行查找的值

● table_array：查找区域

● row_index_num：返回行号，要返回的数据在查找区域中的第几行

● range_lookup：匹配方式，同VLOOKUP

示例

=HLOOKUP("三月",A1:E5,3,0)

在第一行中查找「三月」，找到后返回第3行对应的值。

【注意】HLOOKUP实际使用频率远低于VLOOKUP，因为大多数表格是纵向排列的。原理和VLOOKUP完全相同，只是方向转了90度。

三、INDEX+MATCH组合（最强大灵活的查找）

INDEX+MATCH是Excel中最强大、最灵活的查找组合，被很多Excel高手誉为「查找之王」。它克服了VLOOKUP的所有局限性，支持左右查找、上下查找、多条件查找等。

两个函数分别的作用

MATCH函数：找位置

MATCH在一行或一列中查找指定值，返回该值在区域中的「位置序号」（第几个）。

=MATCH(查找值, 查找区域, 匹配方式)

INDEX函数：按位置取值

INDEX在一个区域中，根据指定的行号和列号，返回对应位置的「值」。

=INDEX(数据区域, 行号, [列号])

两个函数组合起来就是：先用MATCH找到目标在第几行/第几列，再用INDEX根据这个位置取出对应的值。

基础查找公式

=INDEX(D:D,MATCH(F2,A:A,0))

公式逐段解析（从内到外）

第一层：MATCH(F2, A:A, 0)

MATCH函数在A列中查找F2的值，找到后返回它在A列中的行号（第几个）。

● F2：查找值，要找什么

● A:A：查找区域，在哪一列找

● 0：精确匹配（同VLOOKUP的最后一个参数）

● 返回结果：一个数字，表示查找值在A列中的位置。例如F2的值在A列第5行，MATCH就返回5

第二层：INDEX(D:D, MATCH结果)

INDEX函数在D列中，根据MATCH返回的行号，取出对应位置的值。

● D:D：返回列，要从哪一列取数据

● MATCH返回的行号：第几行

● 返回结果：D列中对应行的值，即我们要查找的结果

举例说明：要查找「张三」的成绩，A列是姓名、D列是成绩：

● MATCH("张三", A:A, 0) → 在A列找到张三在第5行 → 返回5

● INDEX(D:D, 5) → 取D列第5行的值 → 返回张三的成绩

为什么INDEX+MATCH比VLOOKUP好？

1. 支持从右向左查找。VLOOKUP只能从左向右，INDEX+MATCH查找列和返回列可以任意位置

2. 插入/删除列不会出错。VLOOKUP的返回列号是固定数字，插入列后需要手动改；INDEX+MATCH直接引用列，自动适应

3. 计算速度更快。大数据量时INDEX+MATCH明显比VLOOKUP快

4. 更灵活。可以轻松实现多条件查找、反向查找、双向查找等高级功能

反向查找（从右向左）

=INDEX(A:A,MATCH(F2,D:D,0))

根据D列的值查找A列的内容，这是VLOOKUP做不到的（除非用辅助列）。INDEX+MATCH只需交换查找列和返回列的位置即可。

双向查找（行列交叉查找）

=INDEX(A1:E10,MATCH(F2,A:A,0),MATCH(F3,1:1,0))

两个MATCH分别找行号和列号，INDEX根据行列交叉点取值。例如根据姓名和科目查找对应的成绩，这就是经典的「双向查找」或「交叉表查询」。

四、XLOOKUP函数（Excel 365新一代查找）

XLOOKUP是Excel 365和Excel 2021推出的新一代查找函数，可以看作VLOOKUP的全面升级版。它集VLOOKUP、INDEX+MATCH的优点于一身，语法更简洁、功能更强大。

语法结构

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

参数详解

1. lookup_value：查找值

2. lookup_array：查找区域（在哪一列/行找）

3. return_array：返回区域（从哪一列/行取）

4. if_not_found：找不到时返回什么，可选。默认返回#N/A

5. match_mode：匹配模式，可选。0精确匹配（默认）、-1近似匹配（找不到返回下一个较小的）、1近似匹配（找不到返回下一个较大的）、2通配符匹配

6. search_mode：搜索模式，可选。1从上到下（默认）、-1从下到上（倒序查找）、2二进制升序、-2二进制降序

基础用法

=XLOOKUP(F2,A:A,D:D)

在A列查找F2的值，返回D列对应的值。比VLOOKUP简洁得多，而且默认就是精确匹配，不用担心忘记写最后一个参数。

XLOOKUP的优势

● 默认精确匹配，更安全

● 查找列和返回列分开引用，支持左右任意方向查找

● 插入/删除列不受影响

● 支持找不到时自定义返回值，不用嵌套IFERROR

● 支持从下往上查找（找最后一个匹配项）

● 支持通配符匹配

● 支持返回整行/整列（动态数组溢出）

找不到时显示自定义文本

=XLOOKUP(F2,A:A,D:D,"未找到")

第四个参数指定找不到时的返回值，不用再写=IFERROR(VLOOKUP(...),"未找到")这样的嵌套公式。

从下往上查找（找最后一个匹配项）

=XLOOKUP(F2,A:A,D:D,"未找到",0,-1)

第六个参数设为-1，表示从下往上搜索。这样当查找列有重复值时，返回的是最后一个匹配项，而不是第一个。这在查找最后一次交易、最后一次登录等场景非常有用。

返回整行数据

=XLOOKUP(F2,A:A,A:D)

返回区域设为多列时，XLOOKUP会自动溢出整行数据，一个公式返回多列结果。

【注意】XLOOKUP仅Excel 365和Excel 2021及以上版本支持。如果需要兼容旧版Excel，建议使用INDEX+MATCH组合。

五、LOOKUP函数（经典老式查找）

LOOKUP是Excel中最古老的查找函数之一，有两种使用形式：向量形式和数组形式。虽然功能不如新函数强大，但在某些特定场景下非常好用，尤其是查找最后一个非空值、最后一个匹配项等。

向量形式

=LOOKUP(lookup_value, lookup_vector, [result_vector])

在一行或一列（lookup_vector）中查找，返回另一行或另一列（result_vector）对应位置的值。使用向量形式时，查找区域必须按升序排列。

数组形式

=LOOKUP(lookup_value, array)

在数组的第一行或第一列中查找，返回最后一行或最后一列对应位置的值。

经典用法：查找最后一个非空值

=LOOKUP(1,0/(A:A<>""),A:A)

公式解析

A:A<>""

判断A列每个单元格是否不为空，返回TRUE/FALSE数组。

0/(...)

用0除以TRUE/FALSE数组。TRUE相当于1，FALSE相当于0，所以：

● 0/TRUE = 0（非空单元格对应0）

● 0/FALSE = #DIV/0!（空单元格对应错误值）

LOOKUP(1, 0/..., A:A)

LOOKUP查找1，但数组中只有0和错误值，没有1。LOOKUP的特性是：如果找不到查找值，就返回小于查找值的最大值中最后一个的对应结果。所以它会找到最后一个0（即最后一个非空单元格），返回A列对应的值。

【注意】这个公式非常实用，可以快速找到一列中最后一个有数据的单元格，常用于动态统计最后一行数据、最新记录等场景。

查找最后一个匹配项

=LOOKUP(1,0/(A:A=F2),D:D)

原理和上面类似，只是把非空判断换成了等于查找值的判断。当A列有多个F2的值时，返回最后一个对应的D列值。

六、MATCH函数（查找位置）

MATCH函数的作用是「找位置」，它不返回具体的值，只返回查找值在区域中的位置序号（第几个）。MATCH通常不单独使用，而是和INDEX、OFFSET等函数组合使用。

语法

=MATCH(lookup_value, lookup_array, [match_type])

参数详解

1. lookup_value：查找值

2. lookup_array：查找区域，必须是单行或单列

3. match_type：匹配类型，可选。0精确匹配、1近似匹配（升序，默认）、-1近似匹配（降序）

三种匹配类型的区别

0 — 精确匹配

找到完全相等的值才返回位置，找不到返回#N/A。这是最常用的匹配方式。

1 — 近似匹配（升序）

查找区域必须按升序排列。找到小于等于查找值的最大值的位置。找不到（查找值比第一个还小）返回#N/A。常用于分段查找。

-1 — 近似匹配（降序）

查找区域必须按降序排列。找到大于等于查找值的最小值的位置。

示例

=MATCH("苹果",A2:A10,0)

在A2:A10中查找「苹果」，如果「苹果」在第3个位置，就返回3。注意返回的是在查找区域中的相对位置，不是工作表的绝对行号。

七、多条件查找

实际工作中经常需要同时满足多个条件才能查找（如姓名+部门、产品+规格等），即多条件查找。下面介绍几种常用方法。

方法一：辅助列+VLOOKUP

最简单的方法，在数据区域最左边插入一列，把多个条件用&拼接起来，然后用VLOOKUP查找拼接后的值。

辅助列公式：=A2&B2查找公式：=VLOOKUP(F2&F3,A:D,4,0)

优点：简单易懂，适合新手。缺点：需要插入辅助列，可能破坏原表结构。

方法二：INDEX+MATCH多条件（数组公式）

=INDEX(D:D,MATCH(1,(A:A=F2)*(B:B=F3),0))

公式解析

(A:A=F2)*(B:B=F3)

两个条件判断相乘。在Excel中，TRUE相当于1，FALSE相当于0。两个条件都满足时，1*1=1；只要有一个不满足，结果就是0。

结果是一个由0和1组成的数组，1表示两个条件都满足的行，0表示不满足的行。

MATCH(1, ... , 0)

在0和1的数组中查找第一个1的位置，即第一个同时满足两个条件的行号。

INDEX(D:D, MATCH结果)

根据行号取出D列对应的值。

【注意】这是数组公式，Excel 2019及更早版本需要按Ctrl+Shift+Enter三键确认；Excel 365和2021直接回车即可。

方法三：XLOOKUP多条件

=XLOOKUP(F2&F3,A:A&B:B,D:D)

XLOOKUP支持数组运算，直接把查找值和查找列都用&拼接即可，写法非常简洁。

方法四：LOOKUP多条件

=LOOKUP(1,0/((A:A=F2)*(B:B=F3)),D:D)

原理和LOOKUP查找最后一个匹配项类似，只是条件从一个变成多个。当有多个满足条件的行时，返回最后一个的结果。

八、模糊查找（通配符匹配）

有时候我们不需要精确匹配，只需要包含某个关键词就能找到，这就是模糊查找。Excel中使用通配符实现模糊查找。

Excel中的通配符

● *（星号）：代表任意多个字符（包括0个）

● ?（问号）：代表任意一个字符

● ~（波浪号）：转义符，要查找*或?本身时，前面加~

VLOOKUP模糊查找

=VLOOKUP("*"&F2&"*",A:D,4,0)

在查找值前后拼接*号，表示只要包含F2的内容就算匹配。例如F2是「张」，就能找到「张三」「张小明」「小张」等所有包含「张」的内容。

【注意】通配符查找必须是精确匹配模式（最后一个参数为0或FALSE），近似匹配不支持通配符。

XLOOKUP模糊查找

=XLOOKUP("*"&F2&"*",A:A,D:D,"未找到",2)

XLOOKUP需要把第五个参数（match_mode）设为2才启用通配符匹配模式。

常见模糊查找模式

● "关键词*"：以关键词开头

● "*关键词"：以关键词结尾

● "*关键词*"：包含关键词

● "?关键词"：关键词前面有一个任意字符

● "关?词"：中间一个字符任意

九、查找最后一个匹配项

默认情况下，VLOOKUP、INDEX+MATCH等都是返回第一个匹配的结果。如果要找最后一个匹配的结果（如最后一次交易、最后一次登录），需要用特殊方法。

方法一：LOOKUP法（最经典）

=LOOKUP(1,0/(A:A=F2),D:D)

前面已经讲过原理，这是最常用的查找最后一个匹配项的公式。

方法二：XLOOKUP倒序查找

=XLOOKUP(F2,A:A,D:D,"未找到",0,-1)

XLOOKUP第六个参数设为-1，从下往上搜索，自然就返回最后一个匹配项。写法更简洁。

方法三：INDEX+MAX+IF

=INDEX(D:D,MAX(IF(A:A=F2,ROW(A:A),0)))

用IF找出所有满足条件的行号，用MAX取最大的行号（即最后一行），再用INDEX取值。这也是数组公式。

十、查找所有匹配项

当查找列有多个重复值时，有时候我们需要把所有匹配的结果都列出来，而不只是第一个或最后一个。

方法一：FILTER函数（Excel 365最简单）

=FILTER(D:D,A:A=F2,"无结果")

FILTER是Excel 365的动态数组函数，可以根据条件筛选出所有满足条件的值，自动溢出显示。这是最简单的查找所有匹配项的方法。

方法二：TEXTJOIN+IF（合并所有结果）

=TEXTJOIN(",",TRUE,IF(A:A=F2,D:D,""))

把所有匹配结果用逗号连接成一个文本字符串。TEXTJOIN是Excel 2019及以上版本的函数。

方法三：INDEX+SMALL+IF（通用版，逐个提取）

=IFERROR(INDEX(D:D,SMALL(IF(A:A=F2,ROW(A:A),99999),ROW(A1))),"")

这是经典的提取所有匹配项的数组公式。在第一个单元格输入后向下拖动，就能依次提取所有匹配结果，提取完毕显示为空。

公式解析

IF(A:A=F2,ROW(A:A),99999)

如果A列等于查找值，返回行号；否则返回一个很大的数（99999）。结果是一个由行号和99999组成的数组。

SMALL(..., ROW(A1))

SMALL函数取第N小的值。ROW(A1)在第一行时是1，取最小的行号（第一个匹配项）；向下拖动时变成ROW(A2)=2，取第二小的行号（第二个匹配项），以此类推。

INDEX(D:D, SMALL结果)

根据行号取出D列对应的值。

IFERROR(..., "")

提取完毕后SMALL会取到99999，INDEX会返回错误，用IFERROR转为空。

十一、其他查找相关函数

CHOOSE函数

=CHOOSE(index_num, value1, [value2], ...)

根据序号从列表中选择对应的值。例如CHOOSE(2,"A","B","C")返回"B"。常用于简单的枚举转换，如数字转等级、数字转星期等。

OFFSET函数

=OFFSET(reference, rows, cols, [height], [width])

以指定单元格为基准，偏移指定的行数和列数，返回一个新的单元格或区域引用。常用于动态区域、动态图表数据源等场景。

INDIRECT函数

=INDIRECT(ref_text, [a1])

把文本字符串转换为单元格引用。例如INDIRECT("A"&B2)，如果B2是5，就相当于A5。常用于间接引用、跨表引用等场景。注意INDIRECT是易失性函数，大量使用会影响计算速度。

ROW/COLUMN函数

=ROW()返回当前行号=COLUMN()返回当前列号

返回当前单元格的行号或列号，常用于生成序号、数组公式中的计数器等。

十二、方法对比与选择建议

各查找函数对比

选择建议

1. Excel 365用户：优先用XLOOKUP，简单直接功能强

2. 需要兼容旧版：用INDEX+MATCH组合，灵活强大

3. 简单查找、数据量小：VLOOKUP也够用，写法简单

4. 找最后一个匹配项：用LOOKUP(1,0/...)经典公式

5. 返回所有匹配项：365用FILTER，旧版用INDEX+SMALL+IF

6. 多条件查找：优先用XLOOKUP或INDEX+MATCH数组公式

7. 分段查找（如税率、等级）：VLOOKUP近似匹配或XLOOKUP近似匹配

十三、常见问题与技巧

VLOOKUP返回#N/A怎么办？

● 查找值和查找列内容确实不匹配：检查数据是否一致

● 存在空格或不可见字符：用TRIM(CLEAN())清洗数据后再查找

● 查找值是文本、查找列是数字（或反之）：用VALUE或TEXT统一格式

● 查找列不在查找区域的第一列：VLOOKUP只能在第一列查找，调整列的位置或改用INDEX+MATCH

● 想让找不到时显示为空而不是#N/A：用IFERROR包裹，如=IFERROR(VLOOKUP(...),"")

查找结果不对的常见原因

● VLOOKUP忘记写最后一个参数：默认近似匹配，如果查找列没排序结果就错了

● 查找区域没有绝对引用：向下复制时区域跑偏了，记得加$锁定

● 查找列有重复值：默认只返回第一个匹配的

● 大小写问题：Excel查找默认不区分大小写

提升查找速度的技巧

● 尽量用精确匹配，近似匹配在未排序时可能更慢

● 大数据量时优先用INDEX+MATCH，比VLOOKUP快

● 避免整列引用（如A:A），尽量用具体范围（如A2:A1000）

● 减少易失性函数（OFFSET、INDIRECT）的使用

● 数据量特别大时考虑用Power Query关联

区分大小写的查找

默认情况下Excel的查找函数不区分大小写。如果需要区分大小写，可以用EXACT函数配合数组公式：

=INDEX(D:D,MATCH(TRUE,EXACT(A:A,F2),0))

EXACT函数区分大小写比较两个文本是否完全相同，返回TRUE/FALSE。然后MATCH找第一个TRUE的位置。

总结

Excel查找函数众多，各有特点。掌握以下核心即可应对绝大多数场景：

● 基础查找：VLOOKUP（入门）或 XLOOKUP（365首选）

● 高级查找：INDEX+MATCH组合（最灵活、兼容性最好）

● 多条件查找：XLOOKUP拼接 或 INDEX+MATCH数组公式

● 找最后一个：LOOKUP(1,0/(...),...) 或 XLOOKUP倒序

● 返回所有匹配：FILTER函数（365） 或 INDEX+SMALL+IF

● 模糊查找：通配符*和?

选择方法时，优先考虑简单、易维护的方案，同时兼顾版本兼容性和数据量大小。能用新函数就用新函数，需要兼容旧版就用经典组合。