PMC函数系列001-XLOOKUP

最新版本的Excel更新了好多新函数，新函数在PMC排程中，可以用到地方非常多，特别是XLOOKUP函数，此函数可以向右查询，向左查询，批量查询等；可以替代的函数有：

VLOOKUP、HLOOKUP、以及经典的IFERROR+VLOOKUP 屏蔽错误的经典用法

• 函数参数

因为功能强大，所以此函数的参数有多个，其中3个必需，3个可选，来自官方的解释：

XLOOKUP 函数搜索区域或数组，然后返回与它找到的第一个匹配项对应的项。 如果不存在匹配项，那么 XLOOKUP 可以返回最接近 () 匹配项。

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

参数	说明
lookup_value 必需*	要搜索的 值 *如果省略，XLOOKUP 将返回在lookup_array中找到 的空白单元格。
lookup_array 必需​	要搜索的数组或区域
return_array 必需​​	要返回的数组或区域
[if_not_found] 可选	如果找不到有效的匹配项，请返回你提供的 [if_not_found] 文本。 如果找不到有效的匹配项，并且 [if_not_found] 缺失， 则返回#N/A 。
[match_mode] 可选	指定匹配类型： 0 - 完全匹配。 如果未找到，则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到，则返回下一个 较小的。 1 - 完全匹配。 如果没有找到，则返回下一个 较大的。 2 - 通配符匹配，其中 *, ? 和 ~ 有特殊含义。
[search_mode] 可选	指定要使用的搜索模式： 1 - 从第一项开始执行搜索。 这是默认选项。 -1 - 从最后一项开始执行反向搜索。 2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序，将返回无效结果。 2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序，将返回无效结果。

上面需要注意的就是，没有VLOOKUP、HLOOKUP 指定列号的参数了，变成直接要返回的数组或区域

• 经典用法01-向右查找

录入公式：=XLOOKUP(L2,B:B,F:F) ，这里为了简单化公式录入，只录入了3个必选参数；和VLOOKUP一样，注意查询的工单号，一定要包含在搜索的区域。

和VLOOKUPR 区别如下：=VLOOKUP(L2,B:F,5,0)

对比下来，VLOOKUP需要时刻关注以搜索区域向右到返回区域的列号

• 经典用法02-向左查找 (反向查找）

录入公式：=XLOOKUP(J2,B:B,A:A)，和向右查找一样的原理，就是需要搜索的内容和搜索区域分别在第1、2参数，返回在第3参数

VLOOKUP 反向查找的话，需要用到嵌套

=VLOOKUP(J2,CHOOSE({1,2},B:B,A:A),2,0)

配合 Choose 强制生成一个新排序的数组，再返回。

• 经典用法03-向下查找

录入公式：=XLOOKUP(J1,$A$1:$D$1,$A$2:$D$2)，这里是多列引用，所以搜索区域和返回区域要锁死，不然向右填充公式的时候，引用区域会偏移；向下查找是行的概念，不是列了，要特别注意；

HLOOKUP 这里也是注意锁定录入公式 =HLOOKUP(J1,$A$1:$D$12,2,0)，

如果要返回第多行，可以用ROW(A2)组合一下 =HLOOKUP(J$1,$A$1:$D$12,ROW(A2),0)

XLOOKUP 这边更改一下锁定方式 =XLOOKUP(J$1,$A$1:$D$1,$A2:$D2)

• 经典用法04-向上查找

向上查找用得非常少，为了演示XLOOKUP 四个方向都可以胜任，这里也演示一下

• 经典用法05-屏蔽错误

在经典用法01的基础上，录入第4个参数，如果搜索不到返回结果是“找不到“

=XLOOKUP(L2,B:B,F:F,'找不到')

=IFERROR(VLOOKUP(L2,B:F,5,0),'找不到')

如果用VLOOKUP的话，还要用多一个函数，所以说XLOOKUP 这里完胜；

• 经典用法06-双条件查询及多条件查询

两个条件的查询怎么查？如下图，查询方正电饭煲的数量是多少？

此时用XLOOKUP，只需要用到文本连接符号&就可以了

录入公式：=XLOOKUP(F2&G2,B2:B5&C2:C5,D2:D5)

相当于重新建立一个合并连接区域；

VLOOUP 如果需要多条件查询，需要再次用到CHOOSE 或者 INDEX+MATCH 的经典组合

=VLOOKUP(F2&G2,CHOOSE({1,2},B2:B5&C2:C5,D2:D5),2,0)

• 经典用法07- 返回多列

录入公式 =XLOOKUP(A9,A2:A5,B2:H5) ，把第三个参数更改为多列，就可以实现多列的批量引用，对于需要连续引用的需求，避免了多次录入公式；

注意结果数组，不能更改，公式为灰色；

如果是VLOOKUP的话，需要配合COLUMN函数来实现，录入公式

=VLOOKUP($A8,$A$2:$H$5,COLUMN(B1),0)

结果如下：

• 经典用法08- 通配 * 符号查找

有时候我们需要找以什么开头，或者包含什么的时候，XLOOKUP 支持通配 符号来查询的，此时我们需要把第5个参数 录入为2

如下图：

录入公式：=XLOOKUP(A9,A2:A5,B2:B5,,2)

这里要注意，重复值只能返回第1个

• 经典用法09- 查询最大值或最小值

有时候我们想知道第1个值和最后一个值的时候，比如工序数据库中各种加工产品，第1道工序和最后一道工序是什么？

此时用XLOOKUP可以快速解决这个问题

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,1)

=XLOOKUP($E2,$A$2:$A$10,$C$2:$C$10,,,-1)

分别下拉填充公式就可以得到结果

理解 第6个参数 就可以了

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

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

我是古哥：
从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。关注古哥计划，每日分享制造行业，特别是生产计划方面的一些职场干货。