昨天提到WPS最新版本有了XLOOKUP函數(shù)，其實(shí)Office365有的函數(shù)，目前基本都有了。今天跟盧子來看看FILTER函數(shù)，這個(gè)作用更大。

有一個(gè)缺陷需要事先說明，在使用FILTER函數(shù)的時(shí)候，Office365能夠自動(dòng)擴(kuò)展區(qū)域，而WPS不能，而且數(shù)組公式依然需要按三鍵。

Office365不需要鎖定區(qū)域，也不需要下拉和右拉公式，也不需要三鍵，啥都不需要。只需在一個(gè)單元格輸入公式，就自動(dòng)擴(kuò)展，簡(jiǎn)單到?jīng)]朋友。

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

下面就是FILTER、XLOOKUP、VLOOKUP、LOOKUP四個(gè)函數(shù)大PK。

1.一般基礎(chǔ)查找

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

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

FILTER函數(shù)的用法看起來跟LOOKUP函數(shù)有點(diǎn)像，不過少了用1查找0，還有就是返回區(qū)域在第1參數(shù)。

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

語法說明：

=FILTER(返回區(qū)域,條件區(qū)域=條件)

2.反向查找

其實(shí)VLOOKUP也能反向查找，借助IF就可以。

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

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

FILTER函數(shù)跟LOOKUP函數(shù)一樣，不區(qū)分方向。

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

3.橫向查找

這個(gè)案例的數(shù)據(jù)其實(shí)提供的并不是很好，凱文有多條記錄。如果有多個(gè)對(duì)應(yīng)值，VLOOKUP返回第一個(gè)，LOOKUP返回最后一個(gè)。

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

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

如果有多個(gè)對(duì)應(yīng)值，支持返回全部對(duì)應(yīng)值。這個(gè)最后面的案例會(huì)說明。

=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)

返回多列結(jié)果的時(shí)候，要先選中返回的整個(gè)區(qū)域，輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

=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函數(shù)不適合這個(gè)案例。

7.匹配模式

其實(shí)就是按區(qū)間查找，這種一般都是將區(qū)間從小寫到大，跟案例的數(shù)據(jù)順序相反。

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

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

FILTER函數(shù)不適合這個(gè)案例。

8.未找到值時(shí)返回指定內(nèi)容

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

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

FILTER函數(shù)最后參數(shù)還能讓錯(cuò)誤值顯示你需要的結(jié)果，不過有些錯(cuò)誤值并沒法處理，有點(diǎn)雞肋。

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

以上的案例，F(xiàn)ILTER函數(shù)雖然大多數(shù)都能做到，不過并沒有什么優(yōu)勢(shì)。最大的優(yōu)勢(shì)，是將符合條件的結(jié)果一次性引用出來。

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

如果有多個(gè)對(duì)應(yīng)值，VLOOKUP返回第一個(gè)，LOOKUP返回最后一個(gè)。而FILTER函數(shù)是可以一次性返回所有對(duì)應(yīng)值。

將所有姓名為凱文的2月績(jī)效全部查找出來。選擇多個(gè)單元格，輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

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

從前面的案例可以知道，F(xiàn)ILTER函數(shù)支持多條件查找，也支持返回多列對(duì)應(yīng)值，這里再通過案例進(jìn)行演示。

將財(cái)務(wù)部所有男的姓名查找出來。選擇多個(gè)單元格，輸入公式，按Ctrl+Shift+Enter三鍵結(jié)束。

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

前面說過第3參數(shù)很雞肋，并不是所有錯(cuò)誤值都可以處理，比如現(xiàn)在這個(gè)案例，因?yàn)閱卧穸噙x了幾個(gè)，多出來的都是錯(cuò)誤值，寫第3參數(shù)依然沒用。

嵌套IFERROR函數(shù)也一樣，依然沒法處理。

這個(gè)就是文章開頭提到的缺陷，如果是Office365，并不會(huì)出現(xiàn)這種尷尬的現(xiàn)象。

你覺得哪個(gè)函數(shù)更厲害？

恭喜這3位粉絲：遙望星辰、小靈、細(xì)水流年，獲得書籍《跟盧子一起學(xué)Excel 早做完 不加班 》，加盧子微信chenxilu2019

推薦：VLOOKUP函數(shù)家族，16個(gè)函數(shù)，7大類別，一次全學(xué)會(huì)！

上文：XLOOKUP難道有這么牛X？看到就兩眼發(fā)光，急著換版本

你還知道Office365或WPS最新版有哪些新函數(shù)，里面最想學(xué)哪個(gè)函數(shù)？