Vlookup-5.xls

1、LookupLookup函函数数 By Excelhome gouweicao78 函数 LOOKUP 有两种语法形式：向量和数组。 向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域 或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一 种形式为自动在第一列或第一行中查找数值。 语语法法 1 1 符合第2条“正方形或高度大宽度小”的情况，在第一列查找 lookup_value，相当于不用第3参数的vlookup。 搬一个由chenjun版主提

2、供的计算个人所得税的例子： 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925这个更是Lookup的强项了，尤其是数组语法。 20向量语法 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的； 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 从从右右向向左左查查询询 工工号号姓姓名名 KT002李四光用lookup,就无所谓从左向右或者从右向左查

3、询了。 看看，是不是比 vlookup简洁啊，呵 呵。 张三丰 李四光 王麻子 赵六儿 KT001 KT002 KT003 KT004 By Excelhome gouweicao78 精确查找也是lookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置lookup第2个参数为0/（ XXX=YYY）条条件件等等式式，即为精确查找。 根据姓名在$B$9:$F$12中查找籍贯，$B$9:$F$12区域最好使用绝对引用（可在公式中选定区域按F4转换），便于复制。 【表一】是定义的单元格区域$B$9:$F$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中

4、查看修改 注意范围已改为$C$8:$E$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3 根据工号在$C$8:$F$12中查找出生年月，但此时返回的是时间序列值（即格式没有带过来） 近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALSE，非零值均视为TRUE） 试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题： 此用法与vlookup相似， 都要求lookup _vlaue排序。 若有多个符合条件的情况： vlookup返回的

5、是第一个满足 条件的值，lookup返回的是最 后一个满足条件的值 由于EXCEL中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下） 左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”） 通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回J 这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改 这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由Vlookup查询到字母，最后“500,10;2000,15;5000,20;20000,25;40000,30;60000,35;80000,40;10

6、0000,45是一个2列9行的数组 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 看看，是不是比 vlookup简洁啊，呵 呵。 Lookup函数的特性“返回查找区”中小于或等于关键字的最大值 应用： 1 1、求求某某行行（列列）最最后后一一个个不不为为空空的的值值 ai456=LOOKUP(9.999999E+307,A:A) 返回A列最后一个数值型字符串（包括日期） 我已对业务员排序=LOOKUP(REPT(“座“,255),A:A) 返回A列最后一个文本型字符串（包括文本型数字） 已对业务员排序=LOOKUP(1,0/(A2:A65536“),A2:A6553

7、6) 返回A列最后一个不为空的单元格的值 Excel已对业务员排序=LOOKUP(1,0*SEARCH(“*“,A2:A65536),A2:A65536)返回A列最后一个不为空的单元格的值（其效率比上一个公式明显慢多了） 123若要返回其行号，则将Lookup的第3个参数改为row(2:65536) 0.375按F9键刷新可以改变A列的内容，以便比较。 Excel 0.482 2、用用来来寻寻找找某某个个值值上上次次出出现现的的位位置置。( (用用行行号号表表示示） 0.596 0.672#N/A=LOOKUP(1,0/(A$5:A13=A14),ROW(A$5:A13)注意看绝对引用和相对引

8、用的混合使用。 ai5=LOOKUP(1,0/(A$5:A14=A15),ROW(A$5:A14)出现#N/A错误证明之前区域没有出现过查找值。 0.626#N/A=LOOKUP(1,0/(A$5:A15=A16),ROW(A$5:A15) 我6=LOOKUP(1,0/(A$5:A16=A17),ROW(A$5:A16) 一一个个用用lookuplookup查查找找上上一一个个当当 前前值值的的应应用用实实例例 我17=LOOKUP(1,0/(A$5:A17=A18),ROW(A$5:A17) 0.619#N/A=LOOKUP(1,0/(A$5:A18=A19),ROW(A$5:A18) 45

9、6#N/A=LOOKUP(1,0/(A$5:A19=A20),ROW(A$5:A19) ai15=LOOKUP(1,0/(A$5:A20=A21),ROW(A$5:A20) 3 3、LookupLookup的的数数组组应应用用 Lookup数组的引引用用运算 Lookup第1参数为数组时，需要选定与之尺寸一致的单元格区域输入公式后按Ctrl+shift+enter结束形成多单元格数组公式 已已对对业业务务 员员排排序序 业业务务员员位位置置销销售售额额第二个超市B=LOOKUP(“张三丰“,“李四光“,B27:C35) 狗皮膏商场A100返回的是与第一参数一样维数的数组（1行2列）注意有多个符

10、合条件时均返回最后一个。 狗尾草超市B200 李四光店铺C300商场A店铺C=LOOKUP(“赵老六“,“七麻子“;“狗尾草“,“王老五“,B27:C35) 李四光超市B400超市B你好吗已对业务员排序，返回正确值，如果多个符合条件返回最后一个。 七麻子店铺C500 王老五你好吗600700500=LOOKUP(F29:G30,C27:D35) 张三丰商场A700200600注意：业务员列已排序，但位置列没有排序，所以，如果出现多个记录，返回不正确答案哦。 张三丰第二个800 赵老六商场A900Lookup第2、3参数本身就支持数组（见帮助文件），且不用按三键结束。多数的Lookup第2、3参

11、数都是数组。 这里有个例子 已对业务员排序，返回正确值，如果多个符合条件返回最后一个。 注意：业务员列已排序，但位置列没有排序，所以，如果出现多个记录，返回不正确答案哦。 Lookup第2、3参数本身就支持数组（见帮助文件），且不用按三键结束。多数的Lookup第2、3参数都是数组。 10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 如果你觉得输数组常量麻烦（要区分【,】、【;】，文本要加【“】）， 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vloo

12、kup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 以下举例中的公式并不是数组公式，输入时不用Ctrl+Shift+Enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子： 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的； 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义

13、的名称，可以自【插入】【名称】【定义】中查看 从从右右向向左左查查询询 我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此时可以使用的公式很多，如lookup、index+match、offset+match、indirect+match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 工工号号姓姓名名 KT002李四光这个公式先由【IF(1,0,$C$9:

14、$C$12,$B$9:$B$12)】计算出内存数组： “KT001“,“张三丰“;“KT002“,“李四光“;“KT003“,“王麻子“;“KT004“,“赵六儿“ 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 张三丰 李四光 王麻子 赵六儿 KT001 KT002 KT003 KT004 By Excelhome shangyu版主 精确查找是vlookup最基本也是最常用的功能，对于数据量大的查找，其速度比菜单中的查找还快。设置v

15、lookup第四个参数为false或0，即为精确查找。 根据姓名在$B$9:$F$12中查找籍贯，$B$9:$F$12区域最好使用绝对引用（可在公式中选定区域按F4转换），便于复制。 查找的姓名必须位于$B$6:$F$9的第一列；籍贯在$B$6:$F$9位于第四列，因此第三个参数为4；精确查找，第四个参数FALSE 【表一】是定义的单元格区域$B$9:$F$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中查看修改 注意范围已改为$C$8:$E$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3 根据工号在$C$8:$F$12中查找出生年月，但此时

16、返回的是时间序列值（即格式没有带过来） 近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALSE，非零值均视为TRUE） 试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题： 由于EXCEL中汉字字符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下） 左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”） 通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回J 这个例子一般用于姓名的缩写，而上面的公式只找第一

17、个字符，因此需要修改 这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由Vlookup查询到字母，最后“40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】

18、直接使用vlookup就不行了 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 处处理理查查找找错错误误 返回 vlookup使用中一般会产生以下错误： #VALUE!和 #REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。 有

19、时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。 #N/A是常见的错误信息，需要针对不同情况处理： 精确匹配查找时出现，是由于未找到完全匹配的值。 近似匹配查找时出现，是由于查找值小于数据区的最小值。 如果vlookup的参数产生错误，vlooukp也返回相同错误，那不在本讨论范围内。 Lookup函数的查找错误与vlookup一样，处理方法一样,左边蓝色的部分是Lookup的处理 精确匹配查找时出现#N/A错误 首先检查引用范围是否正确，查询值是否在数据区的第一列。 当确定引用正确、不应出现#N/

20、A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 表表一一 张三丰100 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码 李四军 200 B18单元格长度 4 B18第四个字符码 32 数 据 含 空 格 等 张三丰 #N/A 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 100 100 对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去除空格后查找 李四军#N/A 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 200 200 对于Tabl

21、e_array中的空格，也可以使用trim。 这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组 因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 李四军 #N/A 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空格 对于这类不可见字符，可以先复制该字符，然后替换该字符为空白 表表二二 100A 查

22、找内容格式不符，也会造成N/A错误 200B 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 数 据 格 式 不 一 致 100#N/A 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 200#N/A 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 A A 这个公式将数值100用&连接空字符串“，强制转为文本 B B 这个公式将文本200用*1运算，强制转为数值 要注意的是采用修改格式的方法，仅仅将单元格格式更改还不行： 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正

23、转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 分列位于菜单栏数据分列 由文本转数值也可以 复制一空白单元格，选定需转换的数据区，选择性粘贴加 当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理 表表三三 A100 注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引用该单元格也会返回#N/A错误。 C200 当C55选B时，显然应该返回错误（数据区没有B）。 处理错误 B#N/A 这个单元格使用条件格式处理，

24、条件格式公式isna(D55)，并设定条件字体颜色与底色相同。 #N/A 使用ISNA测试vlookup函数是否返回#N/A，如vlookup函数返回#N/A，则ISNA函数返回TRUE。再使用IF函数判断，即可去除错误。 精确查找时，也可以直接判断查找值是否存在来去除#N/A错误，如此例使用COUNTIF测试数据区是否有查找值。 近似匹配查找时出现#N/A错误 近似匹配查找时，除了前述的格式等原因，查找值小于数据区的最小值将返回#N/A。 对于此类错误，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用ISNA去除。 处理错误 #VALUE!和 #REF!是由于col_index

25、_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。 当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去

26、除空格后查找 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空格 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 这两个错误都是由于格式不一致造成，一个是按数值查文本，

27、一个是按文本查数值 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理 注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引用该单元格也会返回#N/A错误。 这个单元格使用条件格式处理，条件格式公

28、式isna(D55)，并设定条件字体颜色与底色相同。 使用ISNA测试vlookup函数是否返回#N/A，如vlookup函数返回#N/A，则ISNA函数返回TRUE。再使用IF函数判断，即可去除错误。 精确查找时，也可以直接判断查找值是否存在来去除#N/A错误，如此例使用COUNTIF测试数据区是否有查找值。 一个用lookup查找上一个当前值的应用实例返返回回 測測試試架架領領用用登登記記表表 領用日期編號工作單測試數量發出人領用人歸還日期累計使用次數 2005/9/8P/N59263553350MLA組3350 2005/9/8P/N59263561250MLA組4600 2005/9/

29、9P/N59263578000MLB組12600 2005/9/10P/N59263582000MLA組2000 2005/9/9P/N56263692200MLB組5.9152200 2005/9/9P/N59263692200MLB組4200 2005/9/12P/N56263182500MLA組4700 2005/9/12P/N5926403880MLA組05.9.175080 2005/9/12P/N56264023340MLA組5.9178040 2005/9/13P/N59263841020MLB組5.9156100 2005/9/14P/N56263002000MLB組10040

30、 2005/9/15P/N5926363600MLA組600 2005/9/15P/N5626363600MLA組600 2005/9/15P/N59263201150MLB組1750 2005/9/15P/N5626379100MLB組700 2005/9/15P/N5926379100MLB組1850 2005/9/16P/N56263902000MLA組05.9.172700 2005/9/16P/N5926377550000MLA組551850 2005/9/16P/N56263821050MLB組1050 2005/9/17P/N59263892000MLB組2000 2005/9/

31、17P/N56263902000MLA組3050 2005/9/18P/N59264094005MLB組6005 2005/9/20P/N56263921200MLB組4250 2005/9/22P/N59263574500MLB組10505 ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML ML 备注 备注 更換新座 更換新座 =D10 =D10+D12 =D10+D12+D14 更換新座 =D10+D12+D14+D1610000 上一个P/N56已经“更换新座”，从头开始计=D18 归零问题：原题见 原始数据表的编号太多了，

32、不便于测试（已编好公式） 所以另作了一个表，编号只有2种，看起来就很方便了。 更換新座gouweicao78 2006/3/7 更換新座 所有的測試架上的 “座”使用次數每 超過10000次則需 更換新“座”，更 換新座後，如何實 現會從重新計數 呢？（請注意並非 只有一種測試架） 請教大伙，多謝！ 公式解释： =IF($B6=“,“,IF(LOOKUP(2,1/(B$6:B6=B6),I$5:I5)=“更換新座“,D6, SUMIF(OFFSET(B6,LOOKUP(2,1/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() ,B6, OFFSET(D6,LOOKUP(2,1

33、/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() 1、IF(LOOKUP(2,1/(B$6:B6=B6),I$5:I5)=“更換新座“,D6以编号为关键字找 出上一个同编号对应的“备注”内容，如果是“更换新座”则返回D6（即本次试 验的次数），否则用sumif求。 2、Sumif的Range条件判断区域为Offset OFFSET(B6,LOOKUP(2,1/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() 以当前编号所在单元格平移0行0列，高度是Lookup找到上一个备注为“更换新座 ”的行号减当前行号（负数），即得到 上上次次更更换换新新座座至至今今的

34、的“编编号号”区区域域 Sumif条件B6 Sumif求和区域与上次更换新座至今的“编号”区域 对对应应的的测测试试次次数数区区域域。 測測試試架架領領用用登登記記表表 公式解释： =IF($B6=“,“,IF(LOOKUP(2,1/(B$6:B6=B6),I$5:I5)=“更換新座“,D6, SUMIF(OFFSET(B6,LOOKUP(2,1/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() ,B6, OFFSET(D6,LOOKUP(2,1/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() 1、IF(LOOKUP(2,1/(B$6:B6=B6),I$5

35、:I5)=“更換新座“,D6以编号为关键字找 出上一个同编号对应的“备注”内容，如果是“更换新座”则返回D6（即本次试 验的次数），否则用sumif求。 2、Sumif的Range条件判断区域为Offset OFFSET(B6,LOOKUP(2,1/(I$5:I5=“更換新座“),ROW(I$5:I5)-ROW() 以当前编号所在单元格平移0行0列，高度是Lookup找到上一个备注为“更换新座 ”的行号减当前行号（负数），即得到 上上次次更更换换新新座座至至今今的的“编编号号”区区域域 Sumif条件B6 Sumif求和区域与上次更换新座至今的“编号”区域 对对应应的的测测试试次次数数区区域域。