Vlookup函式

是電子表格中的一個查詢匹配函式，適用於Excel與WPS各個版本。

在所有的函式中，vlookup函式應該是使用頻率最高的函式之一，對新手小夥伴來說，掌握它是十分有必要的。

本篇文章從其

基本用法、進階用法、匹配不出來原因分析、如何規避錯誤值、冷門用法科普

等五個維度來詳細介紹下vlookup函式，希望能夠幫助小夥伴們快速入門、以及加深對此函式的認識。

一、基本用法

語法解釋

vlookup函式一共有

4個引數

，引數1要搜尋的值、引數2搜尋區域、引數3返回的列數、引數4匹配模式，引數有點多，沒關係，我們用一個例項來看下每個引數的具體作用。

精準匹配

下圖中，根據左邊的資訊表，利用vlookup函式匹配出李白的年齡，G2單元格中的公式該如何輸入？

引數1：搜尋值，找誰？

雖然最終結果是獲取年齡欄位，但是查詢值並不是年齡，而是查詢“

李白

的年齡”，主語是“李白”，所以引數1、查詢值是“李白”，也就是

F2

單元格。

引數2：搜尋區域，去哪找？

重點來了：

查詢區域的第一列必須是搜尋值所對應的列

（查詢物件必須位於查詢區域的最左列），引數1搜尋值是“李白”，對應到左邊到表格就是“姓名”欄位，所以引數2搜尋區域必須以B列作為首列，以B列作為首列

向後拖動

到我們需要匹配的值所在列，也就是D列年齡列（也可以繼續向後拖動，只要包括年齡列就可以），所以引數為就是B：D列。

引數3：在第幾列？

這裡有個誤區，很多小夥伴覺得左邊的匹配表一共4列（A-D列），年齡列在D列，也就是第4列，所以引數3輸入了“4”，這是錯誤的。

引數3實際指的是

返回值（年齡）在搜尋區域中所處的列數

，也就是在引數2框選區域中所處的列數，上面我們框選了B：D列，

實際上僅框選了3列

，B是第一列、C是第二列、D是第三列。。。。所以返回值“年齡”在第三列，引數3輸入數字“3”。

引數4：匹配模式？

匹配模式分為

精準匹配

（輸入

0、FALSE或者省略

都可以）、近似匹配（輸入1或者TRUE），最常用的當屬精準匹配，也就是要完完全全找到“李白”這個人，而不是“李小白”、“李白小”，所以引數4輸入數字“0”，表示精準匹配，也可以直接省略或者輸入FALSE。

日常工作中，99%的情況下都是使用vlookup函式的精準匹配，也就是引數4大家可以預設輸入0。

透過以上例子，介紹了vlookup函式的最基本、最實用的用法，最容易出錯的兩點在於：

1、引數2/搜尋區域 的首列必須是返回值所在的列；

2、引數3/列數 必須是返回值在搜尋區域對應的列數。

近似匹配

上面說到99%的情況下，vlookup函式的引數4都是0，也就是精準匹配，那麼還剩下1%的情況，需要用到vlookup函式的近似匹配，在表格中也有非常合適的應用場景，那就是資料分組功能。

下圖中如何根據左側A-B列的評級表，去匹配每位同學所屬的評級？

首先建立一列輔助列，在G2單元格內輸入公式：=VLOOKUP（ F2，B：C，2，1），向下填充，完成區間匹配。

這裡利用vlookup函式近似匹配的功能，它會

返回小於等於查詢值的最大值。

查詢數字56，等於查詢 “小於等於56的最大值”，60/80/90都要大於56，只有0小於56，所以返回0對應的評級“不及格”； 查詢數字88，等於查詢 “小於等於88的最大值”，0/60/80都要小於88，但80最大，所以返回80對應的評級“良好”；

最重要的一點，利用vlookup函式近似匹配之前，需要對匹配表資料進行升序處理

，即上圖中先對輔助列B列升序（上圖中B列數字由小到大排序）。

因為vlookup函式近似匹配採用的是二分法，一般是從中間開始向上下兩端查詢，不斷二分，預設資料升序處理。

不升序會怎麼樣？

下圖中，匹配序列處於亂序狀態，利用vlookup函式近似匹配、返回小於等於查詢值

的

最大值，正確結果應該是a1，（6是小於等於6。5的最大值），但是函式返回結果卻是a2。

二分法中間開始查詢，序列中間值為7，7要大於查詢值6。5，所以繼續向上查詢，上面的1要小於6。5，查詢停止，返回1對應值。所以，在近似匹配的時候，切記要升序處理。

二、進階用法

進階用法主要是vlookup函式搭配輔助列或者其它函式，來實現資料匹配，常用的方法主要分為以下幾種。

在此之前，還有一個比較重要的概念就是

單元格引用

，在vlookup函式匹配的時候，引數1與引數2經常涉及到相對引用與絕對引用，十分重要。

Excel入門必看篇，單元格的相對引用、絕對引用與混合引用

多列查詢

1、結合column函式

下圖中，匹配各同學的性別、年齡、城市三個欄位，直接利用COLUMN函式（返回單元的列），動態返回需求列。公式

=VLOOKUP($F2,$A:$D,COLUMN(B2),0)

，

其中引數1與引數2皆涉及到單元格的引用。

2、結合match函式

上圖需要匹配的列與原表列的位置順序一樣，可以用COLUMN函式解決，如果順序不一致的話，需要結合match函式使用，公式=VLOOKUP（$F2，$A：$D，MATCH（G$1，$A$1：$D$1，0），0）

注意其中的引用方式。

match函式返回查詢值在陣列中的位置，比如查詢欄位“性別”，它在陣列“A1：D1”中處於第2位，所以vlookup函式引數三就等於2。

以上兩種方式都是透過

動態變更引數三

，從而完成多欄位匹配。

逆向查詢

由於vlookup函式的引數2、查詢區域首列必須為搜尋值對應的列，如果返回值所在的列在搜尋值對應的列前方的話，vlookup正常用法無法完成匹配。

下圖中，根據A-B列的資料來源，匹配出E列的學號，根據前文，搜尋區域只能從B列開始、向後拖動，但是返回值在A列，肯定查不到正確的結果。

這種情況我的建議是調整列的位置，比如在學號列前方複製插入姓名列，逆向轉正向匹配。

當然也可以使用INDEX+MATCH組合以及XLOOKUP函式解決逆向匹配的問題，這裡不是我們的討論範圍，感興趣的小夥伴可以自行了解。

多條件匹配

多個欄位匹配的情況，可以在源資料的基礎上，增加一列輔助列，利用“&”將各列資料進行連線。

模糊查詢

“*”是萬用字元，代表0到多個字元，“*”&D2&“*”則表示包含D2關鍵字的任意字串：

一對多查詢

下圖中，根據B-C列資料來源，匹配出E列部門的所有員工姓名。由於vlookup函式只能返回首個值，然後有多個員工，這裡利用COUNTIF函式將每個部門的個數進行編號。

輔助列公式

=B2&COUNTIF($B$1:B2,B2)

，

注意其中的絕對引用

。這樣每個部門都會被標上序號。

接著F2輸入公式

=VLOOKUP($E$2&ROW(A1),A:C,3,0)

，公式下拉，即可返回多個結果。

三、匹配不出來的原因

明明有資料，vlookup卻匹配不出來，在工作中是比較常見的。

第一種常見錯誤是匹配區域未絕對引用、發生變化所致。下圖中匹配年齡欄位。

在E2單元格內輸入公式

=VLOOKUP(D2,A2:B9,2,0)

，由於引數2未採用絕對引用，公式在下拉到“趙雲”的時候，引用區域變成了“A4：B11”，區域內並無此人，當然查詢不到資料。

正確的做法是：引數2引用區域採用絕對引用（選中引用區域，按下F4鍵）

第二種常見錯誤是匹配欄位中存在不可見字元，比如空白符，利用Ctrl+H將空白替換掉就可以了。

其它錯誤可以參考下面這篇文章。

明明有資料，為什麼我的VLOOKUP總是匹配不出來？

四、規避錯誤值

vlookup函式在未匹配到資料的情況下，函式會返回#N/A，如果想到規避這種錯誤值，可以利用IFNA或者IFERROR函式。

公式

=IFNA(VLOOKUP(D2,A:B,2,0),"查無此人")

，在vlookup函式外層巢狀一個IFNA函式，表示內層的函式表示式（引數1）結果為#N/A，則返回指定的值（引數2），否則返回表示式本身的結果。

IFERROR函式與IFNA用法一致，只不過前者比後者更加強大，關於Excel的錯誤型別總結，可以參考下面的文章。

Excel入門科普文，表格常見錯誤型別總結

五、冷門用法（僅作了解）

這裡的冷門用法是指公式寫起來比較麻煩、或者有更高效的函式可以替代，讓我們一起看看吧~

IF函式{1，0}用法，每位使用vlookup的童鞋不得不面對的問題。

比如vlookup函式逆向查詢，公式

=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)

這裡完全可以用match+index函式替代（高版本可用Xlookup）。

比如多條件匹配，公式

=VLOOKUP( E2&F2,IF({1,0},A:A&B:B,C:C),2,0)

，需要Ctrl+shift+Enter三鍵齊按完成公式的輸入。這裡也可以用sumif或者sumproduct函式替代。

vlookup函式搭配IF函式的{1，0}功能是不推薦大家使用的，如果想要了解IF{1，0}的具體原理，可以看下面這篇文章。

拜託，Vlookup函式的這個功能真的別再用了

最後再給大家分享一個vlookup的小技巧，提取固定數字字串的技巧。

下圖中，提取A列是首次出現的手機號碼，輸入以下公式：

=VLOOKUP（0，MID（A2&“a”，ROW（$1：$99），11）*{0，1}，2，0），Ctrl+shift+Enter三鍵。

好了，以上就是今天的分享，想要了解更多幹貨，點個關注喲，我們下期再見。