EXCEL中lookup函数的经典查找方式.docx

EXCEL中lookup函数的经典查找方式.docx

《EXCEL中lookup函数的经典查找方式.docx》由会员分享，可在线阅读，更多相关《EXCEL中lookup函数的经典查找方式.docx（11页珍藏版）》请在冰点文库上搜索。

EXCEL中lookup函数的经典查找方式

EXCEL中lookup函数的经典查找方式（总9页）

主讲老师：

卢子老师

　　讲座主题：

解析lookup函数的经典查找方式

　　学习是需要技巧和经验的。

感谢卢子老师为大家分享和交流他的实战经验。

下面是本期讲座的全部内容。

　　本期讲座包括两部分内容：

第一，lookup函数用法介绍；第二，通过实例讲解lookup函数经典的条件查找解法，通用公式基本可以写为：

LOOKUP（2,1/（条件）,查找数组或区域）或LOOKUP（1,0/（条件）,查找数组或区域）。

　　第一部分：

lookup函数用法介绍

　　lookup函数和vlookup函数是excel中最常用的两个查找函数。

vlookup函数能做到的lookup函数同样可以做到，而且可以做得更好。

　　LOOKUP函数有两种语法形式：

向量和数组。

本期就向量形式的展开交流和探讨。

　　向量形式的语法为：

LOOKUP（lookup_value,lookup_vector,result_vector）

　　其中的参数意义如下：

　　Lookup_value：

为所要查找的数值。

Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。

　　Lookup_vector：

为只包含一行或一列的区域。

Lookup_vector的数值可以为文本、数字或逻辑值。

Lookup_vector的数值必须按升序排序：

...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否则，LOOKUP不能返回正确的结果。

文本不区分大小写。

　　Result_vector:

只包含一行或一列的区域，其大小必须与lookup_vector相同。

　　比如lookup（A1,B1:

B10,C2:

C11）,其中C2:

C11的尺寸要与B1:

B10相同，且如果A1对应B列中的位置是B2的话，那么返回的将是C3的值。

　　LOOKUP函数说明：

　　第一，如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。

这就是为何返回最后一个满足条件的值的原理。

　　第二，如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值

#N/A。

　　利用这个特性，我们可以用=LOOKUP（1,0/（条件）,引用区域）这样一个通用公式来作查找引用。

　　第二部分：

lookup函数实例运用

　　运用一：

模糊查找

　　模糊查找的核心是第二个参数排序必须是升序，否则会导致查找值错误。

下图所示的表1是按升序排序的，表2没有排序。

　　分别在表1和表2下面对应的单元格输入公式。

　　表1的数据源是按升序排序的，根据lookup函数用法：

=LOOKUP（要查找的数据,查找范围,结果），在C24单元格设置公式：

=LOOKUP（B24,$B$5:

$B$17,$C$5:

$C$17），然后下拉得到正确结果。

　　表2的数据源是没有排序的，在J24单元格输入公式：

=LOOKUP（I24,$I$5:

$I$17,$J$5:

$J$17）

，然后下拉，发现J25单元格得到的结果是H126，显然不对。

通过表2的源数据可以看到I25单元格对应的值应该为J8单元格的值H142。

　　为什么会出错呢?

这就印证了第一部分的用法介绍中所讲到的：

Lookup_vector的数值必须按升序排序：

...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否则，LOOKUP不能返回正确的结果。

文本不区分大小写。

　　模糊查找，数据源一定要以升序先进行排序，否则就会出错。

在数据源没有排序的情况下，如何才能查找到正确结果?

LOOKUP函数有一个经典的条件查找解法，可以很好的解决此问题。

　　在第一部分有提到，通用公式基本可以写为：

LOOKUP（2,1/（条件）,查找数组或区域）或LOOKUP（1,0/（条件）,查找数组或区域）。

　　公式中的2、1、0等数字的含义是什么首先，条件是一组逻辑判断的值或逻辑运算得到的由TRUE和FALSE组成或者0与非0组成的数组，因而：

0/（条件）的作用是用于构建一个由0或者#DIV!

0错误组成的值。

比如数据源中能查找到对应值就是ture，没有就是false。

形式如：

0/True=0，0/false=#DIV0!

，查找到就0，没有就是错误值。

　　如果LOOKUP函数找不到lookup_value（即：

1），则它与lookup_vector中小于或等于lookup_value的最大值（即：

0）匹配。

　　也就是说，要在一个由0和#DIV!

0组成的数组中查找1，肯定找不到1，因而将返回小于或等于1的最大值（也就是0）匹配。

用大于0的数来查找0，肯定能查到最后一个满足条件的。

　　以上的原理，被俗称为“以大欺小法”。

这种技巧在LOOKUP函数上的运用是很常见的。

　　利用上面的原理，不管有没有排序，只要使用上面的“以大欺小法”都能得到正确结果。

比如上面实例中，在J25单元格输入公式：

=LOOKUP（1,0/（I25=$I$5:

$I$17）,$J$5:

$J$17），就可以了。

　　运用二：

精确查找

　　第一，查找的数据没有对应值，可以利用ISNA（ISERROR）函数屏蔽错误值。

　　如上图所示，表3是数据源，在下面左边根据“番号”查找“俗称”。

单击C51单元格，输入公式=LOOKUP（1,0/（B51=$B$42:

$B$45）,$C$42:

$C$45），然后下拉可以看到下面的C52和C54单元格出现错误值。

这种情况可以利用ISNA（ISERROR）函数屏蔽错误值。

　　只要在公式外面嵌套个if（isna（lookup（）,"",lookup（）），这样的形式就可以把错误值屏蔽。

在H51单元格，输入这样的公式：

=IF（ISNA（LOOKUP（1,0/（G51=$B$42:

$B$45）,$C$42:

$C$45））,"",LOOKUP（1,0/（G51=$B$42:

$B$45）,$C$42:

$C$45）），下拉，就可以屏蔽错误值了。

将错误值屏蔽了，表格就好看多了。

　　上面公式中，""，是显示空的意思，错误就显示空，没有就查找。

　　第二，借助错误值来判定产品是否存在。

　　下图所示根据左边的数据源，来判定右边对应的数据是否在番号列中。

　　只需要嵌套一个isna函数就可以做到，如果没有存在就错误，有存在就......这样的形式。

在H62单元格输入公式：

=IF（ISNA（LOOKUP（1,0/（G62=$B$62:

$B$74）））,"否","是"），下拉就即可得出结果。

　　“图啥”网友问：

iserror与isna函数的区别。

ISNA只屏蔽#N/A错误，ISERROR屏蔽所有错误。

　　第三，LOOKUP函数多条件查找。

　　如上图所示，根据“俗称”和“订单号”来查找“订单数”和“尾数”，可以套用这样的公式：

=LOOKUP（1,0/（条件

（1）*

（2）*（3）.。

。

。

。

。

）,引用区域），用*或&将各个条件连接起来，*就是和的意思。

　　此题有两种方法：

　　第一，在K112单元格输入公式：

=LOOKUP（1,0/（（$I112=$B$112:

$B$120）*（$J112=$C$112:

$C$120））,D$112:

D$120），复制公式就可以得到结果。

　　第二，另外也可以使用这个公式：

=LOOKUP（1,0/（$I112&$J112=$B$112:

$B$120&$C$112:

$C$120）,D$112:

D$120）

　　第四，含某个字符查找。

　　按照上图所示，根据左边的数据源，来对含有某个字符进行查找。

单击G128单元格，输入公式：

=LOOKUP（1,0/（FIND（$F128,$B$128:

$B$131））,B$128:

B$131），就可以得到结果。

　　VLOOKUP函数与lookup函数对比：

　　第一，在多条件查找方面，就能看出lookup函数好用。

用vlookup多条件查找，最简单的方法就是借用辅助列。

　　第二，VLOOKUP函数对于反向查找是需要嵌套其余函数才能实现，而LOOKUP函数没有正反之分，因此在这方面LOOKUP函数会更加容易实现。

　　第三，vlookup在查找字符方面，可以使用*号类通配符。

LOOKUP是不支持通配符的，但可以使用FIND（查找字符，数据源区域）的形式代替。

　　本讲座有关的Excel源文件请在论坛下载：

&goto=findpost&ptid=7&pid=24&fromuid=1

讲座主题：

VLOOKUP函数运用（下）

　　在第3期的技术讲座中，已经学习了VLOOKUP函数的用法，也分析了一些基础例子。

无言老师在本期讲座通过几个实例来帮助大家更深入的了解VLOOKUP函数的使用。

　　VLOOKUP函数运用一：

　　VLOOKUP函数第三个参数返回的列数可以通过match函数定位查找的返回所需的列数，vlookup和match嵌合使用。

下图所示的A11：

H20单元格区域是excel源数据。

　　=match（需要查找的数据表列标题，元数据表列标题范围，0），这是精确查找定位第一次出现的列位置。

　　请使用vlookup函数解出下图所示的C24单元格中编号所对应的其余单元格的值。

单击C24单元格右下角的向下三角形，可以更换选择其余的编号。

　　在E24单元格输入公式：

=VLOOKUP（$C$24,$A$11:

$H$20,MATCH（D24,$A$11:

$H$11,0）,0）即可得到答案。

同样的方法可以求出其余单元格的值，只是把定位的单元格地址更改一下就好了。

　　VLOOKUP函数运用二：

原工作表存在通配符的查找。

　　如下图所示，原工作表A列中存在“*”通配符。

　　通过上图的源数据，要对下图的C、D、E列对应的数据进行查找，该如何设计公式呢?

　　单击C231单元格，输入以下公式：

　　=VLOOKUP（LEFT（$B231,2）&"*",$A$219:

$G$222,MATCH（C$230,$A$219:

$G$219,0）,0），然后向右和向下拉即可查找相应出相应的数据。

　　无言老师提到，还可以使用下面这样的公式，也能实现。

　　=VLOOKUP（MID（$B239,1,2）&"",$A$219:

$G$222,MATCH（C$238,$A$219:

$G$219,0）,0）

　　通过这个案例，可以看到*和的替换作用，*号替换的可以为某个文本之前或之后的所有字符，号代替的只是一个字符。

通常来说，通配符用的比较多的还是*号。

　　VLOOKUP函数运用三：

反向查找。

　　VLOOKUP函数通常只能从左往右的垂直方向有序查找。

如果需要用到逆序，反向查找就需要使用IF或CHOOSE其中一个函数嵌套使用。

这两个函数在VLOOKUP函数的使用通常是这样的形式：

IF（{1,0},查找内容的列,返回内容的列） 和 CHOOSE（{1,2,3},查找内容的列,返回内容的列-1,返回内容的列-2返回内容的列-3）,【返回内容的列需要几列就写入几列】。

　　下图所示的是A258：

F288单元格区域为源数据，为了演示需要，将其中的部分行区域隐藏了。

　　如下图所示，已知姓名列数据，使用VLOOKUP函数查找年龄和工资列的数据。

　　要完成此题，有两种方法可以实现：

　　第一，使用IF（{1,0}嵌套VLOOKUP实现。

　　单击年龄下方的I270单元格，输入公式：

　　=VLOOKUP（$H270,IF（{1,0},$B$258:

$B$288,$D$258:

$D$288）,2,0），然后下拉。

　　单击工资下方的J270单元格，输入公式：

　　=VLOOKUP（$H270,IF（{1,0},$B$258:

$B$288,$F$258:

$F$288）,2,0），然后下拉。

　　提示：

IF{1,0}函数在这里只能用到2个条件，因此使用有一定局限性。

　　下面我们就使用另外一种方法来实现，即借助CHOOSE函数。

其实IF函数可以做到的CHOOSE同样能做到，而且CHOOSE比IF更灵活。

　　第二，CHOOSE函数和VLOOKUP的嵌套使用的公式：

　　同样在年龄下方的I270单元格，输入公式：

=VLOOKUP（$H288,CHOOSE（{1,2,3},$B$259:

$B$288,$D$259:

$D$288,$F$259:

$F$288）,2,0），然后下拉。

　　单击工资下方的J270单元格，输入公式：

=VLOOKUP（$H288,CHOOSE（{1,2,3},$B$259:

$B$288,$D$259:

$D$288,$F$259:

$F$288）,3,0）。

　　如果大家对以上公式不是很理解，可以在公式栏中选中不明白的部分，按下F9键，俗称“抹黑”，查看公式对应的执行结果。

F9键在学习函数与公式中，对我们来说，有很大的帮助作用，帮助我们理解公式。

　　VLOOKUP函数运用总结：

　　第一，在引用数据区域最好使用绝对引用的方式进行。

如果对引用方式不是很清楚的朋友，可以参看混合引用方式的经典实例：

《99乘法表的制作方法》。

　　第二，对于引用查找的单元格，格式一定要和查找原表格的数据格式一致。

　　第三，如果是要从右往左查找，必须通过IF和CHOOSE等函数的配合使用才能实现。

　　本讲座有关的Excel源文件请在论坛下载：

&goto=findpost&ptid=7&pid=24&fromuid=1