Excel 中强大的查询函数:XLOOKUP

原文标题:《比 Vlookup 更逆天!这个强大的查询函数,看完我就跪了!》

我们今天来讲讲 XLOOKUP,一个强大的查询函数!

在说 XLOOKUP 函数之前,咱们先看看下面这个案例,根据姓名查找对应的性别。

这个问题太常见了!首先想到的就是用 VLOOKUP 函数。

=VLOOKUP(E2,A2:C7,2,0)

在查找区域 A2:C7 的首列找到 E2 单元格的值「王五」,返回查找区域 A2:C7 第 2 列与之对应的值「男」。

初识 XLOOKUP 函数

再来看看 XLOOKUP 函数的用法:

=XLOOKUP(查找的值,查找范围,结果范围)

公式就可以这样写:

=XLOOKUP(E2,A2:A7,B2:B7)

在查找范围 A2:A7 中找到 E2 单元格的值「王五」,返回 B2:B7 对应的值「男」。

如果姓名这列不是在前面,用 VLOOKUP 函数似乎就不太合适了。因为 VLOOKUP 函数的规则是在查找区域的首列查找。

这种情况下,我们通常会用 INDEX 函数和 MATCH 函数组合写公式:

=INDEX(A2:A7,MATCH(E2,B2:B7,0))

MATCH 函数找出 E2 的值「王五」在 B2:B7 中是第几行,得到结果 3,然后用 INDEX 函数将 A2:A7 的第 3 行的值引用出来,得到结果「男」。

而 XLOOKUP 函数就不一样了,它不会像 VLOOKUP 那样受位置的影响,依然照用不误:

=XLOOKUP(E2,B2:B7,A2:A7)

在查找区域 B2:B7 中找到 E2 单元格的值「王五」,返回 A2:A7 对应的值「男」。

XLOOKUP 函数的第 4 参数

经常有小伙伴提这样的问题,如何让 VLOOKUP 查找不到的数据返回为空值?

如下图,VLOOKUP 函数在查找区域 A2:B7 的首列没有找到单元格的值「孙二」, 就会返回错误值#N / A。

=VLOOKUP(D2,A2:B7,2,0)

通常我们都会在 VLOOKUP 函数外层嵌套 IFERROR 函数,或者用 IFNA 函数来容错。

=IFNA(VLOOKUP(D2,A2:B7,2,0),"")

而 XLOOKUP 函数有它专属的参数:它的第 4 个参数专门负责容错。

=XLOOKUP(查找值,查找范围,返回范围,[容错])

这个参数是非必需参数,当你碰到上面这种问题,才有必要把它请出来。

公式就可以写成:

=XLOOKUP(D2,A2:A7,B2:B7,"")

在查找范围 A2:A7 中找 E2 单元格的值「孙二」,如果有找到就返回 B2:B7 对应的值,如果没有找到,则返回第 4 参数指定的内容「""」。

当然第 4 参数的设定并非只可以是字符串,数值。也可以嵌套其它的公式返回结果。

XLOOKUP 函数的第 5 参数

以下是评定的规则:

小于 60 分不合格;

大于等于 60 小于 70 为合格;

大于等于 70 小于 80 为良好;

大于等于 80 为优秀。

先为每个等级设置分数的下限,如下图 A 列,然后在 E2 单元格写入公式:

=XLOOKUP(D2,A2:A5,B2:B5,,-1)

XLOOKUP 函数的第 5 参数是匹配类型。

=XLOOKUP(查找值,查找范围,返回范围,[容错], [匹配类型])

当第 5 参数的值为-1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较小的值。

如上面的公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 小的值,即 70。再返回 B2:B5 对应的等级「良好」。

如果把 A 列的分数下限改成上限,公式就可以这样写:

=XLOOKUP(D2,A2:A5,B2:B5,,1)

当第 5 参数为 1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较大的值。

如公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 大的值,即 79。再返回 B2:B5 对应的等级「良好」。

写到最后

关于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些区别,以下 Tips 供大家参考:

❶ VLOOKUP 函数必须在查找区域的首列查找,而 XLOOKUP 函数不受这种位置限制;

❷ VLOOKUP 函数需要其它函数嵌套来容错,而 XLOOKUP 函数有自己的参数做容错处理,更方便;

❸ LOOKUP 函数在多值判断时,需要升序排序,而 XLOOKUP 函数可以不用排序。

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳,编辑:竺兰

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享