?數(shù)據(jù)查詢是Excel數(shù)據(jù)處理中的一項核心業(yè)務(wù)，也是日常辦公中使用頻率非常高的一項操作 。而LOOKUP系列函數(shù)則是數(shù)據(jù)查詢最基礎(chǔ)的方法，也是日常辦公中必須掌握的一項Excel技能。

LOOKUP系列函數(shù)指LOOKUP、VLOOKUP、HLOOKUP這三個函數(shù)（以及其他的擴(kuò)展函數(shù)），其中應(yīng)用最廣泛的自然要數(shù)VLOOKUP了。本文將會逐一介紹VLOOKUP函數(shù)的各種應(yīng)用場景，及其相應(yīng)的方法技巧，并深入分析每種查詢方法的特點和注意事項。同時，對查詢操作涉及到的其他典型函數(shù)（或組合）應(yīng)用進(jìn)行簡要介紹。

本文內(nèi)容較長，先給大家梳理一下文章目錄。

一、目錄

二、Excel中的數(shù)據(jù)查詢基礎(chǔ)知識

三、正向查詢和逆向查詢

1.用VLOOKUP進(jìn)行正向查詢

2.用VLOOKUP進(jìn)行逆向查詢

3.更加便捷的查詢方法INDEX+MATCH

四、用VLOOKUP進(jìn)行精確查詢和模糊查詢

五、多條件多結(jié)果查詢

1.一對一查詢

2.一對多查詢

（1）用VLOOKUP函數(shù)，借助輔助列查詢多個結(jié)果

（2）借助“Excel擴(kuò)展函數(shù)包”查詢多個結(jié)果

（3）用INDEX+SMALL+IF函數(shù)組合查詢多個結(jié)果

3.多對一查詢

4.多對多查詢

六、總結(jié)

二、Excel中的數(shù)據(jù)查詢基礎(chǔ)知識

如下圖所示的一張表格，如果按照【姓名】查詢對應(yīng)【身份證號】叫作“正向查詢”反之則叫作“逆向查詢”。

如果查詢包含“漁坪村”這個關(guān)鍵字的信息，叫作“模糊查詢”，反之如果查找“高峰鎮(zhèn)漁坪村3組”這個完整字段信息，則叫作“精確查詢”。

如果是查詢【身份證號】對應(yīng)的【姓名】，叫作一對一查詢，即一個條件一個查詢結(jié)果。同理，根據(jù)【姓名】【班級】這兩個字段，來查找對應(yīng)的【身份證號】則叫作多對一查詢，比如下表中的“李杰”有兩個，一個在2班，一個在3班，只根據(jù)【姓名】一個字段，無法準(zhǔn)確定位到正確的身份證號，因此需要進(jìn)行“多對一查詢”（也叫多條件查詢）。至于一對多查詢、多對多查詢也是類似的道理。

三、正向查詢和逆向查詢

1. 用VLOOKUP進(jìn)行正向查詢

正向查詢是VLOOKUP函數(shù)最基礎(chǔ)的用法。如下圖所示，我們要查找金凱風(fēng)的班級信息，只需在I3單元格輸入公式=VLOOKUP(H3,A2:F17,2,0)，確定即可。

這個公式的基本原理如下圖所示。我們在A2:F17這個范圍來查詢H3單元格（金凱風(fēng)）對應(yīng)的信息，返回對應(yīng)查詢范圍A2:F17的第2列（班級）信息，最后一個參數(shù)一般為0（或FALSE），表示精確查找。

后面介紹的所有關(guān)于VLOOKUP函數(shù)的查詢方式也都是基于上述原理，只是稍加變通而已。

1. 用VLOOKUP進(jìn)行逆向查詢

從本質(zhì)上說，VLOOKUP函數(shù)只能進(jìn)行上述類似的正向查詢，即條件列必須在結(jié)果列之前。但是我們可以通過互換兩列的位置來實現(xiàn)逆向查詢。當(dāng)然這里的互換位置不是真的要改變原表結(jié)構(gòu)，而是通過數(shù)組重裝的的方式來實現(xiàn)。

比如，如下圖所示，現(xiàn)在要根據(jù)身份證號查詢對應(yīng)姓名信息，可以用如下公式實現(xiàn)：

=VLOOKUP(H3,IF({1,0},C2:C17,A2:A17),2,0)

這里的第2個參數(shù)依然表示查詢范圍，但卻并不像A2:F17這么直接，而是用一個IF函數(shù)互換了C列和A列數(shù)據(jù)的前后順序。相當(dāng)于查詢范圍變成了如下圖所示的結(jié)果，它是一個16行，2列的數(shù)據(jù)塊兒。我們需要返回的結(jié)果在這個范圍的第2列，因此第3個參數(shù)才是2。最有一個參數(shù)為0，同樣表示精確查找。

我們可以選中上述公式的IF({1,0},C2:C17,A2:A17)部分，按F9鍵查看這個重裝之后的數(shù)組。

1. 更加便捷的查詢方法INDEX+MATCH

其實在Excel中有這么一個函數(shù)組合，可以無視查詢條件和返回結(jié)果的前后順序，逆向查詢和正向查詢一樣簡單，它就是INDEX+MATCH函數(shù)組合。

如下圖所示，在I3單元格輸入公式=INDEX(C2:C17,MATCH(H3,A2:A17,0))即可得到查詢結(jié)果。

INDEX+MATCH函數(shù)組合的工作原理如下：

四、用VLOOKUP進(jìn)行精確查詢和模糊查詢

在90%以上的情境中我們用的都是精確查詢，正如上文所示的查詢案例一樣。但有時候模糊查詢也能取得意想不到的效果。

比如我們現(xiàn)在要查詢一個“漁坪村”的學(xué)生，就只能根據(jù)家庭住址中包含“漁坪村”關(guān)鍵字的信息進(jìn)行查詢。其實原理也很簡單，就是利用通配符*來進(jìn)行模糊匹配。

如下圖所示，查詢公式為=VLOOKUP("*"&H3&"*",IF({1,0},D2:D17,A2:A17),2,0)。

五、多條件多結(jié)果查詢

1. 一對一查詢

上文所示的正向查詢和逆向查詢案例，都是一對一查詢的典型應(yīng)用，也即根據(jù)一個查詢條件，查詢得到一個結(jié)果。因此VLOOKUP的一對一查詢就不再贅述。

1. 一對多查詢

一對多查詢，即根據(jù)一個條件查詢出多個滿足條件的結(jié)果。比如，我們可以一次性查詢出所有的貧困戶學(xué)生的姓名。雖然通過篩選也可以做好這件事，但很多時候我們需要的只能是查詢的方式。

這里有3種方法，第1種方法是Excel原生的VLOOKUP查詢，但是需要借助一個輔助列，步驟相對稍微復(fù)雜；第2種方法非常簡單，但是需要借助一個叫做“Excel擴(kuò)展函數(shù)包”的插件；第3種方法最復(fù)雜，是利用INDEX+SMALL+IF函數(shù)組合來實現(xiàn)一對多查詢。下面逐一介紹。

（1）用VLOOKUP函數(shù)，借助輔助列查詢多個結(jié)果

第一步，建立一個輔助列，一般情況下最好是在表格的最前面插入一列，方便正向查詢，如下圖所示。

然后，在A3單元格輸入公式=($I$3=F3)+N(A2)，其作用是用來統(tǒng)計F3:F17這個范圍內(nèi)，“是”第幾次出現(xiàn)。

這個公式可以這么來理解：

$I$3=F3判斷I3和F3是否相同，如果相同返回TRUE，在計算中會自動轉(zhuǎn)化為數(shù)字1。注意這里的$I$3這種絕對引用方式，其目的是為了在向下拖動的過程中，I3單元格的引用始終不變，即始終拿I3和F列數(shù)據(jù)進(jìn)行比對。

公式后面的+N(A2)是最巧妙的地方，如果沒有這部分，那么輔助列返回的就是1,1,0,1,0,0...這樣的結(jié)果。N(A2)函數(shù)是用來取得A2單元格的數(shù)值，如果是非數(shù)值，則返回0，這樣才能得到累加的效果。

第二步，用VLOOKUP進(jìn)行查詢。

在J3單元格輸入公式=VLOOKUP(ROW(A1),$A$2:$B$17,2,0)，然后向下拖動（或雙擊右下角）填充至最后一行。這樣就查詢出了所有的貧困戶學(xué)生姓名。

這個公式中查詢條件大家也發(fā)現(xiàn)了，并不是I3單元格，而是ROW(A1)，而ROW(A1)=1，再向下拖動過程中公式中的ROW(A1)會變成ROW(A2)、ROW(A3)...對應(yīng)數(shù)字1、2、3...也就是說我們是在查詢貧困戶第幾次出現(xiàn)，即A列對應(yīng)的信息。

有了前文的正向查詢的基礎(chǔ)，公式剩下的部分就好理解了。

第三步，用IFERROR函數(shù)美化查詢結(jié)果。

我們也發(fā)現(xiàn)了除了查詢出所有貧困戶學(xué)生姓名，還在末尾出現(xiàn)了一些#N/A符號，它表示查詢不到結(jié)果。我們可以通過在原公式外面套一個IFERROR函數(shù)即可解決問題。對應(yīng)公式為=IFERROR(VLOOKUP(ROW(A1),$A$2:$B$17,2,0),"")表示，如果查詢不到則留空。

注意事項：公式中的第一個參數(shù)一定要采用$A$2:$B$17這樣的絕對引用方式。

（2）借助“Excel擴(kuò)展函數(shù)包”查詢多個結(jié)果

第一步，先去“方方格子”官網(wǎng)下載“Excel擴(kuò)展函數(shù)包”，根據(jù)自己電腦安裝的OFFICE版本，選擇安裝32位還是64位，一般情況下安裝32位即可。

然后就可以在Excel中進(jìn)行一對多查詢了。

這里用到的函數(shù)是MLookup，它和VLOOKUP函數(shù)非常類似，比如查詢所有姓名為“李杰”的班級信息，只需在I3單元格輸入公式=MLookup(H3,A2:B17,2,-1)即可，返回的所有查詢結(jié)果會用逗號分隔。

MLookup函數(shù)最后一個參數(shù)表示返回第幾個查詢結(jié)果，如果為-1，則表示返回所有查詢結(jié)果。

這個MLookup函數(shù)雖然好用，但是也有缺點，它無法進(jìn)行逆向查詢，比如無法查詢出所有貧困戶學(xué)生的姓名。其原因在于MLookup不支持?jǐn)?shù)組查詢，所以通過重整數(shù)組順序進(jìn)行逆向查詢的方法是行不通的。

（3）用INDEX+SMALL+IF函數(shù)組合查詢多個結(jié)果

其實一對多查詢在日常使用中很少用VLOOKUP函數(shù)，更多的則是使用INDEX+SMALL+IF函數(shù)組合來實現(xiàn)。

先在I3單元格輸入如下公式，然后按CTRL+SHIFT+ENTER三鍵確定，最后用鼠標(biāo)拖動向下填充即可。

=IFERROR(INDEX($A$1:$A$17,SMALL(IF($E$3:$E$17=$H$3,ROW($A$3:$A$17)),ROW(A1))),"")

這是一個數(shù)組公式，因為相對比較復(fù)雜，就不做詳細(xì)解釋，其主要思路是用SMALL和IF兩個函數(shù)配合，查詢對應(yīng)行號，然后用INDEX函數(shù)定位對應(yīng)行的數(shù)據(jù)。

1. 多對一查詢

“多對一查詢”又叫“多條件查詢”，比如根據(jù)【姓名】和【班級】查詢對應(yīng)的身份證號。

如下圖所示，叫“李杰”的同學(xué)有兩個，無法根據(jù)【姓名】這一個條件精確定位其【身份證號】，還需要一個【班級】條件。

這個時候VLOOKUP就有些不太好用了，還得LOOKUP函數(shù)來救場。如下圖所示，我們在J3單元格輸入公式=LOOKUP(1,0/((A2:A17=H3)*(B2:B17=I3)),C2:C17)，回車確定，這樣就可以查詢到2班李杰的身份證號。

LOOKUP函數(shù)在進(jìn)行多條件查詢中的應(yīng)用可以套用如下公式：

LOOKUP(1,0/(條件1*條件2*...*條件n),返回結(jié)果的區(qū)域)

LOOKUP函數(shù)有一個特點，就是如果找不到查詢值，則返回查詢區(qū)域中小于或等于查詢值的最大值。正是利用LOOKUP函數(shù)的這個特點，才能實現(xiàn)多條件查詢。

1. 多對多查詢

多對多查詢可以看做是“一對多查詢”和“多對一查詢”的組合，這里用前面介紹的INDEX+SMALL+IF函數(shù)組合來做。

如下圖所示，在J3單元格輸入如下公式：

=IFERROR(INDEX($A$1:$A$17,SMALL(IF(($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3),ROW($A$3:$A$17)),ROW(A1))),"")

然后，還是用CTRL+SHIT+ENTER三鍵確定輸入。

其實參考前文介紹的“用INDEX+SMALL+IF函數(shù)組合查詢多個結(jié)果”，這個公式唯一的變化就是把條件由$E$3:$E$17=$H$3變成了($B$3:$B$17=$H$3)*($E$3:$E$17=$I$3)，即兩個條件相乘的形式?，F(xiàn)在如果讓你根據(jù)三個條件查詢，也是輕而易舉的，是需要稍微修改上述公式即可。

六、總結(jié)

Excel中的數(shù)據(jù)查詢是一個相對比較大的話題，文章中介紹的LOOKUP系列函數(shù)查詢應(yīng)用示例，僅占其中的一小部分，更多的相關(guān)內(nèi)容，以后再慢慢給大家分享。也歡迎大家分享自己在日常辦公中的一些Excel使用經(jīng)驗。

關(guān)于文中的一些相對復(fù)雜的公式并沒有做太過深入的解讀，因為任何一個公式單獨拿出來講，其都足以寫出一篇長文。但如果有朋友對其感興趣，想深究其原理，也可以私信交流。