XLOOKUP函数，查找之王

VLOOKUP和她的继任者

VLOOKUP函数，相信是很多小伙伴学习Excel时正儿八经学的第一个函数，她强大，易学，好用，陪伴我们做了一个又一个的报表，度过了一个又一个的加班。。。

但是，VLOOKUP毕竟是有着二十多年高龄的老函数了，长久以来，有以下几个问题一直被用户诟病：

• 只能从左往右查找，目前解决的办法有使用数组公式（数据多了会卡），增加辅助列（表增加一列，浪费空间），更改索引列的位置（有时候没法这么做）等各种治标不治本的办法。

• 只能从上往下找，有的特殊需求需要从下往上，就得改用LOOKUP了。

• 查不到的时候会返回错误符号，导致这一列无法做算数运算，需要外面嵌套一个IFERROR函数解决。

• 无法一对多查找，比如锅包肉的原料是猪里脊，淀粉，葱丝姜丝萝卜丝，按照VLOOKUP的逻辑只会返回排在最上面的猪里脊。

• 模糊匹配基本上没用，VLOOKUP函数的第四参数为1时，可以进行模糊匹配，但实际使用中往往会返回完全不相干的结果，导致大部分场合下第四参数默认就是0，模糊匹配则使用通配符，ISNUMBER嵌套FIND等方式解决。

在这样的背景下，XLOOKUP函数横空出世，相比老前辈VLOOKUP，她有如下优点：

• 支持双向查找：

  XLOOKUP 不仅可以像VLOOKUP那样从左到右查找，还可以从右到左查找（即返回查找范围中的任意一列的值），这使得它可以更加灵活地使用。

• 支持模糊匹配：

  XLOOKUP 支持精确匹配和近似匹配。在近似匹配的情况下，XLOOKUP 可以选择最接近的匹配项，而不仅仅是第一个匹配项。

• 可返回不存在时的默认值：    

  XLOOKUP 允许用户指定当没有找到匹配项时返回的默认值，这样可以避免出现 #N/A 错误。

• 更好的错误处理：

  XLOOKUP 可以更容易地处理各种错误情况，例如找不到匹配项或有多个匹配项时。

• 无需排序：

  对于近似匹配，VLOOKUP 要求查找范围的第一列必须按升序排序。而XLOOKUP 则不需要这种排序，即使使用近似匹配也是如此。

• 更简洁的语法：

  XLOOKUP 的语法更简洁，参数更直观，易于理解和使用。

• 支持多表查找：

  XLOOKUP 可以直接在多个表之间进行查找，而不需要额外的操作或函数。

• 性能优化：

  在某些情况下，XLOOKUP 可能比VLOOKUP 更快，尤其是在处理大型数据集时。

XLOOKUP的参数

XLOOKUP函数的参数如下：

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

lookup_value

要搜索的值，比如用身份证号去花名册中查某一个人的名字、性别、年龄、学校。。。那么身份证号就是lookup_value

lookup_array

要搜索的数组或区域，接上面的例子，花名册中身份证号那列就是lookup_array    

return_array

要返回的数组或区域，接上面的例子，花名册中要查的名字、性别、年龄、学习。。。这些列就是return_array，注意和VLOOKUP不同的是，XLOOKUP可以以数组形式一次返回多个值

if_not_found

如果未找到有效的匹配项，则返回本参数中提供的值，此参数可以省略，省略后如果未找到有效的匹配项，会返回#N/A

match_mode

指定匹配类型：

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

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

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

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

search_mode

指定要使用的搜索模式：

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

-1

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

2  执行依赖于 lookup_array 按升序排序的二进制搜索。如果未排序，将返回无效结果。

-2 执行依赖于 lookup_array 按降序排序的二进制搜索。如果未排序，将返回无效结果。

XLOOKUP示例    

基本查找

如上图，实现了最简单的查找，其中第三参数使用相对引用，向右填充公式，就搜索到了所有信息

             

也可以这样写第三参数，这样会以数组形式返回全部信息，就不用向右填充了（如果是多个人，向下填充还是要的）

查不到时返回特定值

   

按上面的例子，如果查不到的话，会返回#N/A，如果不想这样，可以填写第四参数，如下图，查不到的情况下会返回第四参数的值

通配符模糊查找

第五参数填2，可以使用通配符模糊查找模式，如下图

   

因为第六参数省略，所以默认是从上往下找，找到了李艳的信息，如果想从下往上找到李红霞的信息，可以设置第六参数为-1，如下图

多条件查找

如上图，使用连接符"&"，可以实现多条件查找，注意顺序要一致，如第一参数是”周杰伦&三年二班“，那么第二参数就应该是”姓名列&班级列“  

End