Excel中比Vlookup强大的查找函数Xlookup常用用法

上一章介绍了常用查询函数Vlookup，在使用中大家会发现这个函数存在一些缺陷，比如反向查找和多条件查找时需要手动构造查找范围、无法忽略错误值、只能返回第一个被找到的值等。今天，我们一起来看看Vlookup的升级版Xlookup，看看它如何解决处理这些问题。

Xlookup函数语法：=Xlookup(查找值，查找区域，要返回的区域，[如果找不到时返回的结果]，[匹配方式]，[搜索模式])

XLOOKUP，可以在一列中查找，并从另一列中的同一行返回结果，而不管返回列位于查找列的哪一侧。

通过几个案例一起见识一下Xookup的强大之处。

案例1：查找葡萄的单价

在L5单元格中输入，=XLOOKUP(K5,D4:D14,E4:E14)。

Xlookup在查找区域中找到葡萄在第3行，并把要返回的区域的第3行的值作为结果返回。

案例2：查找白菜的销售总额

这种需求，如果用Vlookup也能解决，可以用IF构造一个数组，返回白菜的单价和销量，外面再嵌套一个PRODUCT求乘积。Xlookup就比较直接、简单，一个函数搞定。

在L6单元格中输入，=XLOOKUP(K6,D4:D14,E4:E14*F4:F14)。找到白菜的位置，返回单价*销量的结果数组对应的行。

案例3：查找香蕉的产品编码

要返回的区域位于查找区域的左侧，遇到这种情况，Vlookup就需要手动构造数组做为返回区域，之前分享了2种方法，IF和CHOOSECOLS，比较麻烦。Xlookup不用考虑查找区域与返回区域的位置问题。

在L7单元格中输入，=XLOOKUP(K7,D4:D14,B4:B14)。

案例4：查找水果品类下西红柿的销量

多条件查找也很简单，用&把查找区域按条件顺序连接起来就可以了。

在L7单元格中输入，=XLOOKUP(K8,C4:C14&D4:D14,F4:F14)。

案例5：查找黄瓜的销量

在产品名称中并不存在黄瓜，如果使用Vlookup就会返回一个#VALUE!错误，但有些时候，不想看到错误，想返回一个提示，比如“未找到“，Vlookup就需要在外面嵌套一个IFERROR函数。看Xlookup如何解决？

在L8单元格中输入，=XLOOKUP(K9,D4:D14,F4:F14,"未找到")。是不是特别强大！。

这里使用了Xlookup的第4个参数，如果找不到时返回的结果，自定义找不到时的返回结果，可以是文本、数字、逻辑值等。

三、近似匹配

案例6：查找产品的提成比例

在G4单元格中输入，=XLOOKUP(F4,$N$5:$N$8,$O$5:$O$8,,-1)，双击填充。

这里使用了Xlookup的第5参数。第5参数有4个选项：

0 - 完全匹配。如果未找到，则返回 #N/A。这是默认选项。

-1 - 完全匹配。如果没有找到，则返回下一个较小的项。

1 - 完全匹配。如果没有找到，则返回下一个较大的项。

2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。

本案例中第5参数选择-1，这意味着函数将查找完全匹配项，如果找不到匹配项，则返回下一个较小的项。

案例7：查找以"油"开头的产品单价

在L11单元格中输入，=XLOOKUP(K11&"*",D4:D14,E4:E14,,2)，第5参数选择2，意味着使用通配符匹配。

案例8：查找葡萄的最后一笔的销量

在Lookup兄弟群中，只Lookup是从下往上查找的，用LOOKUP(1,0/(D4:D15=K12),F4:F15)，可以完成这个案例，但多一种方法，多一种选择，看看Xlookup怎么搞定。

查找最后一笔，就需要从下往上搜索，找到第一个葡萄的销量，这里就要使用Vlookup的第6参数。

第6参数有4个选项，今天就介绍前2个：

1 - 从第一项开始执行搜索。这是默认选项。

-1 - 从最后一项开始执行反向搜索。

在L12单元格中输入，=XLOOKUP(K12,D4:D15,F4:F15,,,-1)，个人觉得这公式更直观、更容易理解。

案例9：根据选择的产品编码和查找的字段，返回对应的结果。

要返回的区域是不固定的，根据选择的查找字段确定，这个问题，使用Vlookup+Match可以搞定，今天要分享的是Xlookup+Xlooup嵌套的方法。

在K17单元格中输入，=XLOOKUP(I17,B4:B15,XLOOKUP(J17,C3:F3,C4:F15))。

第二个Xlookup根据选择的字段，返回对应的列，做为第一个Xlookup的第三参数。

关注我，学习更多办公技巧！