Excel查找引用速查:VLOO-KUP、XLOO-KUP、INDEX写法_第1页
Excel查找引用速查:VLOO-KUP、XLOO-KUP、INDEX写法_第2页
Excel查找引用速查:VLOO-KUP、XLOO-KUP、INDEX写法_第3页
Excel查找引用速查:VLOO-KUP、XLOO-KUP、INDEX写法_第4页
Excel查找引用速查:VLOO-KUP、XLOO-KUP、INDEX写法_第5页
已阅读5页,还剩3页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

Excel查找引用速查:VLOOKUP、XLOOKUP、INDEX写法项目内容文档编号EX-SOP-2026-004版本号V1.0编制角色办公效率组审核角色数据管理负责人生效日期2026年12月1日查找引用函数解决的是同一个问题:手头有一个值,要去另一张表里把跟它同一行的另一个值取回来。VLOOKUP是最常用的入门函数,INDEX加MATCH是方向自由的组合写法,XLOOKUP是Office365/2021之后替代前两者的内置函数。本文档按这三个工具分别给语法、成品效果表和填错时的改法,最后用员工信息查询和跨表价格匹配两个完整示例,展示同一份数据用三种写法分别怎么做、结果是否一致。关于绝对引用符号的说明:Excel公式中用于锁定行或列的符号,在部分平台显示为乱码。本文档统一用“锁定列”“锁定行”“行列都锁”来描述它的作用。公式框里实际输入的仍然是那个英文符号(Shift加4键),读者看到“查找区域行列都锁”时,理解为在区域地址的列字母和行号前都加上那个符号即可。目录一、三种写法怎么选:先看版本,再看方向二、VLOOKUP:查找值在区域第一列时最省事语法与四个参数精确匹配与近似匹配:日常统一用精确匹配成品效果表:按工号查部门填错时的改法:#N/A、#REF!、下拉后错位三、INDEX加MATCH:查找列不在左边时用这个组合MATCH先找位置,INDEX再取该位置的值成品效果表:从右往左查双向查找:行标签加列标题交叉取值填错时的改法:#N/A、#REF!、区域大小不一致四、XLOOKUP:一个函数替代前两种写法六个参数与默认行为if_not_found:把“找不到”的处理写进公式里成品效果表:同样按工号查部门填错时的改法:#NAME?、#VALUE!、反向搜索五、查找失败的统一处理:IFNA与IFERROR六、完整示例员工信息查询:同一任务用VLOOKUP、INDEX+MATCH、XLOOKUP各做一遍跨表价格匹配:从产品表取单价,按数量算金额七、启动清单一、三种写法怎么选:先看版本,再看方向先确认版本。XLOOKUP只在Office365和Excel2021及更新版本、以及新版WPS中可用。在Excel2016/2019中打开带有XLOOKUP的文件,公式会显示#NAME?,这不是公式写错,是版本不支持。不确定收件人用什么版本时,用INDEX加MATCH,它在所有版本中行为一致。再看查找方向。VLOOKUP只能从查找列向右取返回值,查找值必须在所选区域的第一列。要找的值在左边、要返回的值在右边(比如根据部门反查工号),VLOOKUP做不到,用INDEX加MATCH或XLOOKUP。XLOOKUP不限制方向,查找列和返回列可以是任意两列。最后看数据量。数据量在几万行以上、需要频繁重算时,INDEX加MATCH和XLOOKUP只扫描查找列,VLOOKUP会扫描整个表区域,前两者在大数据量下更快。二、VLOOKUP:查找值在区域第一列时最省事1.语法与四个参数语法:VLOOKUP(查找值,查找区域,返回列号,匹配方式)。查找值:要去找的那个值,可以是单元格引用。查找区域:包含查找列和返回列的一块连续区域,查找值必须落在这块区域的第一列。返回列号:从查找区域的第一列数起,要返回的值在第几列,不是工作表的列字母。匹配方式:精确匹配写0或FALSE,近似匹配写1或TRUE。2.精确匹配与近似匹配:日常统一用精确匹配精确匹配要求查找值与区域第一列中的某一项完全一致,找不到就返回#N/A。近似匹配在找不到完全一致的值时,返回小于查找值的最大值,要求第一列按升序排列,否则结果不可预测。日常办公中绝大多数场景(按工号查姓名、按订单号查金额、按产品编码查单价)用的都是精确匹配,写0。只有分数段划等级、税率阶梯这类“区间匹配”才用近似匹配,且必须确认第一列已升序排列。3.成品效果表:按工号查部门数据在Sheet1的A2:D6(A列工号,B列姓名,C列岗位,D列部门),查询公式写在Sheet2的B2。单元格内容或公式结果或说明Sheet1!A2EMP-0101工号Sheet1!D2技术部对应部门Sheet1!A3EMP-0102工号Sheet1!D3销售部对应部门Sheet1!A4EMP-0103工号Sheet1!D4财务部对应部门Sheet2!A2EMP-0102要查的工号Sheet2!B2=VLOOKUP(A2,Sheet1!A:D,4,0)销售部公式读法:用A2的“EMP-0102”在Sheet1的A到D列中查找,返回从A列数起第4列(D列部门)的值,精确匹配。查找区域写成整列A:D,行列自然锁定,下拉时不会偏移。4.填错时的改法#N/A:查找值在区域第一列中找不到。先确认查找值确实存在;再用TRIM清理查找值和查找区域两边的空格;最后检查两边的数据类型是否一致,文本格式的工号和数值格式的工号在VLOOKUP看来是不同的值。查不到是正常情况时,用IFNA包住公式,显示自定义文字而不是错误值。#REF!:返回列号写大了。查找区域选了A到D四列,返回列号写4是合法的,写5就超出范围。数清楚区域有几列,把返回列号改到有效范围内。下拉后结果全部错位:查找区域没有锁定。第一行公式引用的是A2:D6,下拉到第二行时区域跟着下移变成A3:D7,查找范围发生了偏移。把查找区域改为行列都锁(或直接用整列A:D)。三、INDEX加MATCH:查找列不在左边时用这个组合1.MATCH先找位置,INDEX再取该位置的值MATCH负责找到查找值在某一列中排第几个,返回的是区域内的相对位置,不是工作表行号。语法:MATCH(查找值,查找区域,0),第三个参数0表示精确匹配。INDEX负责按行号(和列号)从指定区域中取出那个位置的值。语法:INDEX(区域,行号,列号),只取一列时可以省略列号。把MATCH的结果放进INDEX的行号位置,就实现了查找:先在某一列找到位置,再从另一列取出同一位置的值。查找列和返回列可以是任意两列,不受左右方向的限制。2.成品效果表:从右往左查数据在A2:C6(A列部门,B列姓名,C列工号),要根据部门反查工号。VLOOKUP做不到,因为工号在部门的右边,查找列不在第一列。用INDEX加MATCH:公式写在E2。行A列部门B列姓名C列工号E列查询与结果2技术部陈晓EMP-0101E2要查的部门:销售部3销售部刘洋EMP-0102—4财务部周敏EMP-0103—5技术部赵强EMP-0104—E2=INDEX(C2:C5,MATCH("销售部",A2:A5,0))EMP-0102在A列找到“销售部”在第2个位置,返回C列第2个值3.双向查找:行标签加列标题交叉取值INDEX可以同时接受行号和列号。用两个MATCH分别找行位置和列位置,就能在二维表格中交叉定位任意单元格。示例:B2:D4是姓名与科目的交叉表,要根据姓名和科目取分数。行位置用MATCH(姓名,A2:A4,0),列位置用MATCH(科目,B1:D1,0),写成=INDEX(B2:D4,行MATCH,列MATCH)。4.填错时的改法#N/A:MATCH用精确匹配但找不到值,原因和VLOOKUP的#N/A相同(空格、格式不一致)。用TRIM清理后再试。#REF!:MATCH返回的位置超出了INDEX区域的行数或列数。最常见的原因是MATCH用的区域和INDEX用的区域大小不一致,一个选了A2:A100另一个选了C2:C99,行数对不上。两处区域写成相同的起止行。双向查找时列号写死:第二个MATCH的查找区域(列标题行)要行列都锁,否则向右下拉时列标题区域跟着偏移。四、XLOOKUP:一个函数替代前两种写法1.六个参数与默认行为语法:XLOOKUP(查找值,查找列,返回列,未找到时返回什么,匹配模式,搜索模式)。前三个参数必填,后三个可选。查找值是要找的值,查找列是搜索范围,返回列是结果所在的范围,两个范围的行数必须一致。默认行为是精确匹配,找不到时返回#N/A。不需要像VLOOKUP那样数“返回第几列”,直接选中结果所在的那一列即可。2.if_not_found:把“找不到”的处理写进公式里VLOOKUP查不到时返回#N/A,要用IFNA或IFERROR在外面包一层。XLOOKUP的第四个参数直接就是“找不到时显示什么”,可以写“无此工号”“未找到”或空白。示例:=XLOOKUP(A2,Sheet1!A:A,Sheet1!D:D,"无此工号"),查不到时显示文字而不是错误值。3.成品效果表:同样按工号查部门数据与VLOOKUP示例相同(Sheet1的A列工号、D列部门),公式写在Sheet2的B2。单元格内容或公式结果或说明Sheet1!A2:A4EMP-0101、EMP-0102、EMP-0103查找列Sheet1!D2:D4技术部、销售部、财务部返回列Sheet2!A2EMP-0102要查的工号Sheet2!B2=XLOOKUP(A2,Sheet1!A:A,Sheet1!D:D,"无此工号")销售部与VLOOKUP的写法对比:不需要数第几列,不需要锁定返回列号,找不到时的处理直接写在公式里。在Office365中,XLOOKUP在大数据集上的性能优于VLOOKUP,因为它只扫描查找列,不扫描整个表区域。4.填错时的改法#NAME?:当前版本不支持XLOOKUP(Excel2016/2019及更早版本)。改用INDEX加MATCH,或VLOOKUP(如果方向允许)。#VALUE!:查找列和返回列的行数不一致。一个选了A2:A100另一个选了D2:D99,XLOOKUP无法把查找位置和返回位置对齐。两处写成相同的起止行,或都用整列(A:A和D:D)。要从最后一条往上找:第六个参数(搜索模式)写-1,从查找列的最后一个值开始反向搜索。默认1是从第一条往下找。适用于同一工号有多条记录、要取最近一条的场景。五、查找失败的统一处理:IFNA与IFERROR三种写法查不到时都会返回#N/A。如果“查不到”本身是正常业务状态(比如新员工还没有工号、新产品还没有定价),用IFNA把错误值替换成可读的文字:=IFNA(VLOOKUP(...),"未建档")。IFNA只处理#N/A,公式本身写错造成的其他错误(如#REF!)照常暴露,不会被掩盖。IFERROR会吞掉所有错误,包括参数写错造成的#REF!和#VALUE!。排查公式问题时先临时去掉IFERROR看真实报错,确认公式正确后再加回去。XLOOKUP有if_not_found参数,不需要额外套IFNA。六、完整示例1.员工信息查询:同一任务用三种写法各做一遍填写前:员工数据表在Sheet1,A列工号,B列姓名,C列岗位,D列部门,E列入职日期,共100行(第2到101行)。Sheet2的A2是要查询的工号,B2到E2要分别取出姓名、岗位、部门、入职日期。填写中:四种取值的写法分别用三种工具完成,放在Sheet2的不同列做对比。B2取姓名(返回列在查找列的右侧),C2取岗位,D2取部门,E2取入职日期。工具B2(姓名)公式D2(部门)公式适用版本VLOOKUP=VLOOKUP($A2,Sheet1!$A:$E,2,0)=VLOOKUP($A2,Sheet1!$A:$E,4,0)所有版本INDEX+MATCH=INDEX(Sheet1!B:B,MATCH($A2,Sheet1!A:A,0))=INDEX(Sheet1!D:D,MATCH($A2,Sheet1!A:A,0))所有版本XLOOKUP=XLOOKUP($A2,Sheet1!A:A,Sheet1!B:B,"无此工号")=XLOOKUP($A2,Sheet1!A:A,Sheet1!D:D,"无此工号")365/2021+填写后:A2填EMP-0103,三种写法返回的结果如下。取值项VLOOKUP结果INDEX+MATCH结果XLOOKUP结果姓名(B2)周敏周敏周敏部门(D2)财务部财务部财务部工号不存在时(A2改EMP-9999)#N/A#N/A无此工号三种写法在数据正确时结果完全一致。差异在边界情况:VLOOKUP和INDEX+MATCH需要额外套IFNA才能显示友好文字,XLOOKUP把这件事写在了第四个参数里。2.跨表价格匹配:从产品表取单价,按数量算金额填写前:订单表在Sheet1,A列产品编码,B列数量;产品价格表在Sheet2,A列产品编码,B列单价,C列产品名称。订单表需要根据A列编码从价格表取B列单价,再乘以数量算金额。Sheet1行A列产品编码B列数量C列单价(待填)D列金额(待填)2P-100120待填待填3P-10035待填待填4P-100212待填待填5P-99998待填待填(不存在于价格表)价格表Sheet2:A2为P-1001单价150,A3为P-1002单价80,A4为P-1003单价220。填写中:C2写查找单价的公式,D2写=B2*C2算金额。C2用XLOOKUP写法(365/2021+),D2直接乘。C2:=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,0),找不到时返回0,金额也是0,便于识别。下拉到C5。填写后:行A列编码B列数量C列单价D列金额说明2P-1001201503000正常匹配3P-100352201100正常匹配4P-10021280960正常匹配5P-9999800价格表中不存在,单价返回0第5行的编码不在价格表中。XLOOKUP的if_not_found写0,单价显示0、金额显示0,而不是#N/A。如果希望显示文字提醒,把第四个参数改为"无报价",但这样C列会变成文本,D

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论