Excel XLOOKUP函数:All You Need to Know (10 example)

视频- Excel XLOOKUP函数(10个XLOOKUP示例)

excel.XLOOKUP函数终于到了。

如果您已经使用VLookup或索引/匹配,我相信您将热爱XLookup功能提供的灵活性。

在本教程中,我将介绍关于XLOOKUP函数的所有知识,并提供一些示例,帮助您了解如何最好地使用XLOOKUP函数。

让我们开始吧!

XLOOKUP是什么?

xlookup是一个新功能是办公室365,是一个新的和改进的版本vlookup./HLOOKUP功能。

它可以做VLOOKUP所做的一切,甚至更多。

XLookup是一种功能,允许您快速查找数据集中的值(垂直或水平),并在某些其他行/列中返回相应的值。

例如,如果您在考试中获得了学生的分数,您可以使用XLookup快速检查学生使用学生的名称进行评分多少。

当我深入研究一些时,这个函数的威力将变得更加清晰XLOOKUP例子在本教程的后面部分。

但是在进入示例之前,有一个大问题—如何访问XLOOKUP?

如何访问Xlookup?

到目前为止,XLOOKUP只对Office 365的用户可用。

所以,如果你使用的是之前的Excel版本(2010/2013/2016/2019),你将无法使用这个功能。

我也不确定以前的版本是否会发布这个插件(也许微软可以创建一个插件,就像他们为Power Query所做的那样)。但到目前为止,你只有在使用Office 365时才能使用它。

点击此处升级到Office 365

如果你已经使用了Office 365(家庭版、个人版或大学版),但还不能使用它,你可以到“文件”选项卡,然后点击“帐户”。

会有一个办公室内部程序,你可以点击并加入办公室内部程序。这将使您能够访问XLOOKUP函数。

办公室内部选择在帐户

我希望XLOOKUP很快就能在所有Office 365版本上使用。

注意:XLOOKUP也适用于Mac版Office 365和Web版Excel(在线Excel)

XLOOKUP功能语法

下面是XLOOKUP函数的语法:

= xlookup(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Excel中的XLOOKUP语法

如果您使用过VLookup,则会注意到语法非常相似,当然有一些很棒的其他功能。

如果语法和参数看起来太多,不要担心。在本教程的后面,我将通过一些简单的XLOOKUP示例来介绍这些内容,这样会更加清楚。

XLOOKUP函数可以提供6个参数(3个强制参数和3个可选参数):

  1. lookup_value- 您正在寻找的价值
  2. lookup_array-在其中查找查找值的数组
  3. return_array.- 要从中获取和返回值的数组(对应于找到查找值的位置)
  4. (if_not_found)-当查找值没有找到时返回的值。如果您没有指定这个参数,将返回一个#N/ a错误
  5. [match_mode]- 在这里您可以指定所需的匹配类型:
    • 0.- 完全匹配,其中lookup_value应该与Lookup_Array中的值完全匹配。这是默认选项。
    • 1- 查找完全匹配,但如果找到,则返回下一个较小的项目/值
    • 1- 查找完全匹配,但如果找到,则返回下一个更大的项目/值
    • 2- 使用通配符(*或〜)进行部分匹配
  6. (search_mode) -这里指定XLOOKUP函数应该如何搜索lookup_array
    • 1- 这是默认选项,其中函数开始从顶部(第一个项目)到Lookup_Array中的底部(第一个项目)的Lookup_Value
    • 1-从下到上搜索。当您想要查找lookup_array中的最后一个匹配值时非常有用
    • 2 -执行二分搜索,其中数据需要按升序排序。如果没有排序,可能会给出错误或错误的结果
    • 2—对需要降序排序的数据进行二分查找。如果没有排序,可能会给出错误或错误的结果

XLOOKUP函数示例

现在让我们进入有趣的部分——一些实用的XLOOKUP示例。

这些示例将帮助您更好地理解XLOOKUP的工作方式、它与VLOOKUP和INDEX/MATCH的不同之处以及该函数的一些增强和限制。

点击这里下载示例文件并继续学习

示例1:获取查找值

假设您有以下数据集,并且希望为Greg获取数学分数(查找值)。

简单查找 - 使用名称获取学生的分数

以下是以下的公式:

= XLOOKUP (F2 A2: A15, B2:去往B15)

xlookup函数要做一个简单的查找

在上面的公式中,我刚刚使用了强制参数,其中它查找名称(从上到下),找到精确匹配,并从B2:B15返回相应的值。

XLOOKUP和VLOOKUP函数的一个明显区别是它们处理查找数组的方式。在VLOOKUP中,您有整个数组,其中查找值位于最左边的列中,然后您指定要从其中获取结果的列号。另一方面,XLOOKUP允许分别选择lookup_array和return_array

一个即时的好处,让lookup_array和return_array作为单独的参数意味着现在您可以向左看。VLOOKUP有这样的限制,您只能查找并找到右侧的值。但是有了XLOOKUP,这种限制就消失了。

这是一个例子。我有相同的数据集,其中名称在右侧,返回_返回左侧。

数据集在右侧的名称

以下是我可以用来获得Math的Greg的分数(这意味着向Lookup_Value的左侧查看)

= XLOOKUP(F2,D2:D15,A2:A15)

XLOOKUP函数从查找值的左侧获取值

XLookup解决了另一个主要问题 - 如果您插入新列或移动列,则结果数据仍然是正确的。VLOOKUP可能会在此情况下突破或给出不正确的结果,因为大多数列索引值是硬编码的。

示例2:查找并获取整个记录

让我们以相同的数据为例。

在这种情况下,我不想只获取格雷格的数学成绩。我想知道所有科目的成绩。

获取查找值的所有记录的数据

在这种情况下,我可以使用以下公式:

= XLOOKUP (F2 A2: A15, B2: D15)

XLOOKUP公式获取整个记录

上面的公式使用的return_array范围超过了一个列(B2:D15)。因此,当在A2:A15中找到查找值时,公式将从return_array返回整个行。

另外,不能只删除自动填充的数组中的单元格。在本例中,不能删除H2和I2。如果你去尝试,什么也不会发生。如果您选择了这些单元格,公式栏中的公式将会变成灰色(表示不能更改)。

您可以删除单元格G2(我们最初输入它的地方)中的公式,它将删除整个结果。

这是一个有用的增强功能,如vlookup更早,您必须为每个公式单独指定列号。

示例3:使用XLOOKUP的双向查找(水平和垂直查找)

以下是我想知道Math中Greg的得分(小区G2中的主题)的数据集。

简单查找 - 使用名称获取学生的分数

这可以使用双向查找完成,其中我在第1行中查找列中的名称和第1行中的主题名称。此双向查找的好处是结果与主题名称的学生名称无关。如果我将主题名称更改为化学,这双向Xlookup公式仍然可以工作并给我正确的结果。

以下是将执行双向查找的公式并提供正确的结果:

= XLOOKUP (G1, B1: D1, XLOOKUP (F2 A2: A15, B2: D15))

使用xlookup两种方式查找

此公式使用嵌套的XLookup,首先我将它用来获取小区F2中学生的所有标记。

所以XLOOKUP(F2,A2:A15,B2:D15)的结果是{21,94,81},这是Greg在本例中得分的一个数组。

然后在外部XLOOKUP公式中再次使用它作为返回数组。在外部XLOOKUP公式中,我查找主题名称(在单元格G1中),查找数组是B1:D1。

如果主题名称是Math,则外部XLOOKUP公式将从返回数组中获取第一个值——在本例中为{21,94,81}。

直到现在,这与现在实现相同索引和匹配组合

点击这里下载示例文件并继续学习

示例4:没有找到查找值(错误处理)

误差处理现在已添加到XLookup公式中。

XLOOKUP函数中的第四个参数是[if_not_found],您可以在这里指定您想要的内容,以防找不到查找内容。

假设您有数据集,如下所示,在匹配的情况下要获取数学分数,而在找不到名称的情况下,您想要返回 - '没有出现'

数据集使用名称获取查找值

以下公式将这样做:

= XLookup(F2,A2:A15,B2:B15,“没有出现”)

使用XLOOKUP进行错误处理

在这种情况下,我有硬编码我想要遇到的内容,以防没有匹配。你也可以使用一个单元格引用到细胞或公式。

示例5:嵌套XLookup(在多个范围内查找)

使用[if_not_found]参数的天才之处在于它允许您使用嵌套XLOOKUP公式

例如,假设您有两个单独的列表,如下所示。虽然我在同一表上有这两个表,但您可以在单独的纸张甚至工作簿中拥有这些表。

用于查找的两个表

下面是嵌套的XLOOKUP公式,它将检查两个表中的名称并从指定列返回相应的值。

= XLookup(A12,A2:A8,B2:B8,Xlookup(A12,F2:F8,G2:G8))

嵌套Xlookup公式

在上述公式中,我使用了[if_not_found]参数来使用另一个xlookup公式。这允许您在同一公式中添加第二个XLookup,并使用单个公式扫描两个表。

我不确定在一个公式中可以使用多少个嵌套的xlookup。我试了10点,还行,然后就放弃了

例6:查找最后的匹配值

这是迫切需要的,XLOOKUP使其成为可能。现在,您不需要寻找复杂的方法来获取范围中的最后一个匹配值。

假设您有如下所示的数据集,您想要检查每个部门最后招聘的人员是什么时候以及招聘日期是什么时候。

要获取最后一个查找值的数据

下面的公式将查找每个部门的最后一个值,并给出最后一个雇员的姓名:

= Xlookup(F1,$ 2 $ 2:$ 15 $ 15,$ 2 $ 2:$ 15 ,,,  -  1)

下面的公式将给出每个部门最后一次招聘的日期:

= Xlookup(F1,$ B $ 2:$ B $ 15,$ C $ 2:$ C $ 15 ,,,  -  1)

xlookup公式获取最后一个匹配值

由于XLookup具有内置功能,以指定查找的方向(首先持续或持续到第一个),因此使用简单的公式完成。使用垂直数据,Vlookup和索引/匹配始终从上到下看,但使用XLookup并可以将方向指定为底部到上面。

示例7:与Xlookup近似匹配(查找税率)

XLookup的另一个显着改进是现在有四种匹配模式(Vlookup有2个并且匹配有3)。

你可以指定四个参数中的任何一个来决定查找值应该如何匹配:

  • 0.- 完全匹配,其中lookup_value应该与Lookup_Array中的值完全匹配。这是默认选项。
  • 1- 查找完全匹配,但如果找到,则返回下一个较小的项目/值
  • 1- 查找完全匹配,但如果找到,则返回下一个更大的项目/值
  • 2- 使用通配符(*或〜)进行部分匹配
但最好的部分是您不需要担心您的数据是否按升序或降序排序。即使数据没有排序,XLookup也会照顾它。

下面我有一个数据集,我想找到每个人的佣金-和佣金需要使用右边的表格计算。

近似匹配查找数据来查找佣金

下面是这个公式:

= Xlookup(B2,$ E $ 2:$ E $ 6,$ F $ 2:$ 6,0,-1)* B2

XLOOKUP近似匹配

这只是使用销售价值作为查找,并通过右侧的查找表查看。In this formula, I have used -1 as the fifth argument ([match_mode]), which means that it will look for an exact match, and when it doesn’t find one, it will return the value just smaller than the lookup value.

就像我说的,你不需要担心你的数据是否被排序。

点击这里下载示例文件并继续学习

例8:水平查找

Xlookup可以做垂直以及水平查找。

下面我有一个数据集,其中我有学生的名字和他们的分数在行,我想获取在单元格B7名称的分数。

水平查找数据

以下公式将这样做:

= XLOOKUP (B7 B1: O1, B2: O2)

XLOOKUP公式来执行水平查找

这只是简单的查找(类似于我们在示例1中看到的),而是水平的。

我所介绍的所有关于垂直查找的示例都可以使用XLOOKUP(告别VLOOKUP和HLOOKUP)进行水平查找。

示例9:条件查找(使用XLOOKUP和其他公式)

这是一个稍微高级的示例,它还展示了XLOOKUP在需要进行复杂查找时的强大功能。

下面是一个数据集,我有学生的名字和他们的成绩,我想知道每门课得分最高的学生的名字和每门课得分超过80分的学生的数量。

要获取最高分和超过80的数据

下面是每门课得分最高的学生的姓名公式:

= XLOOKUP (MAX (XLOOKUP (G1, B美元1:$ D $ 1, B 2:美元D 15美元)),XLOOKUP (G1, B美元1:$ D $ 1, B 2:美元D 15美元),一个2:美元$ 15美元)

由于XLookup可以用来返回整个数组,因此我使用它首先获得所需主题的所有标记。

例如,对于数学,当我使用Xlookup时(G1,$ B $ 1:$ D $ 1,$ B $ 2:$ D $ 15),它给了我数学中的所有分数。然后,我可以使用MAX功能查找此范围内的最大分数。

此最大分数然后成为我的查找值,查找范围将是Xlookup返回的数组(G1,$ B $ 1:$ D $ 1,$ B $ 2:$ D $ 15)

我在另一个XLOOKUP公式中使用它来获取获得最高分数的学生的名字。

要计算超过80的学生人数,请使用以下公式:

= Countif(Xlookup(G1,$ B $ 1:$ D $ 1,$ B $ 2:$ d $ 15),“> 80”

公式取得分最高的名字

这个例子简单地使用XLOOKUP公式获取给定主题的所有值的范围。然后,它将其包装在COUNTIF函数中,以获得超过80的分数。

示例10:在Xlookup中使用通配符

就像您可以在VLOOKUP和MATCH中使用通配符一样,您也可以通过XLOOKUP实现这一点。

但是有一个区别。

在XLookup中,您需要指定您正在使用通配符(第五个参数中的字符)。如果您未指定此,XLookup会给您错误。

以下是我有公司名称及其市场资本化的数据集。

通配符查找数据集

我想在第d栏中查找公司名称,并从左侧的表格中获取市场资本化。并且由于列D中的名称并不完全匹配,因此我将不得不使用通配符。

下面是这个公式:

= XLOOKUP(“*”d2“*”,一个美元2:一个11美元,B美元2:11美元,美元,2)

XLOOKUP通配符查找

在上述公式中,我在D2之后使用星号(*)通配符(它需要在双引号内并使用Ampersand与D2连接)。

这告诉公式查看所有单元格,如果单元格D2(即Apple)中包含单词,则认为它完全匹配。无论单元格D2的文本前后有多少字符。

为了确保XLOOKUP接受通配符,第五个参数被设置为2(通配符匹配)。

示例11:查找列中的最后一个值

由于XLOOKUP允许从下到上进行搜索,因此可以很容易地找到列表中的最后一个值,并从列中获取相应的值。

假设您有一个如下所示的数据集,您想知道最后一家公司是什么,以及最后一家公司的市值是多少。

获取列表中的最后一项

下面的公式会给你最后一家公司的名字:

= XLOOKUP(“*”,A2: A11 A2: A11,, 2, 1)

下面的公式给出了列表中最后一家公司的市值:

= XLookup(“*”,A2:A11,B2:B11,2,-1)

xlookup公式获取列表中的最后一个项目

这些公式同样使用通配符。在这些示例中,我使用星号(*)作为查找值,这意味着它将把遇到的第一个单元格视为精确匹配(因为星号可以是任意字符和任意数量的字符)。

由于方向是从下到上(对于垂直排列的数据),它将返回列表中的最后一个值。

而第二个公式以来使用单独的return_range来获取列表中姓氏的市场概要。

点击这里下载示例文件并继续学习

如果你没有Xlookup怎么办?

由于XLOOKUP可能只对Office 365用户可用,获得它的一种方法是升级到Office 365。

如果您已经拥有Office 365家庭版、个人版或大学版,那么您已经可以访问XLOOKUP。您所需要做的就是加入Office Insider程序。

要执行此操作,请转到“文件”选项卡,单击“帐户”,然后单击“Office Insider”选项。将有一个选择加入Insider计划。

办公室内部选择在帐户

如果你有其他的Office 365订阅(比如Enterprise),我确信XLOOKUP和其他很棒的特性(比如动态数组,公式,比如SORT和筛选)将很快提供。

如果您使用Excel 2010/2013/2016/2019,您将不会有XLookup,并且您必须继续使用VLookup,Hlookup和指数/匹配组合充分利用查找公式。

XLOOKUP向后兼容性

这是需要注意的一点——XLOOKUP是不向后兼容的

这意味着如果您创建文件并使用XLookup公式,然后在没有XLookup的版本中打开它,它将显示错误。

由于XLOOKUP是朝着正确方向迈出的一大步,我相信它将成为默认的查找公式,但它肯定需要几年的时间才能被广泛采用。毕竟,我仍然看到一些人在使用Excel 2003。

这是11个XLOOKUP示例,可以帮助您更快地完成所有查找和引用操作,并使其易于使用。

希望你发现本教程很有用!

你也可能喜欢下面的Excel教程:

Excel电子书订阅

免费的EXCEL的书

获得51个Excel提示电子书来飙升您的生产力,并更快地完成工作

9关于“Excel Xlookup函数的想法:您需要知道的所有内容(10示例)”

  1. 你好,umit,
    我想在一个细胞里完成“物理,化学,数学”的集合。如何使用xlookup实现这一点?

    我试着用" * "来做,但它会从三列中选出第一列的值。我使用这个公式:=SUM(XLOOKUP(G4,B4:B15,XLOOKUP(“*”,C3:E3,C4:E15,,2)))
    上面的公式是从C4到C15的范围,但我希望根据G4查找,会出现类似“C4到E4”的情况。

    请帮助

  2. 有没有办法找到新功能的抄写,所以我应该能够在旧版本上制作它

  3. Sumit,你的网站知识丰富!!
    我有一个问题,当我打开我们的文件在我的excel &打开xlookup的函数参数在你的公式,它是给e名错误#名
    甚至在我的另一个文件中,公式不工作,请暗示如何处理它。

  4. 嗨,苏米特
    Xlookup的大展示。我尝试从其他来源获取指示,但是没有以清晰逻辑格式解释这一点。
    做得好!

    • 感谢您的评论Wilfred ......很高兴您发现本教程有用!

  5. 这对数据搜集者来说是个好消息。当我知道Xlookup现在已经存在时,我感到很尴尬。谢谢你的见解和例子,真的详细描绘了如何利用这个新公式。

    • I am equally embarrassed I subscribed to the O365 home version and got instant access to these new functions/features. It’s been many days, and I am still experimenting with these new functions (all my latest videos and have been about these only).

      动态阵列真的改变了我们在Excel中工作的方式,并制作了一些用于拍摄大量复杂公式的东西真的很容易。

评论都关门了。