Office 365 新增函数
2020年2月，Excel新增部分函数，但仅供office365订阅用户独享，在 Excel 2016 和 2019 Excel不可用。目前，office 2021已经添加这些函数，通过Excel 2021可以完全使用新增函数。
Xlookup 函数
XLOOKUP 函数搜索区域或数组，然后返回与它找到的第一个匹配项对应的项。 如果不存在匹配项，则 XLOOKUP 可以返回最接近 () 匹配项。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| 参数 | 说明 |
|---|---|
| lookup_value | 要搜索的值 |
| lookup_array | 要搜索的数组或区域 |
| return_array | 要返回的数组或区域 |
| [if_not_found] | 如果找不到有效的匹配项，返回 [if_not_found] 文本， 如未设置则返回#N/A 。 |
| [match_mode] | 指定匹配类型：默认选项 0 - 完全匹配。 如果未找到，则返回 #N/A。 |
| [search_mode] | 指定要使用的搜索模式：默认选项 1 - 从第一项开始执行搜索。 |
=XLOOKUP(检索关键字区域_本表, 查找区域_另表, 返回区域_另表, [未找到的显示内容], [匹配类型], [搜索顺序])
基本用法
使用 XLOOKUP 函 数按行查找表或区域中的东西。 例如，按部件号查找汽车部件的价格，或根据员工 ID 查找员工姓名。 使用 XLOOKUP，可以在一列中查找搜索词，并从另一列中的同一行返回结果，而不管返回列位于哪一边。
=XLOOKUP(A2:A16,综合表!C5:C29,综合表!G5:G29,"")
将‘综合表’中数据，根据本表第2至16行的A列内容，匹配‘综合表’第5行至第29行C列值，找到后在本表中返回‘综合表’该行的G列值。如果未找到，返回空值。
多条件匹配
如果查找的单列值不惟一，可以利用&符号将多列连接起来，实现多条件匹配查找。
=XLOOKUP(A2:A16&B2:B16,综合表!C5:C29&综合表!D5:D29,综合表!G2:G29,"")
将‘综合表’中数据，根据本表第2至16行的A列和B列内容，匹配‘综合表’第5行至第29行C列和D列的值，找到后在本表中返回‘综合表’该行的G列值。如果未找到，返回空值。
多列查询
如果单行查询，还可以返回多列值。通过绝对引用，利用复制粘贴功能，可以实现多行多列查询。
=XLOOKUP(A2,综合表!$C$5:$C$29,综合表!$E$2:$G$29,"")
将‘综合表’中数据，根据本表第2行的A列内容，匹配‘综合表’第5行至第29行C列值，找到后在本表中返回‘综合表’该行的E、F、G三列值。如果未找到，返回空值。将此公式复制粘贴到第3行至第16行，可以得到第2至第16行中返回的三列值。
提示
xlookup函数还可以实现模糊查找、横向查找等功能。
filter 函数
FILTER 函数可以基于定义的条件筛选一系列数据，此函数不需要绝对引用，因为它仅存在于一个单元格中，并将其结果填充到相邻单元格。
=FILTER(array,include,[if_empty])
| 参数 | 说明 |
|---|---|
| array | 要筛选的数据区域 |
| include | 筛选的条件，它是一个布尔值 |
| [if_empty] | 根据条件如果找不到结果，就返回第三参数的值，它是一个可选参数 |
=FILTER(源数据区域,条件判断式,[未找到的显示内容])
基本用法
一对多查询的效果就是通过一个值来返回多个结果，我们可以将其看做是数据的筛选，通过筛选条件值就可以返回多个结果。它是不包含表头的，所以我们需要将表头粘贴过来。
=FILTER(综合表!A2:K40,综合表!E2:E40=条件表!B2)
将‘综合表’第2行至第40行E列值，匹配‘条件表’第2行的B列，返回‘综合表’符合条件行的第A至K列的值。如果未找到，返回空值。
多条件匹配
在FILTER函数中，多条件判断式用加号来表示“或”，用星号（乘）表示“和、且”。每个条件的括号是英文的，不能省。
=FILTER(综合表!A2:K40,(综合表!E2:E40=条件表!B2)*(综合表!K2:K40=条件表!F2),"未找到")
将‘综合表’第2行至第40行E、K列值，匹配‘条件表’第2行的B、F列，返回‘综合表’符合条件行的第A至K列的值。如果未找到，返回“未找到”。
自定义列查询
如果要拼接多列，可以用SWITCH函数。
=FILTER(SWITCH({1,2,3},1,综合表!C2:C40,2,综合表!H2:H40,3,综合表!J2:J40),综合表!E2:E40=条件表!B2)
将‘综合表’第2行至第40行E列值，匹配‘条件表’第2行的B列，返回‘综合表’符合条件行的第C、H、J列的值。如果未找到，返回空值。
双表比较
1.筛选两表共同的记录。
=FILTER(表二!A2:C3,ISNUMBER(MATCH(表二!C2:C3,表一!B2:B5,0)),"")
对表二C列与表一B列进行比较，相同的行按表二格式返回内容。如果未找到，返回空值。
2.在某表中筛选另一表内不存在的记录。
=FILTER(表一!A2:C5,ISNA(MATCH(表一!B2:B5,表二!C2:C4,0)),"")
对于表一所有行，凡表二中没有的，按表一格式返回内容。如果未找到，返回空值。
提示
结合sort函数，可以对筛选出的数据同步排序。