首页 excel笔记函数技巧正文

xlookup函数的使用技巧及实例演示

九天 函数技巧 2020-02-18 413 0 xlookup

xlookup函数是2019年8月发布的查找函数,共5个参数,语法:xlookup(查找的值,查找范围或数组,返回范围或数组,查找模式,搜索模式),其中参数123是必须项,参数45是可选项,日记坊将带给你xlookup函数的使用技巧及精彩实例;


1、常规查询

如下图所示,要根据G1的部门,在A列查询该部门,并返回B列对应的负责人姓名。公式为:=XLOOKUP(G1,A2:A11,B2:B11)

xlookup函数1.jpg

公式的意思就是在A2:A11单元格区域中查找G1单元格指定的部门,并返回B2:B11单元格区域中与之对应的姓名。


2、逆向查询

由于XLOOKUP函数的查询区域和返回区域是分开的两个参数,这样就不用考虑查询的方向问题了,不仅能实现从左到右,还能从右到左、从下到上、从上到下等任意方向的查询。

如下图所示,要根据G1的部门,在B列查询该部门,并返回A列对应的负责人姓名。公式为:=XLOOKUP(G1,B2:B11,A2:A11)

xlookup函数2.jpg


3、返回多列

如果要根据指定的查询内容,返回不同列中的内容也很简单。

如下图所示,要根据G1单元格的部门,分别返回该部门对应的姓名、日期和销售金额。公式为:=XLOOKUP(G1,A2:A11,B2:D11);

这个公式里的第三参数选择了多列的范围。由于office365中的公式有溢出功能,所以只要输入一个公式,就可以返回B~D列的多项信息了。

xlookup函数3.jpg


4、自动除错

XLOOKUP函数还自带双黄连,当查询不到内容时,可以指定返回的提示信息。

如下图,XLOOKUP函数在A列查询不到G1单元格的“大兴店”,这时候只要加上一个参数,就能让公式不再返回错误值#n/A了。公式:=XLOOKUP(G1,A2:A11,B2:D11,"无此数据");

第四参数,用于指定在查找不到结果时返回的提示内容。

xlookup函数4.png


5、近似查找

如下图,要根据F1单元格的应税所得额,在左侧的对照表中查询对应的预扣率和速算扣除数。公式为:=XLOOKUP(F2,B2:B8,C2:D8,0,-1);

XLOOKUP在B列中查询F2的值,第五参数使用-1,表示如果找不到它,就从查询区域中返回下一个较小的值。如果第五参数是1,如果找不到查询值,就返回查询区域中返回下一个较大的值。

xlookup函数5.png

这个用法还有一个比较牛掰的地方,就是查询区域不用事先排序。比如下面这个表格里,B列的数值就是乱序的:

xlookup函数6.jpg

如果查询值中使用了通配符,记得第五参数要选择2。

打赏
  • 文章发表:九天
  • 本文地址:https://rijifang.com/index.php/post/155.html
  • 声       明:转载请注明出处和附带本文链接!文章部份资料来自于网络,版权归原作者,尊重原创,注重分享;如涉版权问题,请联系本站删除!