一、VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)根据线索查找目标值参数lookup_value线索(值或者单元格引用)table_array目标区域(两列或多列数据)col_index_num目标在目标区域的第几列(数值)range_lookup 匹配方式(TRUE/FALSE)注意事项1 线索所在列必须在目标区域的第一列2 匹配方式0/FALSE: 返回精确匹配值1/TURE/省略返回精确匹配值或近似匹配值,如果找不到精确匹配值则返回小于lookup_value 的最大数值目标区域的第一列必须以升序排序3 如果 table_array 第一列中有两个或多个值与 lookup_value 匹配则使用第一个找到的值1、常规查找2、将返回的错误值替换成文字3、 查找一系列的值这种也只能按照一定的顺序查找如果需要查找指定列详见方法54、逆向查找推荐使用INDEXMATCH的方法但VLOOKUP也是可以进行逆向查找的适用于线索列不在第一列示例函数如下5、查找指定列需要结合MATCH进行使用6、通配符查找姓黄的人黄*姓黄的人q且姓名为2个字的人黄?7、模糊匹配1首先需要进行排序、升序2在VLOOKUP中查找方式1/TURE/省略返回精确匹配值或近似匹配值,如果找不到精确匹配值则返回小于 lookup_value 的最大数值目标区域的第一列必须以升序排序。二、MATCHMATCH(lookup_value, lookup_array, [match_type])返回查找值在查找区域中的相对位置返回是一个数值)参数lookup_value: 查找的值lookup_array 查找的区域match_type 查找方式注意事项1 行号和列号都是针对区域而言2 查找文本值时不区分大小写字母3 查找方式1或省略 查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列. 0 查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列. -1 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列.1、MATCH可以进行多条件查找三、INDEXINDEX(array,row_num,column_num)返回行列交叉处的值一般和MATCH配合使用参数array 区域row_num 行号column_num 列号注意事项1 行号和列号都是针对区域而言2 如果将 row_num 或 column_num 设置为 0函数 INDEX 分别返回对整列或整行的引用可以认为返回区域的第几个值1、常规查找2、文本数字查找Tips为了不改变原始数据可以在公式中运用加减乘除运算将文本型数字变成数字3、查无此人4、查找一系列值5、逆向查找可以直接写因为数据源的排列顺序并不影响查找因为INDEX函数所需的参数是数据在所在数据源的行和列的位置信息6、查找指定列只要用两个MATCH知道两个条件所在行列即可7、多条件查找1使用VLOOKUP①建立辅助列辅助列一般放在第一列②根据辅助列进行查找注意VLOOKUP的数据源区域不可以进行拼接但是查找值可以进行拼接2 使用INDEX使用INDEX可以不需要制作辅助列8、案例员工信息卡的制作结果数据源1首先使用数据验证来进行名字的选择2使用INDEXMATCH查找信息主要逻辑是根据姓名和条件对数据源进行行列查找3使用图片超链接和名称进行照片的选择①如果直接用INDEX查找的话出来的结果是0②应该先新建立一个名称命名为图片其对象为使用INDEXMATCH查找得出的图片③将任意一张图片复制粘贴到员工信息表中选中图片并使其图片④回车之后图片会根据姓名的变化而变化四、OFFSETOFFSET(reference,rows,cols,height,width)以指定的引用为参照系通过给定偏移量返回新的引用可以返回一个单元格也可以返回一个区域参数Reference偏移量参照系的引用区域单元格或相连单元格区域的引用rows相对于偏移量参照系的左上角单元格上下偏移的行数----正下负上cols相对于偏移量参照系的左上角单元格左右偏移的列数----正右负左height 高度即所要返回的引用区域的行数。Height 必须为正数width 宽度即所要返回的引用区域的列数。Width 必须为正数注意事项1 如果行数和列数偏移量超出工作表边缘函数 OFFSET 返回错误值 #REF!。2 如果省略 height 或 width则假设其高度或宽度与 reference 相同。3 函数 OFFSET 实际上并不移动任何单元格或更改选定区域它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。1、特定区域总值计算2、OFFSETCOUNTA实现动态的数据验证如何让新增的数据自动添加到下拉菜单提示使用到名称的功能1首先理解如何引用某一区域的非空单元格的所有内容2然后使用名称工具定义名称引用位置为OFFSETCOUNTA的组合函数3使用数据验证实现动态下拉列表五、INDERECTINDIRECT(ref_text,[a1])返回由文本字符串指定的引用参数ref_text: 定义为引用的名称或对作为文本字符串的单元格的引用; 如果是对另一个工作簿的引用(外部引用)则工作簿必须被打开[a1]: TRUE1或省略第一参数为A1样式的引用FALSE0第一参数为R1C1样式的引用一种加引号一种不加引号。INDIRECT(A1)——加引号文本引用即引用A1单元格所在的文本即返回单元格本身INDIRECT(A1)——不加引号地址引用引用的是A1单元格地址即引用单元格所在地址的值1、实现下拉菜单的二级联动1新建名称可以批量新建名称注意名称是不能以数字开头的所以根据所选内容创建名称的时候系统会自动在数字前面添加下划线_因此使用SUM时应该写SUM(INDIRECT(_I6))2数据验证3使用INDERECT配合第一步中创建的名称进行引用4配合SUM求和六、CHOOSECHOOSE(index_num, value1, [value2], ...)根据给定的索引值从参数串中选出相应值或操作参数index_num 索引值如果 index_num 为小数则在使用前将被截尾取整value1、2、3 参数串参数可以为数字、单元格引用、已定义名称、公式、函数或文本函数 CHOOSE 的数值参数不仅可以为单个数值也可以为区域引用。例如公式SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))1、常规查找2、 案例根据员工级别及销量计算提成3、案例配合VLOOKUP进行查找七、HYPERLINK超链接HYPERLINK(link_location, [friendly_name])创建快捷方式或跳转用以打开存储在 Internet 中的文档。参数link_location 要打开的文档的路径和文件名friendly_name 单元格中显示的跳转文本或数字值.显示为蓝色并带有下划线。如果省略 Friendly_name单元格会将 link_location 显示为跳转文本。 
长河君 · 2026/9/10 9:27:56 · 阅读 1.2k EXCEL：查找与引用函数
一、VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)根据线索查找目标值参数lookup_value线索(值或者单元格引用)table_array目标区域(两列或多列数据)col_index_num目标在目标区域的第几列(数值)range_lookup 匹配方式(TRUE/FALSE)注意事项1 线索所在列必须在目标区域的第一列2 匹配方式0/FALSE: 返回精确匹配值1/TURE/省略返回精确匹配值或近似匹配值,如果找不到精确匹配值则返回小于lookup_value 的最大数值目标区域的第一列必须以升序排序3 如果 table_array 第一列中有两个或多个值与 lookup_value 匹配则使用第一个找到的值1、常规查找2、将返回的错误值替换成文字3、 查找一系列的值这种也只能按照一定的顺序查找如果需要查找指定列详见方法54、逆向查找推荐使用INDEXMATCH的方法但VLOOKUP也是可以进行逆向查找的适用于线索列不在第一列示例函数如下5、查找指定列需要结合MATCH进行使用6、通配符查找姓黄的人黄*姓黄的人q且姓名为2个字的人黄?7、模糊匹配1首先需要进行排序、升序2在VLOOKUP中查找方式1/TURE/省略返回精确匹配值或近似匹配值,如果找不到精确匹配值则返回小于 lookup_value 的最大数值目标区域的第一列必须以升序排序。二、MATCHMATCH(lookup_value, lookup_array, [match_type])返回查找值在查找区域中的相对位置返回是一个数值)参数lookup_value: 查找的值lookup_array 查找的区域match_type 查找方式注意事项1 行号和列号都是针对区域而言2 查找文本值时不区分大小写字母3 查找方式1或省略 查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列. 0 查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列. -1 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列.1、MATCH可以进行多条件查找三、INDEXINDEX(array,row_num,column_num)返回行列交叉处的值一般和MATCH配合使用参数array 区域row_num 行号column_num 列号注意事项1 行号和列号都是针对区域而言2 如果将 row_num 或 column_num 设置为 0函数 INDEX 分别返回对整列或整行的引用可以认为返回区域的第几个值1、常规查找2、文本数字查找Tips为了不改变原始数据可以在公式中运用加减乘除运算将文本型数字变成数字3、查无此人4、查找一系列值5、逆向查找可以直接写因为数据源的排列顺序并不影响查找因为INDEX函数所需的参数是数据在所在数据源的行和列的位置信息6、查找指定列只要用两个MATCH知道两个条件所在行列即可7、多条件查找1使用VLOOKUP①建立辅助列辅助列一般放在第一列②根据辅助列进行查找注意VLOOKUP的数据源区域不可以进行拼接但是查找值可以进行拼接2 使用INDEX使用INDEX可以不需要制作辅助列8、案例员工信息卡的制作结果数据源1首先使用数据验证来进行名字的选择2使用INDEXMATCH查找信息主要逻辑是根据姓名和条件对数据源进行行列查找3使用图片超链接和名称进行照片的选择①如果直接用INDEX查找的话出来的结果是0②应该先新建立一个名称命名为图片其对象为使用INDEXMATCH查找得出的图片③将任意一张图片复制粘贴到员工信息表中选中图片并使其图片④回车之后图片会根据姓名的变化而变化四、OFFSETOFFSET(reference,rows,cols,height,width)以指定的引用为参照系通过给定偏移量返回新的引用可以返回一个单元格也可以返回一个区域参数Reference偏移量参照系的引用区域单元格或相连单元格区域的引用rows相对于偏移量参照系的左上角单元格上下偏移的行数----正下负上cols相对于偏移量参照系的左上角单元格左右偏移的列数----正右负左height 高度即所要返回的引用区域的行数。Height 必须为正数width 宽度即所要返回的引用区域的列数。Width 必须为正数注意事项1 如果行数和列数偏移量超出工作表边缘函数 OFFSET 返回错误值 #REF!。2 如果省略 height 或 width则假设其高度或宽度与 reference 相同。3 函数 OFFSET 实际上并不移动任何单元格或更改选定区域它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如公式 SUM(OFFSET(C2,1,2,3,1)) 将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。1、特定区域总值计算2、OFFSETCOUNTA实现动态的数据验证如何让新增的数据自动添加到下拉菜单提示使用到名称的功能1首先理解如何引用某一区域的非空单元格的所有内容2然后使用名称工具定义名称引用位置为OFFSETCOUNTA的组合函数3使用数据验证实现动态下拉列表五、INDERECTINDIRECT(ref_text,[a1])返回由文本字符串指定的引用参数ref_text: 定义为引用的名称或对作为文本字符串的单元格的引用; 如果是对另一个工作簿的引用(外部引用)则工作簿必须被打开[a1]: TRUE1或省略第一参数为A1样式的引用FALSE0第一参数为R1C1样式的引用一种加引号一种不加引号。INDIRECT(A1)——加引号文本引用即引用A1单元格所在的文本即返回单元格本身INDIRECT(A1)——不加引号地址引用引用的是A1单元格地址即引用单元格所在地址的值1、实现下拉菜单的二级联动1新建名称可以批量新建名称注意名称是不能以数字开头的所以根据所选内容创建名称的时候系统会自动在数字前面添加下划线_因此使用SUM时应该写SUM(INDIRECT(_I6))2数据验证3使用INDERECT配合第一步中创建的名称进行引用4配合SUM求和六、CHOOSECHOOSE(index_num, value1, [value2], ...)根据给定的索引值从参数串中选出相应值或操作参数index_num 索引值如果 index_num 为小数则在使用前将被截尾取整value1、2、3 参数串参数可以为数字、单元格引用、已定义名称、公式、函数或文本函数 CHOOSE 的数值参数不仅可以为单个数值也可以为区域引用。例如公式SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))1、常规查找2、 案例根据员工级别及销量计算提成3、案例配合VLOOKUP进行查找七、HYPERLINK超链接HYPERLINK(link_location, [friendly_name])创建快捷方式或跳转用以打开存储在 Internet 中的文档。参数link_location 要打开的文档的路径和文件名friendly_name 单元格中显示的跳转文本或数字值.显示为蓝色并带有下划线。如果省略 Friendly_name单元格会将 link_location 显示为跳转文本。