LOOKUP函数也是excel中强大的查找函数之一,VLOOKUP函数能做到的LOOKUP函数同样可以做到,而且还可以做得更好。
LOOKUP函数有两种语法形式:向量形式和数组形式
向量形式语法:LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP的向量形式在单行区域或单列区域中查找值,然后返回第二个单行区域或单列区域中相同位置的值。
lookup_value必需。LOOKUP在第一个向量中搜索的值。Lookup_value可以是数字、文本、逻辑值、名称或对值的引用。
lookup_vector必需。只包含一行或一列的区域。lookup_vector中的值可以是文本、数字或逻辑值。
注意:lookup_vector中的值必须按升序排列:..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;否则,LOOKUP可能无法返回正确的值。文本不区分大小写。
result_vector可选。只包含一行或一列的区域。result_vector参数必须与lookup_vector参数大小相同。其大小必须相同。
注意:如果LOOKUP函数找不到lookup_value,则该函数会与lookup_vector中小于或等于lookup_value的最大值进行匹配。
如果lookup_value小于lookup_vector中的最小值,则LOOKUP会返回 #N/A 错误值。
数组形式语法:LOOKUP(lookup_value, array)
LOOKUP的数组形式在数组的第一行或第一列中查找指定的值,并返回数组最后一行或最后一列中同一位置的值。当要匹配的值位于数组的第一行或第一列中时,使用数组形式
lookup_value必需。LOOKUP在数组中搜索的值。lookup_value参数可以是数字、文本、逻辑值、名称或对值的引用。
如果LOOKUP找不到lookup_value的值,它会使用数组中小于或等于lookup_value的最大值。
如果lookup_value的值小于第一行或第一列中的最小值(取决于数组维度),LOOKUP会返回#N/A 错误值。
Array必需。包含要与 lookup_value 进行比较的文本、数字或逻辑值的单元格区域。
注意:array的数值必须按升序排列,否则函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookup_value,则查找array中小于或等于lookup_value的最大数值。如果lookup_value小于array中的最小值,函数LOOKUP返回错误值#N/A。
下面结合案例讲解LOOKUP的用法
如上图,我们需要根据姓名查找所对应的代号,公式输入:=LOOKUP(E2,$A$2:$A$10,$C$2:$C$10)
但我们发现查找出的代号是错误的,那是为什么呢?我们检查一下公式并没有问题,我们再仔细回想一下函数参数的语法,“lookup_vector中的值必须按升序排列” 所以我们需要将数据进行升序排列再使用公式查找。
经过排序之后结果就正确了。
单条件查找
使用LOOKUP函数查找的前提是对数据进行升序排列,但实际工作中很多数据都是乱序的,且不可移动数据位置,这种情况下,怎么使用LOOKUP函数查找呢?
F2单元格输入公式:=LOOKUP(1,0/($A$2:$A$10=E2),$C$2:$C$10)
公式说明:
如果A列中的姓名等于E2会返回结果TRUE,如果不等于返回结果FALSE,true和false在四则运算中等于数字1和0,所以$A$2:$A$10=E2的结果是由true和false构成的一组值。 所以这里使用0除的方法跟结果1和0相除,唯一符合条件的值为0,其他不符合的均为错误值#DIV/0!
LOOKUP函数还有一个关键的特征,就是查找时可以忽略错误值,这样错误值忽略后就只剩下一个0,这时只需要使用任意一个大于0的值来查找即可。
多条件查找
如下图所示,需要根据品牌和颜色查找出价格,实现多条件查找
G2单元格输入公式:=LOOKUP(1,0/(($A$2:$A$11=E2)*($B$2:$B$11=F2)),$C$2:$C$11)
公式说明:首先我们看看($A$2:$A$11=E2)*($B$2:$B$11=F2)运算后是什么结果, {0;0;0;0;0;0;0;1;0;0} 结果是1和0构成的一组值,再使用0除的方法跟结果1和0相除,唯一符合条件的值为0,其他不符合的均为错误值#DIV/0!
返回月份所在季度
如下图,需要根据月份返回所在季度
B2单元格输入公式:=LOOKUP(MONTH(A2),{1,4,7,10},{'一季度','二季度','三季度','四季度'})
如不使用常量数组,则需做一辅助列
数组形式
公式输入:=LOOKUP(MONTH(A2),$E$2:$F$5)
公式说明:
首先使用MONTH函数将月份提取进行查找,{1,4,7,10},{'一季度','二季度','三季度','四季度'} 这里使用常量数组,如MONTH(A2)返回12,在{1,4,7,10}中找不到,则与接近12的最小值10匹配,返回四季度。
全称查找简称
如下图,根据店面名称返回店名简称
B2单元格输入公式:=LOOKUP(0,0/FIND($E$2:$E$5,A2),$E$2:$E$5)
公式说明:
我们来看一下LOOKUP运算过程,首先FIND($E$2:$E$5,A2)表示在A2中查找$E$2:$E$5,如果查找返回在文本中的位置,如果查找不到则返回错误值。{5;#VALUE!;#VALUE!;#VALUE!}这里表示E2单元格内容在A2中的第5个字符找到,所以是5,其他找不到则返回错误值。
最后用0除的方法将5变成0,其他仍是错误值。
然后使用查找值0,查找到对应0的单元格返回店名。
联系客服