104 365新函数 XLOOKUP 高阶用法

接下来古老师继续更新XLOOKUP的第11种到第15种的高阶用法，这部分内容会有点复杂，如果基础相对比较薄弱的话，可以看102初阶和103中阶的用法。XLOOKUP的高阶用法主要是配合其它函数一起，如VSTACK、INDIRECT等，还有定义名称动态引用等；

多条件查找：

查询条件不只有一个的时候，需要通过两个条件来确定返回结果，如果用VLOOKUP的话，就需要用到辅助列，这样最快，而且有多少个条件就连接多少个条件区域，这也是当年XLOOKUP没有推出来最常见的方法之一；多条件查找如果没有重复值并且查询的返回条件是数字区域的话，可以用SUMIFS汇总求和替代。下图中的案例分别录入3个函数：

I2=XLOOKUP(F2&G2,A2:A6&B2:B6,D2:D6)

I3=SUMIFS(D:D,A:A,F2,B:B,G2)

J2=VLOOKUP(G2&H2,A:E,5,0)

注意不同函数的方法，XLOOKUP因为查询区域和返回区域都是一列，可以直接用文本连接符号&连接在一起形成一个新的查询区域，VLOOKUP不行的原因是因第3参数需要有返回的列号，所以没有办法直接连接符号&连接区域；

如果有二个条件以上，就再连接，有多少个条件就用连接符号&连接多少次；

范围查询：

查询条件不是固定的，而是动态的话，比如查询1号到5号的销量汇总，或者查询8号到15号的，这是一个范围段的话，如果查询呢？传递的查询方法是通过MATCH定位开始和结束的位置，再利用OFFSET偏移到开始位置，用结束位置减去开始位置加上1，得到范围，再用SUM函数求和；如果用XLOOKUP的话就简单多了；

可以利用XLOOKUP查询结果可以是返回位置引用的特点，通过分别查询开始和结束的位置定位到其引用的点，再用SUM求和。相当于=SUM(C4:C6)

G3=SUM(XLOOKUP(G1,A:A,C:C):XLOOKUP(G2,A:A,C:C))

H7=SUM(OFFSET($C$1,G5,,G6-G5+1))

多区域查询：

查询的位置不一样，可能需要查询的位置可能在表1、表2、也可能是表3、表4，如果表特别多的话，用传统的VLOOKUP的话，可能需要配合IFERROR函数，如果表1找不到，就找表2，表2找不到表3……，以此类推，函数算法简单，但是公式会非常长。

用XLOOKUP，配合VSTACK函数的话就相对简单了，提前用VSTACK函数把需要查询多表的位置合并起来，就不用一个一个去判断了，相当于多表查询通过VSTACK合并成一个表查询了；分别录入以下函数，观察函数的不同用法效果；

C10=IFERROR(IFERROR(VLOOKUP(A10,$A$2:$C$6,3,0),VLOOKUP(A10,$E$2:$G$6,3,0)),VLOOKUP(A10,$I$2:$K$6,3,0))

B12=XLOOKUP(A12,VSTACK($A$2:$A$6,$E$2:$E$6,$I$2:$I$6),VSTACK($C$2:$C$6,$G$2:$G$6,$K$2:$K$6))

B11=VLOOKUP(A11,VSTACK($A$2:$C$6,$E$2:$G$6,$I$2:$K$6),3,0)

多工作表查询：

查询的数据在不同工作表之间，一个一个工作表查询特别麻烦，此时可以用INDIRECT配合工作表名快速查询，INDIRECT函数相当于用公式把引用范围INDIRECT($A3&"!B:B")转换成'2021'!B:B,只需要记住标准格式就可以了；“引号加区域”前面连接工作表名的单元格引用；

C3=XLOOKUP($B3,INDIRECT($A3&"!B:B"),INDIRECT($A3&"!C:C"))

D3=VLOOKUP(B3,INDIRECT($A3&"!B:C"),2,0)

这里发现VLOOKUP比XLOOKUP的公式更加短小；所以有时候用VLOOKUP还是比XLOOKUP更加方便；

工作表相片动态查询：

在没有信息化之前，人事部门录入员工的信息一般都是用Excel录入，把员工的基本信息和照片一一录入，员工录入比较多的情况下，要查询某个员工，如果直接筛选的话，就会出现员工图片错位，此时如果在一张新表建立一个员工查询界面，直接通过查询工号就返回对应的员工信息和相片，就相对比较好；

这个需求也不用复杂的VBA，各种函数嵌套，只需要一个XLOOKUP函数配合定义名称就可以了；

步骤1：G2录入函数=XLOOKUP(F2,A:A,B:D)

步骤2：定义名称（Ctrl+F3）,新建一个名称，并在引用位置录入函数=XLOOKUP($F$2,$A:$A,$D:$D)，回车

步骤3：复制一张图片到I2，也就是照片0处

步骤4：点中照片，把照片的链接地址录入=xp，也就是刚刚命名的名称，回车后，就创建了照片的动态链接，此时把工号换成102,返回的就是102号工单的照片，实现了动态引用；

总结：

XLOOKUP掌握好后，对生产计划在查询分析数据的时候非常有用，升级你的Excel版本是非常有必要的；

文件名称：102 365新函数 强大的XLOOKUP 十五个经典用法.XLSX

是古哥：

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