VLOOKUP、ISERROR和IF函数在excel中的高效应用_匹配查找

1、有关EXCEL表的查找引用类函数的匹配VLOOKU函数的使用方法(入门级)VLOOKUP :功能是在表格的首列查找指定的数据，并返回指定的数据所在行中的指定 列处的数据。函数表达式是：=VLOOKUP(lookup_value,table_array,col_i ndex_ nu m,ra nge_lookup)=VLOOKUP (查找目标，查找范围，返回值的列数，0精确或1模糊查找)=VLOOKUP (判断目标条件，查找范围，返回第几列的数据，是否精确匹配)1. 首先在你需要的新的 EXCEL表里的找出一个空白列， 为匹配数据的结果返回列，确定后就直接输入 “ =VLOOKUP( ”即表示确

2、定。2. 判断的条件：然后还是在你需要的新的 EXCEL表里的找出“关联数据” 点击一下起始的关联数据即可，然后输入一个逗号一一即：需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。女口： =VLOOKUP( C4，=VLOOKUP(C4,C4:G1185,5,0)3. 跟踪数据的区域：在原数据库的EXCEL表里选定一个区域，该区域应包含“关联数据”和要查找“匹配的数据” |如：=VLOOKUP(C4,C4:G1185,4. 返回第几列的数据：然后，从第一列中查找的数据(如：C4 )开始，看一下你要取的数据在第几列，就直接在函数后面输入相应的数字，然后输入一个逗号，接着一般输入0和

3、括弧”)”回车即可完成。5. 是否精确匹配：注意最后一个0表述精确匹配。如果找不到，则返回#N/A，表示没有该数据。最后完成函数：=VLOOKUP(C4,C4:G1185,5,0)=VLOOKUP(C4, $C$4: $G$1185,5,0)6. 特别注意事项：常见错误之一：格式不匹配。格式不一样。如元数据是靠左对齐“数值”，生成的新表格里的数据是靠右对齐“文 本”我们就连一个空& “”，把它强制转换成文本如：=VLOOKUP(C4&“” ,C4:G1185,5,0)1、格式不一样。如元数据是靠右对齐“文本”，生成的新表格里的数据是靠左对齐“数值”，我们就连一个空*1，把它强制

4、转换成文本如=VLOOKUP(C4*1,C4:G1185,5,0)B13p牡二VL00KUP(A13, $B$2:$D$& 3, 0)ABcDE12I眾龟-性别31刘障2042南星男1653吴路女3064张新远男75李玉成男4586张欣女3Q9101112113南星16.141匚李玉成45有关EXCEL表的查找引用类函数的匹配567391011234 §6南星吴路张新远李玉成张欣性别男男男辿204530L. 781. 671.671. 91.B1I. 5912 蛀名性电£ 年尿身高13 SS IE6770100707860VLOOKU函数的使用方法（初级篇）一、VL

5、OOKU多行查找时复制公式的问题VLOOKUP函数的第三个参数是查找返回值所在的列数，如果我们需要查找返回多列时， 这个列数值需要一个个的更改，比如返回第2列的，参数设置为2，如果需要返回第3列的,就需要把值改为3。如果有十几列会很麻烦的。那么能不能让第3个参数自动变呢？向后复制时自动变为 2，3，4，5。在EXCEL中有一个函数 COLUMN它可以返回指定单元格的列数，比如=COLUMNS（ A1）返回值 1=COLUMNS（ B1）返回值 2而单元格引用复制时会自动发生变化，即A1随公式向右复制时会变成 B1, C1，D1。这样我们用COLUM函数就可以转换成数字 1，2，3，4。例：下例

6、中需要同时查找性别，年龄，身高，体重。在二VLOOKUP($A13,COLUMN(51),0)有关EXCEL表的查找引用类函数的匹配901. 81公式：=VLOOKUP（$A13,$B$2:$F$8, COLUMN（B1）0）公式说明：这里就是使用COLUMN B1 ）转化成可以自动递增的数字。二、VLOOKU匿找岀现错误值的问题。1、如何避免出现错误值。EXCEL2003 在VLOOKU匿找不到，就#N/A的错误值，我们可以利用错误处理函数把错误值 转换成0或空值。即: =IF（ISERROR（VLOOKUP（参数略）,"",VLOOKUP （参数略）EXCEL2007

7、，EXCEL2010中提供了一个新函数 IFERROR,处理起来比 EXCEL2003简单多了。IFERROR （VLOOKUFP ），""）2、VLOOKU函数查找时岀现错误值的几个原因A、实在是没有所要查找到的值B、查找的字符串或被查找的字符中含有空格或看不见的空字符，验证方法是用=号对比一下，如果结果是 FALSE,就表示两个单元格看上去相同，其实结果不同。C、参数设置错误。VLOOKU啲最后一个参数没有设置成1或者是没有设置掉。第二个参数数据源区域，查找的值不是区域的第一列，或者需要反回的字段不在区域里，参数设置在入门讲里已注明，请参阅。D 、数值格式不同，如果查找

8、值是文本，被查找的是数字类型，就会查找不到。解决方法是 把查找的转换成文本或数值，转换方法如下：文本转换成数值：*1或-或/1数值转抱成文本：&""VLOOKU函数的使用方法(进阶篇)一、字符的模糊查找在A列我们知道如何查找型号为“AAA的产品所对应的B列价格，即:=VLOOKUP( C1,A:B,2,0)如果我们需要查找包含“AAA的产品名称怎么表示呢？如下图表中所示。B10荻 二VLOOKUPA2: Bd 2, 0)ABDE1立口 .! 口口价格r2AAB8-3213BRE-53334SFEW-3245/5AAA-226/6WBWEB-2217f8g立口口口价格

9、/10AAA &11公式=VLOOKUP("*"&A10 & "*",A2:B6,2,0)公式说明：VLOOKU的第一个参数允许使用通配符“ *”来表示包含的意思， 把*放在字符的两边，即"*" & 字符& "*"。二、数字的区间查找数字的区间查找即给定多个区间，指定一个数就可以查找出它在哪个区间 并返回这个区间所对应的值。在VLOOKU入门中我们提示 VLOOKU的第4个参数，如果为0或FALSE是精 确查找，如果是1或TRUE或省略则为模糊查找，那么实现区间查找正是第 4

10、个 参数的模糊查找应用。首先我们需要了解一下VLOOKU函数模糊查找的两个重要规则：1 、引用的数字区域一定要从小到大排序。杂乱的数字是无法准确查找到的。 如下面A列符合模糊查找的前题，B列则不符合。A1B12342翹673672478545簸896T2 、模糊查找的原理是：给一定个数，它会找到 和它最接近，但比它小的那 个数。详见下图说明。最后看一个实例:例：如下图所示，要求根据上面的提成比率表，在提成表计算表中计算每个 销售额的提成比率和提成额。B11&二 VLOOKUP (A1Z,$A$3: $B$ 匚 分ABC 氏D1提成比率表2销售额比率表示区间301%大于等干0小干1000

11、0、L4100002%大千等千1小3000053000035(大干等干30000b于4DDDB640000454大于等干40000/b干)7200000大于 200000>f 89提成计算表10销售额提成比率I 115000血5012565000. 04226013129000. 02258145000000.05250001 口有关EXCEL表的查找引用类函数的匹配公式：=VLOOKUP(A11,$A$3:$B$7,2)公式说明：1 、上述公式省略了 VLOOKU最后一个参数，相当于把第四个参数设置成1或TRUE这表示VLOOKU要进行数字的区间查找。2 、图中公式中在查找5000时返

12、回比率表0所对应的比率1%原因是0和1 0000与5000最接近，但VLOOKU只选比查找值小的那一个，所以公式会返回 0 所对应的比率1%VLOOKU函数的使用方法(高级篇)一、VLOOKU的反向查找。一般情况下，VLOOKU函数只能从左向右查找。但如果需要从右向右查找, 则需要把区域进行“乾坤大挪移”，把列的位置用数组互换一下。例1:要求在如下图所示表中的姓名反查工号。二.B9- *7 =VL00KUP (A9, IF (1, Oh B2: B5, A2: A5), 2, 0)A B.：C .“ DEF飞1工号姓名2A001张3A002赵三4A003杨五Z5A004孙二/6r_/f7二 /

13、8姓名_工号,血02|9赵510.11公式：=VLOOKUP(A9,IF(1,0,B2:B5,A2:A5),2,0)公式剖析:有关EXCEL表的查找引用类函数的匹配1 、这里其实不是VLOOKU可以实现从右至右的查找，而是利用IF函数 的数组效应把两列换位重新组合后，再按正常的从左至右查找。2 、IF (1,0,B2:B5,A2:A5) 这是本公式中最重要的组成部分。在 EXCE L函数中使用数组时(前提时该函数的参数支持数组)，返回的结果也会是一个 数组。这里1和0不是实际意义上的数字，而是1相关于TRUE 0相当于FALSE 当为1时，它会返回IF的第二个参数(B列)，为0时返回第二个参数

14、(A列)。 根据数组运算返回数组，所以使用IF后的结果返回一个数组(非单元格区域)： "张一","A001""赵三","A002""杨五","A003""孙二","A004"、VLOOKUB数的多条件查找。VLOOKUP函数需要借用数组才能实现多条件查找。例2:要求根据部门和姓名查找 C列的加班时间。79直 匸 VLOOIOJF1 (朋IF 1, 0,战：A5 阳戈：C5),乙 0)h BF G I H售售事售 部销销人销号售 工销姓名

15、 赵三25加班时间153_ 6加班时间151分析：我们可以延用例1的思路，我们的努力万向不是让 VLOOKUP身实 现多条件查找，而是想办法重构一个数组。多个条件我们可以用&连接在一起，同样两列我们也可以连接成一列数据，然后用IF函数进行组合。公式：=VL00KUP(A9&B9,IF(1,0,A2:A5&B2:B5,C2:C5),2,0)公式剖析：1 、A9&B9把两个条件连接在一起。把他们做为一个整体进行查找。2 、A2:A5&B2:B5,和条件连接相对应，把部分和姓名列也连接在一起， 作为一个待查找的整体。3 、IF(1,0,A2:A5&B2

16、:B5,C2:C5)用 IF (1，0把连接后的两列与 C列数据合并成一个两列的内存数组。按F9后可以查看的结果为：" 销售张一 ",1;"销售赵三",5;"人事杨五",3;"销售赵三",64 、完成了数组的重构后，接下来就是VLOOKU的基本查找功能了，另外公式中含有多个数据与多个数据运算(A2:A5&B2:B5),，所以必须以数组形式输 入，即按ctrl+shift 后按ENTER吉束输入。三、VLOOKU函数的批量查找。VLOOKUP 一般情况下只能查找一个，那么多项该怎么查找呢?例3要求把如图表中所

17、有张一的消费金额全列出来AIBC1日期姓名消费额22011-2-1 张一10032011-2-2 王二2|42011-2-3 张一20052011-2-44462011-2-5 张一10007S9张- 11 10011020011100012分析：经过前面的学习，我们也有这样一个思路，我们在实现复杂的查找 时，努力的方向是怎么重构一个查找内容和查找的区域。要想实现多项查找，我们可以对查找的内容进行编号，第一个出现的是后面连接 1,第二个出现的连接 2 0 0 0公式：=VLOOKUP(B$9&ROW(A1),IF(1,O,$B$2:$B$6&COUNTIF(INDIRECT(&

18、quot; b2:b"&ROW($2:$6),B$9),$C$2:$C$6),2,)公式剖析：1 、B$9&ROW(A1连接序号，公式向下复制时会变成 B$9连接1，2，32 、给所有的张一进行编号。要想生成编号，就需要生成一个不断扩充 的区域(INDIRECT("b2:b"&ROW($2:$6)，然后在这个逐行扩充的区域内统计 “张 一”的个数，在连接上$B$2:$B$6后就可以对所有的张一进行编号了。3 、IF (1，0把编号后的B列和C组重构成一个两列数组通过以上的讲解，我们需要知道，VLOOKU函数的基本用法是固定的，要 实现高级查

19、找，就需要借助其他函数来重构查找内容和查找数组。2. Table_array 为“需要在其中查找数据的数据表 ”，可以使用单元格区域或区域名 称等。 如果 range_lookup 为 TRUE 或省略，则 table_array 的第一列中的数 值必须按升序排列，否则，函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE ， table_array 不必进行排序。 Table_array 的第一列中的数值可以为文本、数字或逻辑值。若为文本时， 不区分文本的大小写。3. Col_index_num 为 table_array 中待返回的匹配值的列序号。Col

20、_index_num 为 1 时，返回 table_array 第一列中的数值； Col_index_num 为 2 时，返回 table_array 第二列中的数值，以此类推；如果 Col_index_num 小 于 1 ，函数 VLOOKUP 返回错误值 #VALUE! ；如果 Col_index_num 大于 table_array 的列数，函数 VLOOKUP 返回错误值 #REF! 。4. Range_lookup 为一逻辑值， 指明函数 VLOOKUP 返回时是精确匹配还是近似 匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配 值，则返回小于 look

21、up_value 的最大数值；如果 range_value 为 FALSE ，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A 。ISERROR ：它属于 IS 系列， IS 系列用来检验数值或引用类型，有九个相关的函数：ISBLANK(value) ：判断值是否为空白单元格。ISERR(value) ：判断值是否为任意错误值(除去#N/A )。ISERROR(value) ：判断值是否为任意错误值( #N/A 、#VALUE! 、 #REF! 、 #DIV/0! 、 #NUM! 、 #NAME? 或 #NULL! )。ISLOGICAL(value) ：判断值是否为

22、逻辑值。ISNA(value) ：判断值是否为错误值 #N/A (值不存在)。ISNONTEXT(value) ：判断值是否为不是文本的任意项 (注意此函数在值为空白单元格 时返回 TRUE )。ISNUMBER(value) ：判断值是否为数字。ISREF(value) ： 判断值是否为引用。ISTEXT(value) ：判断值是否为文本。IF：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。下所示：函数表达式为： IF(logical_test,value_if_true,value_if_false)，其中含义如logical_test ：要检

23、查的条件。 value_if_true ：条件为真时返回的值。 value_if_false ：条件为假时返回的值。下面介绍下通过上述的三个函数如何达到我想要的要求的， 下图是工作中的两个 EXCEL 表， sheet1 和 sheet2 ，现在要将 sheet2 的每一行数据在 sheet1 中查找匹配，如有 sheet1 中存在，则在 sheet2 中的 E 列显示 “存在 ”，否则显示 “不存在 ”。有关EXCEL表的查找引用类函数的匹配E6物料代码1 01.01.00Ij-电阻规格型号仓库名称0aO603ARnHS贴片仓1.01.01.00021 01.01.000；1 01.01.0

24、000.33Q,0603ARoHS贴片仓lQ,0603AFtaHS贴片仓1,8a0603ARoHS贴片仓sheet2丄T345TTTTo物料吒码1 01 01. QU011.01.01,000211.01.01.00031.01.01.00011.01.01.00021.01.01.00071.O1.D1.DD0B1 01.G1.D0091 01 01.0010称料阻阻阻阻规整生号QQ.06Q3*.RoHSC.33Q.0603/RoHS10,0603 *,RoHS1.80.0603AROHS5.1Q0603AROHS10Q,0E03.RoHS100.0503. RoHSIBQ.tlOSOlAR

25、oHS18 2Q.±1%,0603.*RoHSsheetl首先使用了 VLOOKUP函数将sheetl =VLOOKUP(A2,sheet1!$A$2:$C$952,1,FALSE) 在sheet1的所有列中查找就是使用第二个条件: 复制的时候不会随着单元格位置变化而变化， 回#N/A，FALSE表示返回精确匹配值。中的数据在sheet2中进行查找，其中A2表示用来匹配项的数据，将A2sheet1!$A$2:$C$952，“ $表示绝对引用，1表示匹配成功后返回第一列的数据，否则返有关EXCEL表的查找引用类函数的匹配有关EXCEL表的查找引用类函数的匹配注：绝对引用和相对引用只要在

26、公式栏里面对应的数据下按F4功能键即可切换当有返回结果后刚开始直接使用IF去判断了，公式是：=IF(VLOOKUP(A2,sheet1!$A$2:$C$952,1,FALSE)=A2," 存在","不存在")，这个时候发现当 匹配成功的时候输出了存在”当匹配不成功是却输出了“#N/A； 直没法实现想要的结果，后来发现VLOOKUP只能输出指定的值或者“#N/A；而与A2判断的结果也为“#N/A”作为IF函数是无法识别 “#N/A,”这样导致不会输出不存在”，所以要想办法将IF的第一个条件的结果是“Ture” or "False,"于是

27、就找到了函数 ISERROR (Value )，这个输出的结果是“ Ture ” or "False"于是公式就变成了 =IF(ISERROR(VLOOKUP(A2,sheet1!$A$2:$C$952,1,FALSE),"不存在","存在")，大功告成，输出自己想要的结果，当在 shhet2中的项目能在sheet1中找到时输出 存在”找不到 时输出不存在”ABCEF站片仓 存在oa.OGQS.tRoHS0 33Q.0B0r.RoHE物枷弋码11 01 01 00011.01.01.00021.01 01 ,ooo1.01 01.00

28、011Q.0693AR*HS1贴片仓.貼片仓:HS Sui-e Sirif* JiC* B /E S 3学“叩% *攝字車1晓 花鬲 习VLOOKUP(AZ sheeP l$A12：3C(952h L FALSE)."不存在："存左")总结：VLOOKUP的函数比较好用，可以寻找并且匹配，但是要注意只能是匹配项在 首列，如果不是则要用 HLOOKUP函数。EXCEL的函数功能还是挺强大的，好好研究对于我们数据统计和处理是非常有帮助的，目前对于VLOOKUP、ISERROR和IF三个函数有一定的认识，以后还得继续研究学习。有关EXCEL表的查找引用类函数的匹配=VL

29、OOKUP(C3,C3:G1184,5,0)如何在 EXCEL 中进行多条件匹配一般情况下，我们都是根据单个条件（关键字）进行数据匹配，采用VLOOKU函数进行，但有时遇到需要根据两个及其以上的条件进行匹配, 这时采用VLOOKU已经无法实现。多条件匹配可采用 SUMPRODUCSUMIFS函数、以及高级筛选法来 实现，函数原理请自查EXCEI帮助或百度，多条件匹配具体见下图操作 演示（如无法查看动态演示，需要点击图片，再点击图片下的“查看原 图”即可）： 方法一：SUMPRODU函数法，优点：可自动更新，如数据量大，打开文件时计算慢。缺点：仅支持数值型匹配结果，不支持文本匹配结果。n *茹1

30、1 £ V = = 1 孙 |ttat 金 *_ * |小A订哽”合井后JS中t雪” 丨：勢贴板底字X恳龙齐方式N17卫币汩 插入灵商布喝金式 敖克 重甩 規图 加载项 PowerPivot專彗炙件匹配一"竝 X AB1 c IDEFGEIJKL1公司部门员工数收入公司部门员工数收入1 2A110010000A13A2no11000A34A312012000B15A413013000B46B114014000C17B215015000C28B316016000D39B417017000D410C118018000E111C219019000F212c320020000F313c421021000F414D12202200015D22302300016D32402400017D42502500018E12602600019E22702700020E32802800021E4290290002223103100024F33203200025F433033000方法二：SUMIF