這也許是史上最好最全的VLOOKUP函數教程

Excel不加班，讓你不再因為Excel問題而加班。如果你有Excel問題不能解決可以私聊我，我將用心替你解決。如果你喜歡盧子寫的原創文章，請分享給你的朋友，讓更多人一起進步！

函數中最受歡迎的有三大家族，一個是以SUM函數為首的求和家族，一個是以VLOOKUP函數為首的查找引用家族，另外一個就是以IF函數為首的邏輯函數家族。根據二八定律，學好這三大家族的函數，就能完成80%的工作。

現在一起來學習VLOOKUP函數，讓關於查找的煩惱一次全解決！

1、根據番號精確查找俗稱。

=VLOOKUP(D2,A:B,2,0)

VLOOKUP函數語法：

=VLOOKUP(查找值,查找區域,返回查找區域第N列,查找模式)

VLOOKUP函數示意圖。

2、屏蔽錯誤值錯誤值查找。

=VLOOKUP(D2,A:B,2,0)

VLOOKUP函數如果查找不到對應值會顯示錯誤值#N/A，這個看起來很不美觀。這時可以在外面加個容錯函數IFERROR，如果是2013版本那就更好，可以用IFNA函數，這個是專門處理#N/A這種錯誤值。

=IFERROR(VLOOKUP(D2,A:B,2,0),"")

=IFNA(VLOOKUP(D2,A:B,2,0),"")

函數語法：

=IFERROR(表達式,錯誤值要顯示的結果)

說白了就是將錯誤值顯示成你想要的結果，不是錯誤值就返回原來的值。IFNA函數的作用也是一樣，只是IFERROR函數是針對所有錯誤值，而IFNA函數只針對#N/A。

3、按順序返回多列對應值。

通過上面的例子，我們知道可以通過更改第3參數，返回各項對應值如：

=VLOOKUP($A13,$A$1:$F$10,2,0)

=VLOOKUP($A13,$A$1:$F$10,3,0)

如果項目少，更改幾次參數也沒什麼，但項目多時，肯定不方便。如圖 5?103所示，可以通過ROW、COLUMN產生行列號，從而得到1，2，……，n的值。

=VLOOKUP($A13,$A$1:$F$10,COLUMN(B1),0)

因為這裡是同一行產生序號，所以用COLUMN函數。

4、按不同順序返回對應值。

這回看來只能手動更改第3參數了，COLUMN完全派不上用場。

NO！每當你覺得操作繁瑣時，就要停下來思考，也許Excel本身存在這個功能，只是自己一時想不到或者不知道而已。列號不管千變萬化，在數據源的位置始終不變，利用這個特點可以去搜索一下看看有什麼函數可以解決。

在「搜索函數」文本框輸入：位置，單擊「轉到」按鈕，就會出現跟位置有關的函數，查看每個函數的說明，找到我們需要的，如MATCH函數，返回符合特定值特定順序的項在數組中的相應位置，單擊「確定」按鈕。

在彈出的「函數參數對話框」中嘗試填寫相應的參數，每個參數的作用下面都有相關說明，填寫後會出現計算結果3，也就是訂單數在區域中是第3列。嘗試下更改第1參數為C12（俗稱），計算結果是2，也就是區域中第2列。經過嘗試，知道這個函數是我們要找的那個函數，單擊「取消」按鈕，返回工作表。

在單元格再做最後一次驗證。

到這一步已經十拿九穩了，將公式設置為：

=VLOOKUP($A13,$A$1:$F$10,MATCH(B$12,$A$1:$F$1,0),0)

5、根據番號逆序俗稱。

幫助提到VLOOKUP函數只能按首列查找，不能逆向查找，既然如此，那就得想辦法將非首列的區域轉換成首列。怎麼轉換區域呢，這時IF函數就派上用場。一步步來了解IF函數的轉換。

看看好友傳遞如何趣聊IF函數，吃貨的福音。

IF函數其實只有一個條件來判斷是否符合條件，返回FALSE和TRUE兩種結果。

當菜只有分甜的或鹹的2種口味時，甜味是紅燒肉，鹹味是醬油肉。

盲人吃飯時，看不到是什麼菜。當別人問盲人：「你現在吃的什麼菜？ 是鹹的嗎？如果是鹹的，就是醬油肉，如果不是鹹的就是紅燒肉。」（給定判斷條件：鹹味）盲人剛好在吃紅燒肉，於是就咂吧著嘴說：「恩，好吃，不是鹹的！是紅燒肉」（根據提問的要求，不符合鹹的）假如要是盲人當時是在吃醬油肉呢,一定回答；「是的，鹹的，是醬油肉」（條件為真，是！TRUE）。盲人根據口感，結合提問者說的條件，就知道自己吃的是紅燒肉還是醬油肉了。

把這段話用公式來寫：

=IF(A1="鹹的",A2,B2)

翻譯：是鹹的嗎？要是（TRUE），就是醬油肉，要是不是鹹的（FALSE），就是甜的紅燒肉。

A1="鹹的"這個條件也可以直接換成TRUE或者FALSE。

=IF(TRUE,A2,B2)

因為滿足條件，所以返回A2的對應值醬油肉。

=IF(FALSE,A2,B2)

因為不滿足條件，所以返回B2的對應值紅燒肉。

其實TRUE=1，FALSE=0，所以可以直接用1跟0表示。

=IF(1,A2,B2)

=IF(0,A2,B2)

IF函數不止可以返回1個單元格的值，也可以返回多個單元格的值。

=IF({1,0},A2,B2)

=IF({0,1},A2,B2)

選擇兩個單元格輸入，按Ctrl Shift Enter三鍵結束。條件為{1,0}，返回A2:B2的對應值順序不變；條件為{0,1}，返回A2:B2的對應值，順序對換。也就是說通過改變1跟0的位置，可以調換兩單元格的前後位置。

看到這裡，知道IF函數通過改變1,0可以調換單元格的順序，如果要改變區域的順序也是可以實現的。

用IF函數重新構造的新區域，是多單元格數組公式，記得按Ctrl Shift Enter三鍵結束，否則出錯。

新區域：

=IF({1,0},B2:B10,A2:A10)

所以公式可以變成：

=VLOOKUP(A13,新區域,2,0)

兩個公式合併，大功告成。

=VLOOKUP(A13,IF({1,0},$B$2:$B$10,$A$2:$A$10),2,0)

6、根據俗稱跟訂單號兩個條件查詢完成情況。

正常情況下VLOOKUP函數是不能多條件查詢，通過IF函數的學習，我們知道IF函數可以重新構造區域，這裡就再次用IF構成一個區域。

新區域：

=IF({1,0},A2:A9&C2:C9,E2:E9)

所以公式可以變成：

=VLOOKUP(A12&B12,新區域,2,0)

兩個公式合併，大功告成，記得按Ctrl Shift Enter三鍵結束。

=VLOOKUP(A12&B12,IF({1,0},$A$2:$A$9&$C$2:$C$9,$E$2:$E$9),2,0)

7、根據俗稱的第一個字元查找番號。

=VLOOKUP(D2&"*",A:B,2,0)

星號（*）是通配符，代表所有字元，問號（?）代表一個字元。D2&"*"就是開頭包含D2的意思。

8、根據區域判斷成績的等級。

藉助輔助列的話，很容易查詢等級，只需將VLOOKUP函數的第四參數設置為1或者省略即可。

=VLOOKUP(E2,A:C,3)

如果不用輔助列，估計很多人看到這條公式就得哭了，得結合前面所有函數知識才能完成，有興趣的朋友可以自己去研究。

=VLOOKUP(E2,IF({1,0},--LEFT(B$2:B$5,FIND("-",B$2:B$5)-1),C$2:C$5),2)

前陣子無意間發現了IMREAL函數，所以不用輔助列的數組公式可以稍微簡單一點。

=VLOOKUP(E2,IF({1,0},IMREAL(B$2:B$5&"i"),C$2:C$5),2)

IMREAL函數是計算複數的實部係數的函數，作用就是提取區間的下限。

通過這8個疑難，基本上的查詢問題都能夠解決。

開心嗎？一下搞定8大疑難！

盧子簡介：20萬讀者支持的《Excel效率手冊 早做完，不加班》系列叢書作者，十年的Excel職場經驗，精通Excel函數和數據透視表。

推薦閱讀：