【原】FILTER、XLOOKUP、VLOOKUP、LOOKUP四个函数大PK，谁才是你心目中最厉害的函数？

昨天提到WPS最新版本有了XLOOKUP函数，其实Office365有的函数，目前基本都有了。今天跟卢子来看看FILTER函数，这个作用更大。

有一个缺陷需要事先说明，在使用FILTER函数的时候，Office365能够自动扩展区域，而WPS不能，而且数组公式依然需要按三键。

Office365不需要锁定区域，也不需要下拉和右拉公式，也不需要三键，啥都不需要。只需在一个单元格输入公式，就自动扩展，简单到没朋友。

=FILTER(C2:G11,B2:B11=D14)

下面就是FILTER、XLOOKUP、VLOOKUP、LOOKUP四个函数大PK。

1.一般基础查找

=VLOOKUP(H3,B3:F12,3,0)

=LOOKUP(1,0/(B3:B12=H3),D3:D12)

FILTER函数的用法看起来跟LOOKUP函数有点像，不过少了用1查找0，还有就是返回区域在第1参数。

=FILTER(D3:D12,B3:B12=H3)

语法说明：

=FILTER(返回区域,条件区域=条件)

2.反向查找

其实VLOOKUP也能反向查找，借助IF就可以。

=VLOOKUP(H3,IF({1,0},D3:D12,C3:C12),2,0)

=LOOKUP(1,0/(H3=D3:D12),C3:C12)

FILTER函数跟LOOKUP函数一样，不区分方向。

=FILTER(C3:C12,D3:D12=H3)

3.横向查找

这个案例的数据其实提供的并不是很好，凯文有多条记录。如果有多个对应值，VLOOKUP返回第一个，LOOKUP返回最后一个。

=VLOOKUP(G3,B3:E12,3,0)

=LOOKUP(1,0/(G3=B3:B12),D3:D12)

如果有多个对应值，支持返回全部对应值。这个最后面的案例会说明。

=FILTER(D3:D12,G3=B3:B12)

4.多列查找

=VLOOKUP($H3,$B$3:$F$12,COLUMN(B1),0)

=LOOKUP(1,0/($H3=$B$3:$B$12),C$3:C$12)

返回多列结果的时候，要先选中返回的整个区域，输入公式，按Ctrl+Shift+Enter三键结束。

=FILTER(B3:F12,B3:B12=H3)

5.多条件查找

=VLOOKUP(H3&I3,IF({1,0},B3:B12&D3:D12,C3:C12),2,0)

=LOOKUP(1,0/(H3&I3=B3:B12&D3:D12),C3:C12)

=FILTER(C3:C12,H3&I3=B3:B12&D3:D12)

6.搜索模式

=LOOKUP(1,0/(E3=C3:C12),B3:B12)

FILTER函数不适合这个案例。

7.匹配模式

其实就是按区间查找，这种一般都是将区间从小写到大，跟案例的数据顺序相反。

=VLOOKUP(F3,$J$3:$L$6,3)

=LOOKUP(F3,$J$3:$L$6)

FILTER函数不适合这个案例。

8.未找到值时返回指定内容

=IFERROR(VLOOKUP(H3,IF({1,0},D3:D12,C3:C12),2,0),"查无此人")

=IFERROR(LOOKUP(1,0/(H3=D3:D12),C3:C12),"查无此人")

FILTER函数最后参数还能让错误值显示你需要的结果，不过有些错误值并没法处理，有点鸡肋。

=FILTER(C3:C12,H3=D3:D12,"查无此人")

以上的案例，FILTER函数虽然大多数都能做到，不过并没有什么优势。最大的优势，是将符合条件的结果一次性引用出来。

9.查找符合条件的所有值

如果有多个对应值，VLOOKUP返回第一个，LOOKUP返回最后一个。而FILTER函数是可以一次性返回所有对应值。

将所有姓名为凯文的2月绩效全部查找出来。选择多个单元格，输入公式，按Ctrl+Shift+Enter三键结束。

=FILTER(D3:D12,B3:B12=G3)

从前面的案例可以知道，FILTER函数支持多条件查找，也支持返回多列对应值，这里再通过案例进行演示。

将财务部所有男的姓名查找出来。选择多个单元格，输入公式，按Ctrl+Shift+Enter三键结束。

=FILTER(D3:D13,H3&I3=C3:C13&E3:E13)

前面说过第3参数很鸡肋，并不是所有错误值都可以处理，比如现在这个案例，因为单元格多选了几个，多出来的都是错误值，写第3参数依然没用。

嵌套IFERROR函数也一样，依然没法处理。

这个就是文章开头提到的缺陷，如果是Office365，并不会出现这种尴尬的现象。

你觉得哪个函数更厉害？

恭喜这3位粉丝：遥望星辰、小灵、细水流年，获得书籍《跟卢子一起学Excel 早做完 不加班 》，加卢子微信chenxilu2019

推荐：VLOOKUP函数家族，16个函数，7大类别，一次全学会！

上文：XLOOKUP难道有这么牛X？看到就两眼发光，急着换版本

你还知道Office365或WPS最新版有哪些新函数，里面最想学哪个函数？

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）