excel的vlookup函式怎麽使用?

全文 5000+字，前方高能，幹貨預警 ！花了一個周末的時間，整理了 VLOOKUP 函式最全的 16 種用法，借著這個話題分享給大家，希望對大家有幫助呀，收藏的同時不要忘記點贊呀~

16 個技巧劃分成了 3 個部份： 初級篇、中級篇和高級篇 ，並且每個用法都有案例+詳細解釋，整體大綱如下：

持續更新中，還有漏的技巧，大家可以評論區 @指北針 ，我會更新上來~

當然只看圖片不練習怎麽也學不會 VLOOKUP 的，關於這 16 種用法，我也整理了一份練習檔，分享給大家，希望對大家有幫助呀，→這裏： VLOOKUP的這16種用法，強到沒朋友！(練習下載)

另外不少小夥伴私信咨詢有沒有零基礎學習 Excel 函式的書籍推薦，對於零基礎的小夥伴，可以看下這 3 冊書，剛好京東暑假讀書優惠（適合零基礎）：

另外我也分享了近 500+ 篇 Excel/Word/PPT 類的技巧，也是超級有用的學習資料：

01. VLOOKUP基礎語法

要了解一個 Excel 函式，第一個學習的肯定是函式的語法結構，VLOOKUP 共有 4 個參數，參數語法如下：

= VLOOKUP(尋找的值, 尋找區域, 返回值所在列數, [匹配模式])

這 4 個參數的解釋如下：

1. 尋找的值 ：要尋找的詞或單元格參照；
2. 尋找區域 ：包含尋找欄位和返回欄位的單元格區域，尋找欄位必須在尋找區域的第 1 列；
3. 返回值所在列數 ：返回值在尋找區域中的列數；
4. 匹配模式 ：0 為精確匹配，1 為模糊匹配；

以上就是 VLOOKUP 函式的基礎語法， 作為 Excel 中使用頻率最高、資質最老的一批函式，相容性也非常強，基本上市面的 Excel 和 WPS 版本都能完美相容 。

02. VLOOKUP 單條件查詢

首先是 Excel 中最常用，也最基礎的用法「單條件查詢」，只需要根據語法規則將對應單元格區域錄入到公式中即可。

案例：「 根據工號將對應薪資進行查詢匹配 」，操作也很簡單，輸入公式：

現在就可以輕松將工號為 6 的員工薪資匹配出來了，如下：

簡單解釋下這個公式的含義：

1. p：要尋找的單元格參照；
2. A2:F11：尋找區域單元格區域；
3. 6：薪資欄位位於尋找區域的第 6 列；
4. 0：代表精準匹配；

所以並不是所有的 VLOOKUP 函式都是返回第 2 列的，還要根據實際的工作場景來返回。

03. 遮蔽檢索不到錯誤

VLOOKUP 在找不到數據的情況下會返回 #N/A 錯誤，如果想將錯誤遮蔽掉，或者輸出其他提示資訊，用 IFERROR+VLOOKUP 組合即可實作。

案例：「 匹配不到數據的時候返回 / 提示 」，使用公式：

如下，尋找區域並沒有工號 11 的員工，所以返回了「/」：

另外 VLOOKUP 對數據的匹配是極為嚴格的，哪怕差了一個空格、一個不可見字元，都會認為是不一樣的，例如： 「芒種學院」和「芒種學院 」並不相等，因為差了一個空格 。

04. 反向匹配技巧

講解語法的時候提到過「尋找詞必須在尋找區域的第 1 列」，但是在實際工作中，數據往往並不能完美符合需求，這個時候可以利用 IF 陣列公式來調換列。

案例：「 根據姓名尋找對應的工號 」，使用如下公式：

這裏用到了「IF陣列公式」，將 B 列和 A 列的位置進行互互換，然後重新組成一個新的區域，這樣 B 列就跑到 A 列前面了，如下：

另外這個公式是「陣列公式」，如果使用的版本並非 365，還需要按三鍵 Ctrl+Shift+Enter 結束，並不能直接 Enter 結束，大機率結果會出錯。

05. 模糊關鍵詞檢索

除了檢索確定的關鍵詞，VLOOKUP 也可以配合通配符實作「 模糊關鍵詞匹配 」，在 Excel 中共有 2 個通配符：

1. 星號* ：代表任意個字元，可以是 0、1、N 個；
2. 問號? ：代表任意 0 個或者 1 個字元；

案例：「 匹配出包含陽字姓名的員工薪資 」，可以使用如下公式：

這樣即可匹配出數據中包含「陽」字的相關數據，如下：

06. 代替 IF 數值區間匹配

針對尋找詞為「數位」的場景，VLOOKUP 還可以實作區間匹配，完美代替 IF 巢狀地獄。

案例：「 根據不同的銷售業績層級計算提成 」，使用如下公式：

不過使用這個公式是有條件限制的，必須符合這 2 個條件方可使用：

1. 尋找詞必須為數位；
2. 尋找區域的數位必須從小到大進行排序；

公式實作效果如下所示，輕松將提成計算出來：

07. 去除空格/不可見字元匹配

如果碰上不規範的數據，明明肉眼看上去一模一樣，但是怎麽都匹配不出來，大機率是碰到了數據中存在空格、數據中存在不可見字元。

這個時候可以使用 SUBSTITUTE 或者 CLEAN 函式進行處理後匹配，例如：

這個公式的技巧在於使用 SUBSTITUTE 將空格替換成空，然後再進行匹配，確保數據的一致性。

另外如果碰到的是不可見字元，則可以使用 CLEAN 函式清除。

再分享多一個 判斷是否存在「空格」或者「不可見字元」的技巧，可以使用 LEN 函式判斷數據的長度，如果長度不一致，則表明存在 。

08. 橫向匹配返回多列

前面分享的 7 個技巧都是返回單個結果的，如果返回的數據是 N 列呢？難道還要寫 N 個公式，如果數據是連續的，可以利用 COLUMN+VLOOKUP 配合實作返回多列。

案例：「 根據工號返回姓名/部門/性別/年齡/薪資 」等欄位資訊，使用如下公式：

分別向右向下拖動完成填充即可，這裏主要用到了 COLUMN 函式動態生成 2/3/4/.../N ，避免將返回列數固定寫死，從而達到返回多列的效果。

如果數據並非連續的呢？這個時候就可以考慮使用 MATCH 函式來定位數據所在的列數了。

09. 匹配多表查詢

如果匹配的表有多張，並且可以透過條件來判斷數據是位於哪張表中，那麽可以使用 VLOOKUP+IF 公式來實作需求。

案例：「 不同的城市位於不同的表中，尋找廣州工號 8 員工的薪資 」，利用如下公式：

這裏用到了 IF 函式判斷 A2 單元格的數值是否為深圳，是則返回 A6:F15，否則返回返回 H6:M15，實作了動態變更尋找區域。

當然這個技巧只適用於表的數量較少的情況下，如果表數量比較多，使用 IF 會讓公式看起來很長，而且還容易出錯，後面會有另外的技巧來實作。

10. 多條件匹配尋找

前面分享了 9 個技巧，不過都是單條件尋找，多個欄位才能確定唯一一條數據的情況下，利用 VLOOKUP 也可以輕松實作，同樣用到了 IF 陣列公式。

案例：「 根據姓名+部門尋找員工對應薪資 」，利用如下公式：

這裏用到了 2 個技巧：

1. 將 p&I2 拼接起來組成一個新欄位，並且該欄位是唯一的；
2. IF 陣列公式將 B 和 C 列也拼接到一起，然後和 F 列組成成新的尋找區域；

這樣就可以 巧妙地將「多條件」轉換成「單條件」 ，實作效果如下：

11. 尋找區域存在合並單元格

如果尋找區域中存在合並單元格，直接使用 VLOOKUP 匹配非常容易出錯，因為合並單元格只有最左上角的單元格存在數據，其他單元格並不存在。

這個時候就可以考慮使用 VLOOKUP+OFFSET+MATCH 來實作。

案例：「 根據部門+姓名尋找出員工對應的薪資 」，使用如下公式：

MATCH 函式用於定位部門所在的行號，然後使用 OFFSET 函式向下偏移獲得「動態尋找區域」，這樣就可以輕松實作我們的需求了：

當然這個案例會復雜很多，在實際工作中也盡量不要使用合並單元格來實作數據分組，無論是函式、透視表、圖表、排序、篩選等絕大部份功能對合並單元格都不太友好。

12. 尋找詞中存在合並單元格

如果在尋找詞中也存在合並單元格，可以巢狀使用兩個 VLOOKUP 函式完成需求。

案例：「 根據部門返回月度獎金 」，使用如下公式：

巢狀的 VLOOKUP 函式主要是用於找到 D 列截止至本行的最後一個非空值，這樣就可以將尋找詞一一匹配上，如下所示：

13. 尋找返回多個結果

關鍵詞和返回結果是 1:N 的關系，也可以使用 VLOOKUP 將所有結果全部返回，用到了 VLOOKUP+ROW+IF+INDIRECT 陣列公式配合。

案例：「 找出市場部所有員工的姓名 」，使用如下公式：

公式非常復雜，整體可以分為 4 個思路：

1. 用 ROW 函式生成序列；
2. INDIRECT+ROW 生成行數遞增的尋找區域；
3. COUNTIF 計算部門的個數，生成新編號；
4. IF 陣列公式將構建的數據組成一個新的尋找區域；

同樣是陣列公式，按 Ctrl+Shift+Enter 結束後向下拖動填充，得到如下結果：

這個公式的相容性雖然很好，不過維護起來實在費勁，如果是 365 版本，可以直接使用 FILTER 函式即可實作需求，而且非常便捷。

14. 尋找結果返回同一單元格

上面的技巧是將尋找結果填充到不同的單元格中，如果想將結果合並到一個單元格內呢？

純粹利用 VLOOKUP 實作不了，但是可以配合輔助列，共有 2 個公式：

其中 G2 單元格的公式用到了「呼叫自身列」的技巧，將找到的數據依次拼接，最後使用二分法進行匹配，實作效果如下：

當然難度也非常大，如果版本比較新，可以考慮使用 TEXTJOIN+IF/FILTER 函式來實作，會非常簡單，而且更流暢一些。

15. 返回最後一個結果

如果返回的結果有多個，只想要最後一個，利用 VLOOKUP+IF 陣列公式也可以實作。

案例：「 找到市場部的最後一位職員 」，使用如下公式：

具體思路為當忽略 VLOOKUP 的最後一個參數時，函式會使用二分法進行尋找，同時用 0/條件 可以將不符合條件的數值變成錯誤值，符合的變成 0。

最後用 1 尋找最後一個 0 即可實作需求，是不是非常巧妙，實作效果如下：

16. 跨多Sheet表匹配

數據被分布在 N 張 Sheet 表中，而且並不確定尋找詞位於其中的哪張表，這個時候可以利用 INDIRECT+VLOOKUP 實作跨多 Sheet 表匹配。

案例：「 存在深圳/廣州/上海共計 3 張表，匹配出不同員工的薪資 」，使用如下公式：

公式非常長，不過邏輯非常簡單，利用 INDIRECT 函式將所有表的數據全部匯入進行匹配，找到非錯誤的值即可，如下。

當然針對這類表格，已經不建議使用 VLOOKUP 進行匹配了，不僅卡不說，還非常容易出錯，不過公式還是非常值得研究的。

可以的話，利用 PQ 將數據合並起來然後進行匹配，效果會更高。

好了，暫時先分享 16 個，後續還有用到其他的 VLOOKUP 技巧，我也會更新上來，希望對大家有幫助呀，收藏的同時不要忘記點贊哦~

我的其他高贊回答，也可以看下哦，都是一些非常有用的資料：

1. VLOOKUP 到底有多重要？
2. 如何制作圖表非常精美的 Excel 文件？
3. 工作中 Excel 哪個功能最實用？
4. 有哪些值得推薦的數據視覺化工具？
5. Excel 有多強大？你用 Excel 做的最酷的事情是什麽？
6. 如何快速學習數據透視表？
7. Excel 有哪些和 vlookup 一樣重要的函式或功能？
8. 怎樣才算精通 Excel？

如果覺得有用，點個贊再走唄~持續更新哦！！