Excel技巧之——LOOKUP函数示例3
LOOKUP函数主要用于在查找范围中查询指定的值,并返回另一个范围中对应位置的值,其查询原理与VLOOKUP函数和HLOOKUP函数中当第四个参数为1或true时非常相似。
示例1:无序查找
假设表格中的姓名列未进行排序,在使用VLOOKUP函数进行模糊查找时会返回#N/A错误。我们可以使用LOOKUP函数来代替进行查找。LOOKUP函数的用法主要利用了其内部数组运算的原理。
具体操作如下:
- 在B12单元格输入公式:
LOOKUP(B11, B2:D9, 2)
- 结果返回#N/A错误,表示找不到。实际上,B11的数据在数据表中确实存在,只是由于数据表姓名列未进行排序,同时VLOOKUP函数采取了模糊查找方式而导致此错误。
- 因此,可以使用LOOKUP函数来代替进行查找。
在C12单元格输入公式:LOOKUP(1, 0/(B2:B9B11), C2:C9)
,回车即可返回正确结果。
这个公式的主要原理是先比较姓名与表格中的姓名范围,如B2:B9B11
,比较结果为数组{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
。然后利用0除以这个内部数组,结果为数组{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!}
。最后在这个数组中查找数值1(参数1),返回数组中小于等于参数1的最大值(本例为0)的位置(姓名列的第7行)。最后LOOKUP函数返回参数3相同位置的数据(本例为综合部)。这种算法是LOOKUP函数在无序查找中的典型用法,目前已被广泛应用。
示例2:查找最后非空值
利用LOOKUP函数的近似查找原理,我们可以实现返回查找范围中的非空数值,而且还可以适用于二维区域或数组。
具体操作如下:
- 在I2单元格输入公式:
LOOKUP(CHAR(65535), A:A)
- 回车即可得到A列最后一个非空单元格的值。
由于LOOKUP函数必须查找尽可能大的值,因此只能使用CHAR(65535)
这个最接近最大文本的值来进行查找。
同理,在I3单元格输入公式:LOOKUP(9E 307, B:B)
,回车即可得到B列最后一个非空值(数值)。9E 307
是最接近Excel允许的最大数值的数值。
版权声明:本文内容由互联网用户自发贡献,本站不承担相关法律责任.如有侵权/违法内容,本站将立刻删除。