首页 函数技巧正文

经典LOOKUP函数的九种运用方法

九天 函数技巧 2019-12-18 14:00:12 122 0 lookup

今天介绍的是lookup函数,它的超级强大自是无需多言,它的参数可以是向量,也可以是二维的数组;

语法结构

(向量)

lookup(①查找值,②查找所在区域,③返回的结果)

①查找值,大家都明白;

②查找所在区域,为行或者是列,需要留意的是,需要升序排列,不然结果返回不正确;

③返回的结果,当没找到精确值时,返回小于离查找值最近的值;

(数组)

lookup(①查找值,②二维数组)

经典实例运用

套路公式

=lookup(1,0/(条件1)*(条件2)*..(条件n),目标区域或数组)

如果是使用在区间闭合方向不同的时候,可以:

=lookup(1,0/(查找值&gt;目标),返回结果)或是=lookup(1,0/(查找值<目标列),返回结果);

其中大小或是小于取决于区间闭合方向,参考文章《KPI区间闭合方向不同,如何快速取值》;

1、lookup单条件的逆向查找

公式:lookup(1,0/(目标列=查找值),返回结果),如下图,根据员工姓名逆向查工号:

lookup函数1.png

2、lookup多条件查找

公式:lookup(1,0/(条件1)*(条件2)*..(条件n),返回结果),如下图,根据姓名和部门及工号找查职务:

lookup函数2.png

①((条件1)*( 条件2)* ( 条件N)),所有条件满足返回TRUE,否则返回FALSE。

②以0/((条件1)*( 条件2)* ( 条件N))构建一个0、#DIV/0!组成的数组,避免了查找范围必须升序列排序的弊端。(因为True在运算时当作1,False在运算时当作0,所以0/TRUE返回0,0/FALSE返回#DIV/0!)

③再用1作为查找值,即可查找最后一个满足非空单元格条件的记录。

3、lookup查找最后一次出现的符合条件的数据或是文本

这个是根据上述公式题解思路的拓展,以1作为查找值,返回最后一个非空单元格,这个单元格可以是数字也可以是文本;

lookup函数3.png

4、lookup查找某列最后一个出现的文本

lookup函数4.jpg

这些特殊字符是什么意思?"々"、"龠"、"座"、"做"、"吖"、"龥"、"咗"、"9E+307"、"-9E+307"

"々"是汉字"相同"的简写,被看作是非常大的一个汉字字符,

由于"々"是特殊字符,比较难打出来,大部份人使用"座"来替代;

5、lookup查找某列最后一个数值

lookup函数5.jpg

这些特殊字符是什么意思?"々"、"龠"、"座"、"做"、"吖"、"龥"、"咗"、"9E+307"、"-9E+307"

6、查询某列中的最后一个单元格内容

lookup函数6.jpg

(A:A<>"")是判断单元格是否不为空;

7、根据客户简称查询全称

lookup函数7.jpg

公式:=iferror(LOOKUP(1,0/find(A2,D:D),D:D,"")

①0/FIND(A2,D:D),用FIND函数查询A2单元格“湖南永怡”在D列的起始位置,得到一个由错误值和数值组成的数组。

②使用ifERROR函数来屏蔽公式查询不到对应结果时返回的错误值。

8、多个区间的条件判断

lookup函数8.jpg

或者=LOOKUP(F2,$A$2:$B$8),这种方法查找区域必须升序排序。

9、提取单元格内的数字

lookup函数9.jpg

①-left(A2,row($1:$99))用LEFT函数从A2单元格左起第一个字符开始,依次返回长度为ROW($1:$99)也就是1至99的字符串,添加负号后,数值转换为负数,含有文本字符的字符串则变成错误值。

②LOOKUP函数使用1作为查询值,在由负数、0和错误值构成的数组中,忽略错误值提取最后一个等于或小于1的数值。

③最后再使用负号,将提取出的负数转为正数。

打赏
收藏日记坊