XLOOKUP函數(shù)參數(shù)太多：6個(gè)參數(shù)。那這些參數(shù)帶來了什么呢？是否讓它更靈活呢？今天首先來看看它在常規(guī)用法上與VLOOKUPLOOKUP的對(duì)標(biāo)。

XLOOKUP有6個(gè)參數(shù)，橫豎皆可，正反都來，有一點(diǎn)通吃的感覺。

=XLOOKUP(查找值,查找區(qū)域或數(shù)組,返回區(qū)域或數(shù)組,找不到后要返回的值,匹配類型,搜索模式)

1. 大多時(shí)候你用前三個(gè)參數(shù)工作

不要擔(dān)心參數(shù)多，多數(shù)你用的是前3個(gè)。

=XLOOKUP(查找值,某一行或某一列查找區(qū)域,返回區(qū)域)

為啥？

①匹配類型默認(rèn)為完全匹配，數(shù)值為0，與VLOOKUP一樣。省略不寫，就是默認(rèn)。

②搜索模式默認(rèn)為從上往下從左到右，數(shù)值為1，與VLOOKUP一樣。省略不寫，就是默認(rèn)。

③第4參數(shù)可以不寫。不寫的最大后果就是查找不到的時(shí)候顯示錯(cuò)誤值。

因?yàn)樯厦娴奶攸c(diǎn)，所以大多數(shù)你用的是前3個(gè)。

2.常規(guī)用法

1）精確查找

（1）返回單個(gè)值（前提：返回區(qū)域是單行或單列）

譬如根據(jù)姓名查分?jǐn)?shù)。

=XLOOKUP(G2,A2:A10,E2:E10)

對(duì)比參考：

VLOOKUP公式=VLOOKUP(G2,A2:E10,3,0)

LOOKUP公式=LOOKUP(1,0/(A2:A10=G2),E2:E10)

繼承LOOKUP查找區(qū)域和返回區(qū)域各自獨(dú)立的特點(diǎn)，XLOOKUP查找也能輕松完成反向查找。譬如根據(jù)學(xué)號(hào)查姓名。

=XLOOKUP(G6,C2:C10,A2:A10)

對(duì)比參考：

VLOOKUP公式=VLOOKUP(G6,IF({1,0},C2:C10,A2:A10),2,0)

LOOKUP公式 =LOOKUP(1,0/(C2:C10=G6),A2:A10)

（2）返回一行或者一列值（前提：返回區(qū)域是多行多列）

譬如根據(jù)姓名查其所有信息。

=XLOOKUP(A14,A2:A10,B2:E10)

對(duì)比參考：

VLOOKUP公式，高版本=VLOOKUP(A14,A2:E10,{2,3,4,5},0)

              低版本=VLOOKUP($A14,$A2:$E10,COLUMN(B2),0)，然后右拉填充

LOOKUP公式 =LOOKUP(1,0/($A2:$A10=$A14),B2:B10)，然后右拉填充

再譬如查所有總分。

=XLOOKUP(G8,B1:E1,B2:E10)

對(duì)比參考：

VLOOKUP公式,高版本=VLOOKUP(G8,TRANSPOSE(A1:E10),ROW(2:10),0)

TRANSPOSE是轉(zhuǎn)置函數(shù)，把表格橫豎方向換位。VLOOKUP只能縱向查找，所以此處需要TRANSPOSE的幫忙。

             低版本=VLOOKUP(G$8,TRANSPOSE(A$1:E$10),ROW(A2),0),然后下拉填充

LOOKUP公式 =LOOKUP(1,0/(A$1:E$1=G$8),A2)，然后下拉填充。

2）模糊查找，典型的區(qū)間或者等級(jí)查找，第5參數(shù)-1或1

=XLOOKUP(查找值,某一行或某一列查找區(qū)域,返回區(qū)域,,-1或1)

① -1，表示找小于等于自己的最大數(shù)。效果與平常的VLOOKUP模糊查找一致。

② 1，表示找大于等于自己的最小數(shù)。

③ 查找區(qū)域不需要排序！不需要排序！不需要排序！這與VLOOKUP或者LOOKUP模糊查找最大不同。

譬如根據(jù)分?jǐn)?shù)查等級(jí)。

=XLOOKUP(G9,$B$17:$E$17,$B$16:$E$16,,-1)

對(duì)比參考：

VLOOKUP公式

如果不改變當(dāng)前的等級(jí)和分值布局，要轉(zhuǎn)置還要交換列順序，公式比較復(fù)雜：

=VLOOKUP(G9,IF({1,0},INDEX(TRANSPOSE($B$16:$E$17),0,2),INDEX(TRANSPOSE($B$16:$E$17),0,1)),2,1)

LOOKUP公式=LOOKUP(G9,$B$17:$E$17,$B$16:$E$16)，然后下拉填充。

打亂查找區(qū)域排序，結(jié)果仍然正確：

對(duì)比參考：

VLOOKUP公式：對(duì)不起，我無法

LOOKUP公式：對(duì)不起，我無法。

額外說明：

第6參數(shù)中的升序（數(shù)字2）、降序（數(shù)字-2）只用于按VLOOKUP或者LOOKUP工作模式進(jìn)行模糊匹配。設(shè)置2，好比使用VLOOKUP或者LOOKUP進(jìn)行模糊查找，那就必須讓查找區(qū)域升序排列， 否則結(jié)果可能是錯(cuò)的；設(shè)置為-2，就必須讓查找區(qū)域降序排列，否則結(jié)果也可能是錯(cuò)的。

只要不寫第6參數(shù)，或者讓其保持默認(rèn)值1，則模糊查找時(shí)，不需要排序就能得到正確結(jié)果。這就是第6參數(shù)中的2、-2很少使用的原因。

3.通配符查找：必須2一下（第5參數(shù)）

支持查找包含某某字符的數(shù)據(jù)，與VLOOKUP通配符查找功能一樣，但沒有VLOOKUP方便，必須要多寫一個(gè)第5參數(shù)“2”。2表示按通配符查找。

譬如，查找姓名中含“坤”的人員。

=XLOOKUP("*坤",A2:A10,A2:A10,,2)

注意返回的是從上往下第一個(gè)含“坤”人員。

如果打破第6參數(shù)的默認(rèn)，寫成-1，則返回從下往上查的第一個(gè)：

=XLOOKUP("*坤",A2:A10,A2:A10,,2,-1)

對(duì)比參考：

VLOOKUP公式=VLOOKUP("*坤",A2:A10,1,0)（返回“唐坤”）

LOOKUP公式=LOOKUP(99,FIND("坤",A2:A10),A2:A10)（返回“畢開坤”）

提示：第6參數(shù)搜索模式1或者-1，讓XLOOKUP更靈活。默認(rèn)1，從上往下，效果等同VLOOKUP精確查找；-1，從下往上，效果等同LOOKUP精確查找套路——有相同值的返回最后一個(gè)。

4.多條件查找：保持了VLOOKUP的合并條件做法

可以把多個(gè)條件合并在一起，然后將查找區(qū)域也合并在一起，實(shí)現(xiàn)多條件查找。

譬如，有兩個(gè)楊開顏，只找2組的楊開顏的總分。

=XLOOKUP(A13&B13,A2:A10&B2:B10,E2:E10)

對(duì)比參考：

Vlookup公式=VLOOKUP(A13&B13,IF({1,0},A2:A10&B2:B10,E2:E10),2,0)

LOOKUP公式=LOOKUP(1,0/((A2:A10=A13)*(B2:B10=B13)),E2:E10)

OK，關(guān)于XLOOKUP的常規(guī)用法今天就說到這里。

不知通過三個(gè)函數(shù)的對(duì)標(biāo)，你是否對(duì)XLOOKUP了解更深了？是否更喜歡了？

喜歡，請(qǐng)點(diǎn)贊，超過50，后面我們?cè)僬fXLOOKUP的特殊用法。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

LOOKUP函數(shù)經(jīng)典用法7例

LOOKUP二分法原理與查找規(guī)律

深度解讀VLOOKUP四大參數(shù)以及最新用法

液面效果百分比圖

版權(quán)申明：

本文作者Mutou；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。