当您使用电子表格时,有时需要匹配两个具有相同单元格的不同工作表中的数据。也许你有一份包含员工姓名和身份证号码的列表,另一份包含姓名和地址的列表,或者是一份包含流量数据和作者姓名的网络文章列表。这就是Excel的新XLOOKUP函数的作用所在。
XLOOKUP于2020年2月首次添加到Excel的当前版本中,旨在改进流行的XLOOKUPVLOOKUP函数,它也合并来自不同表的数据,但功能较差。它还执行HLOOKUP的所有操作,即在您搜索的数据所在的同一列中查找数据。
XLOOKUP相对于VLOOKUP的优点
- 可以一次复制多个列。
- 不要求引用单元格位于左边的第一个单元格
- 默认只精确匹配(VLOOKUP没有)
- 可以对不精确的匹配使用通配符吗
- 让您指定“未找到”文本时,没有匹配
- 只需要三个参数
XLOOKUP最多接受六个参数,但只需要前三个参数。格式是:
= XLOOKUP (lookup_value lookup_array return_array,(如果没有找到),[match_mode], [search_mode])
让我们从展示如何执行一个简单的XLOOKUP查询开始。
如何做一个简单的XLOOKUP查询
1.类型= XLOOKUP (进入您希望显示结果的第一个单元格。
2.单击包含lookup_value的单元格并输入逗号(您也可以键入单元格地址——例如:C2)。这是你在两张纸上检查的价值。在我们的示例中,lookup_value是单元格C2,其中包含姓氏。你也可以只把值写在引号里,但是你不能复制和粘贴一系列的行,并得到不同的匹配。
3.选择要搜索的单元格范围查找lookup_value和添加一个逗号.我们强烈建议您选择一个完整的列,而不是仅仅突出显示包含数据的单元格。这样,如果你复制粘贴,范围也会显示相同的信息。
同样,你可以手动输入范围,但用鼠标高亮显示可能是最简单的。这个列可以在同一个Excel文件的不同选项卡中,甚至在计算机上完全不同的文件中。在我们的例子中,我们在文件的Addresses选项卡上选择所有的B列,因为它也包含姓氏。
4.选择要返回的单元格范围然后添加一个右括号来完成函数调用。同样,我们建议选择完整的列。如果选择的数据值超过一列,那么第一个列之后的所有列都将复制到与XLOOKUP公式相邻的单元格中。
在我们的例子中,我们在地址表中选择了从C到E的列,这样我们就可以携带员工的电子邮件地址、性别和IP地址。我们的最终公式如下:
= XLOOKUP (C2、地址! B2: B1001,地址! C2: E1001)
你会得到如下图所示的结果。
5.复制粘贴公式插入到其他单元格中,以便跨整个行集使用它。如果将公式拖到数据的最后一行,Excel将自动用适当的行号替换查找值单元格。所以,如果你的第一个单元格是C2你把公式复制到第500行,它将是C500。
但是,如果没有为lookup_array或return_array参数选择完整列,则需要在复制和粘贴之前向单元格范围添加$符号。否则,您正在搜索的单元格范围将随着您粘贴到较低的行而改变。添加美元符号的最快方法是突出显示公式的那些部分,然后按F4键。
XLOOKUP的“If Not Found”参数
如果您没有在XLOOKUP中填写第四个参数,任何失败的搜索都会显示为#N/ a。但是,如果您想为单元格定制消息(或将其留空),只需在引号中添加一个找不到的消息。
在我们的例子中,我们使用了文本“抱歉,没有找到这个。”现在我们的公式如下:
= XLOOKUP (C2、地址! B2: B1001,地址!C2:E1001,“对不起,没有找到这个。”)
XLOOKUP匹配模式
默认情况下,XLOOKUP只返回精确匹配,因此,如果它正在搜索最后一个Symmonds,并且在另一个表上拼写为Simmonds,则没有匹配。或者,如果您正在搜索像“300”这样的数值,并且有更高或更低的数字,您将无法得到匹配。
但是,对于第5个参数,您可以选择“匹配模式”,它使用Excel搜索通配符、下一个最大或下一个最小的数字。默认模式0是精确匹配。如果没有找到完全匹配,输入-1将查找下一个最小的项。如果它找的是300,结果是200和400,它会返回200。匹配模式1给你下一个最大的数字(400)。
匹配模式2允许使用通配符进行搜索。通配符*将匹配任意数量的字符,而?字符只用于一个。如果你想找到"西蒙兹"或者"西蒙兹"用"S "吗?在第二场模式中。如果你想找到以S开头的名字,你可以在匹配模式2中使用“S*”。
XLOOKUP搜索模式
第六个也是最不重要的XLOOKUP参数控制Excel的搜索方式。对于大多数人和情况,我们会完全跳过这个参数,因为它可能不会有太大变化。
对于搜索模式,您有四个选项。模式1(默认)从搜索范围的第一行开始搜索,并找到第一个匹配项。模式1从下往上搜索。模式2(从前到后)和-2用于二进制搜索。对于二进制,需要对数据进行排序,系统将每个单元格与列中的中间值进行比较,如果不匹配,则进一步收缩搜索。老实说,我不知道你为什么需要这个,但它在庞大的数据集上可能更快。