这Vlookup功能一直是Excel最强大的功能之一。它允许您在表的第一列中搜索值,并从右边的字段返回值。但是Excel还有一个名为XLOOKUP的函数,它允许您在任何列或行中搜索值,并从任何其他列返回数据。
Xlookup如何工作
XLOOKUP函数比VLOOKUP函数更容易使用,因为您可以指定整个范围,而不是为结果列指定一个值。
该函数还允许您搜索列和行,找到相交单元的值。
XLOOKUP功能参数如下:
= xlookup(lookup_value,lookup_array,return_array,[match_mode],[search_mode])
- lookup_value:您要搜索的价值
- lookup_array:您要搜索的数组(列)
- return_array.:要从中检索值的结果(列)
- match_mode(可选):选择精确匹配(0),精确匹配或下一个最小值(-1),或通配符匹配(2)。
- search_mode(可选):选择从列(1)的第一项开始,从列(-1)的最后一项开始,从二进制升序查找(2),从二进制降序查找(-2)。
以下是您可以使用XLookup函数执行的最常见查找中的一些。
如何使用xlookup搜索单个结果
使用XLookup的最简单方法是使用从一列的数据点搜索单个结果。
-
此示例电子表格是销售代表提交的订单列表,包括商品、单位数量、成本和总销售额。
-
如果您想在特定销售代表提交的列表中找到第一个销售,您可以创建一个XLookup函数,用于搜索REP列的名称。该函数将从总列返回结果。xlookup函数是:
= XLookup(I2,C2:C44,G2:G44,0,1)
- I2:指出代表的名字搜索单元格
- C2: C44:这是REP列,它是查找阵列
- G2: G33:这是总列,即返回数组
- 0.:选择完全匹配
- 1:在结果中选择第一个匹配项
-
当你按下进入并输入销售代表的名称,Total结果单元格将显示表格中该销售代表的第一个结果。
-
如果您想搜索最近的销售(由于表格以相反顺序按日期订购),请将最后一个XLookup参数更改为-1,它将从查找数组的最后一个单元格开始搜索,并将结果提供给您。
-
此示例显示了您可以的类似搜索vlookup函数,使用Rep列作为查找表的第一列。但是,XLOOKUP允许您在任意方向上搜索任何列。例如,如果您想找到当年销售第一个Binder订单的销售代表,您可以使用以下XLOOKUP函数:
= XLOOKUP (I2, D2: D44 C2: C44, 0, 1)
- D2:指向项目搜索单元格
- D2:D44:这是Item列,它是查找数组
- C2: C44:这是Rep列,它是查找数组左边的返回数组
- 0.:选择完全匹配
- 1:在结果中选择第一个匹配项
-
这一次,结果将是销售本年度第一个活页夹订单的销售代表的名字。
使用XLookup执行垂直和水平匹配
Xlookup的另一个能力,Vlookup无法能够执行垂直和水平搜索的能力,这意味着您也可以在列中搜索一个项目,以及跨行的项目。
-
在以下示例电子表格中,每个销售代表的销售逐季度分开。如果您希望看到特定销售代表的第三季度销售,则没有Xlookup功能,这种搜索将很难。
-
使用XLookup功能,这种搜索很容易。使用FOLLOING NEXTED XLOOKUP功能,您可以搜索特定销售代表的第三季度销售:
= XLookup(J2,B2:B42,XLookup(K2,C1:H1,C2:H42))
- J2.:指向代表搜索单元格
- B2:B42:这是Item列,它是列查找数组
- K2:指向Quarter搜索单元格
- C1:H1:这是行查找阵列
- C2:H42:这是每季度美元金额的查找阵列
这个嵌套的XLOOKUP函数首先标识销售代表,下一个XLOOKUP函数标识所需的季度。返回值将是这两个截取的单元格。
-
这个公式的结果是汤普森名称代表的季度盈利。
使用xlookup函数
XLOOKUP功能只对Office Insider的订户可用,但很快就会向所有微软365的订户推出。
如果您想自己测试函数,您可以成为办公室内幕。选择文件>帐户,然后选择办公室内部下拉以订阅。
加入Office Insider Program后,您已安装的Excel版本将收到所有最新更新,并且可以使用XLookup功能开始。