XLOOKUP
Excel函数的参数越多，可能性也越多，灵活性越强。当然，SUM,COUNTIFS,这一类参数雷同的不算。
参数最多的是XLOOKUP，6个。这固然使其具备了强大功能，但也让一些初学者望而却步。
本文将对6个参数逐一解读，带你领略XLOOKUP的独特魅力。
XLOOKUP的功能可以用“搜索”来概括，大部分时候只需要前三个必须选参数就可以实现比肩VLOOKUP的搜索功能。
第一章 三个必选参数
1.1 参数解读
参数一，要搜索的值
参数二，要在其中进行搜索的数组或范围
参数三，要返回的数组或范围
- 示例：
=XLOOKUP("小郑",A:A,C:C)
在A列搜索"小郑"，返回C列对应的值。
XLOOKUP示例
注意，以上示例中第二第三参数都是范围（A列和C列），在参数的定义中它们也可以是一个数组。
- 示例：
=XLOOKUP("小胡",A2:A9,{20;30;40;50;60;70;80;90})
第二参数为范围，第三参数则是一个数组。"小胡"是第二参数中的第五元素，XLOOKUP返回第三参数数组中的第五元素"60"。
XLOOKUP示例
1.2 第二三参数元素对等
Excel笔记：XLOOKUP第二参数和第三参数两个参数必须元素对等。
- 示例：
要查询队列中的第一个数字：
=XLOOKUP(TRUE,ISNUMBER(B2:B9),B2:B9)
XLOOKUP示例
第一参数：要搜索”TRUE”.
第二参数：编辑栏选中ISNUMBER(B2:B9)，按F9预览其结果：
{FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}
一个以TRUE和FALSE组成的数组
F9预览
第三参数：范围B2:B9
XLOOKUP第二参数中搜索到"TRUR"位于第3个元素，所以返回B2:B9中的第3个元素"30".
由于是按对等位置来确定返回值，如元素数量不同，将无法正常工作。
- 示例：
正确示范：
=XLOOKUP("A008",C2:C10,A1:A9)
错误示范：
=XLOOKUP("A008",C2:C11,A1:A9)
第二参数C2:C11有9个元素，第三参数A1:A9有8个元素，XLOOKUP返回错误值。
正确示范与错误示范
1.3 同向
Excel笔记：XLOOKUP第二，第三参数必须同向。
第二三参数除了元素对等，还需要同向，即两个参数同时为纵向或横向。
框选范围时很好判断是否同向。
- 示例：
纵向查询：
=XLOOKUP("小胡",A2:A9,B2:B9)
横向查询：
=XLOOKUP("小胡",E1:L1,E2:L2)
XLOOKUP示例
Excel笔记：手动输入数组时，元素之间用分号";"间隔表示纵向排布，用逗号","间隔表示横向排布。
示例：
纵向查询：
=XLOOKUP("小胡",A2:A9,{20;30;40;50;60;70;80;90})
横向查询：
=XLOOKUP("小胡",D1:K1,{20,30,40,50,60,70,80,90})
纵向查询与横向查询
1.4【数组】或【范围】
Excel笔记：XLOOKUP的参数定义为【数组】或【范围】，意味着它可以是任意一个公式运算后的结果，三个参数皆如此。
例如班上有两个小王，家长去找人时需要明确你要找的是哪个小王，男小王？女小王？
=XLOOKUP("小王女",A2:A6&B2:B6,C2:C6)
注意第二参数，A列和B列连接返回数组作为搜索区域，以对应第一参数中姓名和性别两个条件。
XLOOKUP查找
利用第三参数可以将一些简单的后续运算包含到XLOOKUP中。比如最终目的是返回B列和C列后再相加，可以直接返回B+C，有效缩短整个过程。
同时，第三参数可以以数组的形式返回多个结构。例如一次性返回小胡的语文和数学成绩。
=XLOOKUP(F4,A:A,C:D)
XLOOKUP示例
1.5 位置关系
Excel笔记：XLOOKUP对（第二参数）搜索区域和（第三参数）返回区域的位置没有要求。
这也是得益于【数组】或【范围】的设计模式，使得XLOOKUP具备极强的灵活性。
- 示例：
=XLOOKUP(G3,E3:E9,B5:B11)
注意E3:E9和B5:B11两个范围的位置关系，并不在同行，也没有左右关系的限制。
XLOOKUP示例
1.6应用案例
- 案例1：反向查询学生信息
在VLOOKUP长达30多年的统治下，一个观念已经潜移默化地影响了大量用户：从左往右查找才是正义。所以才有了“反向查询”一说，即从右往左查。
根据学号查询姓名
=XLOOKUP(H2,C:C,A:A)
根据学号查询性别
=XLOOKUP(H2,C:C,B:B)
反向查询学生信息
- 案例2：横向查询返回右下方的值
从数据区域提取指定数字右下方的值。注意理解A2:J2和B3:K3两个数据区域的位置关系。
=XLOOKUP(M2,A2:J2,B3:K3)
横向查询返回右下方的值
- 案例3：横向查询员工首次产出日期
=XLOOKUP(TRUE,FILTER(B2:H7,A2:A7=A10)>0,$B$1:$H$1)
FILTER返回查询员工的整行数据，并判断是否大于0，形成一个TRUE和FASLE组成的数组，在其中查找TRUE,XLOOKUP找到第一个TRUE后返回B2：H2中对应的日期。
横向查询员工首次产出日期
- 案例4：错列查询员工信息
实现效果：在H2中输入不同的部门，黄色区域显示对应的员工共和职位。
扩展知识点：合并单元格的数据放置于左上单元格中，其他单元格为空。例如A1和B1合并后输入的行政部，是存放在A1单元格中的，B1单元格实际为空。
=XLOOKUP(H2,A1:F1,A2:F6)&""
=XLOOKUP(H2,A1:F1,B2:G6)&""
两个公式中只有第三参数有差别，由于合并单元格的存在，查询员工职位时需要右移一列。
错列查询员工信息
- 案例5 根据出生日期是*的身份证号码查询姓名
=XLOOKUP(LEFT(D2,6)&RIGHT(D2,4),LEFT(A2:A7,6)&RIGHT(A2:A7,4),B2:B7)
第一参数：LEFT(D2,6)&RIGHT(D2,4)
LEFT和RIHGT提取查找值的前6位和后4位，连接后作为查询条件；
第二参数：LEFT(A2:A7,6)&RIGHT(A2:A7,4)
LEFT和RIHGT提取身份证列的前6位和后4位，连接后作为被查询区域，以此对应第一参数的数据结构。
根据出生日期是*的身份证号码查询姓名
- 案例6 统计员工产量
通过”姓名”&”日期”查询的方式将一维数据转换为二维数据：
=XLOOKUP($E2&F$1,$B:$B&$A:$A,$C:$C)
统计员工产量
- 案例7 按三个条件提取数据
知乎网站截图
这是知乎上的网友提问，可以当作查询问题处理，查询的条件有三个：部门，类型，周期。将以下公式填入C7下拉右拉填充。
=IFERROR(XLOOKUP($A7,$C$1:$E$1,XLOOKUP($B7&C$6,$A$2:$A$4&$B$2:$B$4,$C$2:$E$4)),"")
先将C7中的公式取消锁定符号以便分析：
=IFERROR(XLOOKUP(A7,C1:E1,XLOOKUP(B7&C6,A2:A4&B2:B4,C2:E4)),"")
内层XLOOKUP将”部门”&”类型”作为查询条件，返回对应的数组{1,22,56}作为外层XLOOKUP的第三参数，外层XLOOKUP在C1:E1中查找wk1,返回对应位置的元素1.
按三个条件提取数据
- 案例8 查询并返回指定格式的数据
通过XLOOKUP实现上面表格到下面表格的形式转换，将以下公式填入B8右拉填充。
=XLOOKUP($A8&B$7,$A$2:$A$4&$C$2:$C$4,$B$2:$B$4&","&TEXT($D$2:$D$4,"m/d"))
将B8中公式的锁定符号取消逐步分析：
第一参数：A8&B7将公司名称和办理事项连接后作为查询条件；
第二参数：A2:A4&C2:C4对应将公司名称和办理事项两个区域连接作为查询区域
第三参数：B2:B4&","&TEXT(D2:D4,"m/d")，同时返回B列和D列的信息，中间用逗号隔开，并且通过TEXT函数指定日期格式，得到”张三，10/12”的结果。
查询并返回指定格式的数据
该案例中三个参数都是逻辑运算或公式运算后的结果，充分体现了【数组】或【范围】带来的便利。
- 案例9 根据订单号查询信息
注意该案例中查询输出的顺序和原始表格不同。
=XLOOKUP($F2,$A$2:$A$11,XLOOKUP(G$1,$B$1:$D$1,$B$2:$D$11))
内层XLOOKUP根据G1查询，返回数组D2:D12作为外层XLOOKUP的第三参数。
根据订单号查询信息
第二章 第四参数
2.1 参数解读
参数四，如果找不到匹配项，则返回。
这一参数等同于集成了函数IFERROR或函数IFNA，解决“找不到，怎么办？“这个问题。
2.2纠错
第四参数省略的情况下如果搜索不到查找值，XLOOKUP将返回错误值”#N/A”,故而经常被称作纠错处理。
- 示例：
第四参数未定义，返回” #N/A”
=XLOOKUP("王老师",A:A,C:C)
返回第四参数定义的字符
=XLOOKUP("王老师",A:A,C:C,"老师没有成绩")
XLOOKUP示例
只能说“纠错”这样的描述狭隘了，找不到的情况下，还可以在这里定义下一步行动。
2.3 案例
- 案例10 未找到则返回小于该日期的平均价格
=XLOOKUP(D2,A:A,B:B,AVERAGEIF($A$2:$A$11,"<"&D2,$B$2:$B$11))
在A列查找日期，如果找到返回B列对应的值，找不到则执行第四参数。
第四参数通过AVERAGEIF函数求取小于该日期的平均值。
=AVERAGEIF($A$2:$A$11,"<"&D2,$B$2:$B$11)
未找到则返回小于该日期的平均价格
- 案例11 查询订单销售额
=XLOOKUP("AT-7467",A:A,B:B,XLOOKUP("AT-7467",D:D,E:E,""))
外层XLOOKUP在A列搜索不到AT-7467，执行第四参数。
即内层XLOOKUP,到D列搜索AT-7467。
如仍然搜索不到，返回内层XLOOKUP的第四参数“请确认订单“
查询订单销售额
第三章 第五参数
3.1 参数解读
第五参数：匹配模式，指定如何匹配第一参数和第二参数。
- 设置为0：精确匹配，未找到结果，返回 #N/A。 这是默认选项。
- 设置为-1：精确匹配或下一个较小的项。
先进行精确匹配，如未找到结果，则匹配比它小的下一个项。例如在{1,2,4,5}中查找3，精确匹配失败后，则匹配比3小的下一个值2.
- 设置为1：精确匹配或下一个较大的项。
先进行精确匹配，如未找到结果，则匹配比它大的下一个项。例如在{1,2,4,5}中查找3，精确匹配失败后，则匹配比3大的下一个值4.
- 2 通配符匹配
第一参数有通配符时，需设置为2，否则无法正常工作
3.2 案例
- 案例12 查询员工用餐前打卡类型
查找员工用餐时间前的最后一次打卡是上班卡还是下班卡，上班卡判断为“异常用餐”。
=XLOOKUP($E2&$G2,$A$2:$A$13&$C$2:$C$13,$B$2:$B$13,,-1)
取消锁定符号以便解读公式：
=XLOOKUP(E2&G2,A2:A13&C2:C13,B2:B13,,-1)
第一参数：E2&G2，搜索“姓名&时间”。
第二参数：A2:A13&C2:C13，“姓名&时间”作为搜索区域。
第三参数：B2:B13返回打卡类型
第五参数：设置为-1，查找“李宇8:00”时无法精确匹配，则匹配比它小的下一个值”李宇7:30”，返回B2单元格的”上班卡“。
查询员工用餐前打卡类型
- 案例13 提取内容并归类放置
需求是把A列单元格中多种疾病提取后放到对应的列。
=XLOOKUP("*"&B$1&"*",$A2,B$1,"",2)
公式解读：B1前后搭配通配符在A2中查询，A2包含“心脏病”即查询成功，返回B1“心脏病”，否则为空。第五参数需设置为2，表明公式中有通配符。
提取内容并归类放置
- 案例14 按日期取价格样本
取每个月5的倍数日期进货价格作为样本价格，如当日没有进货，则取前后最近两次价格的平均值。
=XLOOKUP(D2,A:A,B:B,AVERAGE(XLOOKUP(D2,A:A,B:B,,{-1,1})))
外层XLOOKUP第五参数省略，如不能精确匹配，执行第四参数：
=AVERAGE(XLOOKUP(D2,A:A,B:B,,{-1,1}))
内层XLOOKUP第四参数设置为{-1,1}表明查询前后两次最近两次的价格，以数组的方式返回，再用AVERAGE取平均。
如查找2023/9/5，外层XLOOKUP无法精确匹配，执行第四参数。内层XLOOKUP返回2023/9/4和2023/9/8对应的进货价格{12.1，12.6}，再由AVERAGE取平均值得到12.35。
按日期取价格样本
第四章 第六参数
第六参数：搜索模式。默认为从第一项到最后一项的搜索模式。
- 设置为1：从第一项到最后一项进行搜索
由上而下，从左到右
- 设置为-1：从最后一项到第一项进行搜索
由下而上，从右到左
- 设置为2：二进制文件搜索（升序排序）
- 设置为-2：二进制文件搜索（降序排序）
在数组{4,99,4,99,4,99,4}中搜索99.
默认情况下按设置为1处理，从左到右搜索，结果是从左往右数的第1个99
设置为-1，将从右到左搜索，结果是第右往左数的第1个99.
Excel笔记：如有重复值,XLOOKUP返回第一个值对应的结果。
二进制文件搜索是一种搜索方式，在数据量巨大的情况下可以提升效率，避免电脑卡顿甚至卡死，一般行数达到6位数才能体现效果。
- 案例15：最近一次采购日期和价格
由于原数据按日期升序排列，XLOOKUP从下往上查找即为最近一次采购数据。
=XLOOKUP(E3,A:A,B:C,,,-1)
最近一次采购日期和价格
- 案例16：关键字匹配
根据A,B列关键字的对应关系，为项目匹配类型。
=XLOOKUP(1,XLOOKUP("*"&$A$2:$A$6&"*",D2,1,0,2),$B$2:$B$6,,,-1)
关键字匹配
延伸阅读：
XLOOKUP凭什么能实力碾压VLOOKUP
