EXCEL VLOOKUP終極教學：基本用法+多條件範例、#NA排除等
EXCEL VLOOKUP終極教學：基本用法+多條件範例、#NA排除等
VLOOKUP 全名為 「Vertical Lookup」，也就是垂直往下找資料的意思，雖然也有 HLOOKUP 以及XLOOKUP的存在，但是因為 VLOOKUP 大家用習慣了，因此使用頻率非常高，是職場上不可不學的30個函數之一。
本文整理了 VLOOKUP 的用法範例、跨工作表 / 檔案操作、多條件、回傳多筆資料、比對兩工作表差異等進階用法、以及如何排除 #NA 等常見錯誤。一步步帶你了解如何使用，並馬上應用到工作中，大幅提升工作效率。
那麼我們就開始吧！
VLOOKUP 函數基本用法
以下是 VLOOKUP 函數的語法說明：
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
引數名稱 解釋
lookup_value 指定查找資料的名稱或儲存格位置
table_array 選定欲查找之範圍
col_index_num 想回傳第幾欄？
[range_lookup] 0：完全符合／1：部分符合（選填）
使用範例：假設 A 欄是學生姓名，B 欄是分數。我們要查找「吳芝正」，並回傳分數，VLOOKUP 函數可以這樣寫：
=VLOOKUP("吳芝正",A:B,2,0)
引數名稱 解釋 範例
lookup_value 指定查找資料的名稱或儲存格位置 “吳芝正”
table_array 選定欲查找之範圍 A:B
col_index_num 想回傳第幾欄？ 2
[range_lookup] 0：完全符合／1：部分符合（選填） 0
如果還是不太懂的話，那我們來各個了解 VLOOKUP 的4個引數的規則與限制。
一、lookup_value（指定查找資料名稱）
第一個引數要輸入欲查找資料的「名稱」或是「儲存格位置」。
我們在 E2 中輸入「=VLOOKUP」，並且接著選定要查找的儲存格「D2」（或者輸入”吳芝正”）：
=VLOOKUP(D2,
=VLOOKUP("吳芝正",
二、table_array（選定查找範圍）
第二個引數，我們可以選定查找的範圍，告訴 EXCEL：我要在這個範圍中找到第一個引數輸入的資料。這個範圍可以在同一張工作表內，不同工作表，甚至是不同的活頁簿（檔案）之間。
接續前面的例子，我想要查找的範圍是該工作表的 A 到 B 欄，因此 VLOOKUP 函數的第二個引數就要拉一個「A:B」：
注意！VLOOKUP 有個很強硬的限制：第一個引數一定要在查找範圍的最左邊。沿用前面的範例，既然我們第一個引數輸入了「吳芝正」，那範圍就一定得要從姓名的欄位開始拉，否則就會回傳 #N/A!：
=VLOOKUP("吳芝正",A:C,3,0) //顯示#N/A!
三、col_index_num（選定回傳第幾欄）
選完範圍之後，我們就要告訴 EXCEL 要回傳範圍中的第幾欄。
在這個例子中，我想要回傳的是學生的分數，也就是範圍中的第 2 欄，那麼我就要輸入「2」：
注意！是「範圍中的第幾欄」，而不是「在這張工作表的第幾欄」！是相對位置的概念，不是絕對位置。
四、[range_lookup]（完全符合/部分符合）
第 4 個引數，我們要確認資料的比對方式，要「完全符合」還是「部分符合」：
- 完全符合：輸入 0 / FALSE
- 部分符合：輸入 1 / TRUE / 不輸入
以下會分別解釋「完全符合」和「部分符合」的用法與差異。
完全符合（0 或 FALSE）
基本上，大多數情況都適用「完全符合」，也就是 VLOOKUP 查找資料名稱時一定要找一字不差的值，直到找到後才會回傳值。
這也代表資料名稱一定要存在而且名稱相同，就算差了一個空格也不行，否則就會回傳 #N/A!，發生找不到相符資料的情況。
要設定完全符合，我們只要在 VLOOKUP 的第4個引數輸入「0」或者「FALSE」就可以了：
=VLOOKUP(D2,A:B,2,0)
部分符合（1 或 TRUE）
部分符合就比較複雜了。部分符合的邏輯是：找出最接近且小於等於查找資料的「數值」。
使用範例：假設我們今天想要知道用 400 塊最多能買到幾入的馬桶清潔碇，那麼我們就可以利用「部分符合」找出最接近 400 的值並且回傳數量。以下為詳細的步驟：
STEP. 1：我們先將網路上蒐集到的定價貼在 EXCEL 上（A 與 B 欄）。
STEP. 2：用 EXCEL 的「排序與篩選」功能將資料的A 欄由小到大排序。
注意！將資料由小到大排序非常重要，否則 VLOOKUP 最後回傳的值會是錯的。（因為 VLOOKUP 的查找邏輯是垂直由上往下找，只要找到超過 400 的值就停止查找，所以我們必須要將資料由小到大排序）
STEP. 3：將資料由小到大排序之後，就可以在 VLOOKUP 的第 4 個引數中輸入「1」或是「TRUE」囉：
=VLOOKUP(D2,A:B,2,1)
設定「部分符合」 的 VLOOKUP 函數會先找到最接近且小於等於 400 的值，也就是本例的「399」，接著再回傳 B 欄的「30入」。
VLOOKUP 不同工作表 / 不同檔案查找資料
VLOOKUP 函數在「跨工作表」與「跨檔案」的操作其實跟在同一個工作表的步驟一模一樣。
可以參考以下的 GIF ：
詳細操作步驟為：
2. 輸入 VLOOKUP 函數
4. 第一個引數：點一下「吳芝正」。
6. 第二個引數：直接換到另一個工作表，拉想要查找的範圍（記得欲查找的資料一定要在範圍的最左邊）。
8. 第三個引數：輸入要回傳範圍中的第幾欄。
10. 第四個引數：輸入0（完全符合）或1（部分符合）。
12. 按下Enter
引數名稱 解釋 範例
lookup_value 指定查找資料的名稱或儲存格位置 B3 或 “吳芝正”
table_array 選定欲查找之範圍 全校成績單!B:C（「全校成績單」工作表的 B 到 C 欄）
col_index_num 想回傳第幾欄？ 2
[range_lookup] 0：完全符合／1：部分符合（選填） 0
而跨 EXCEL 檔案的操作也一樣，輸入第 2 個引數時直接換到另一個 EXCEL 檔案拉範圍就好。
VLOOKUP 進階用法 1 – 多條件查找資料（兩欄條件）
假設我們今天有各家服飾單品的價格，但我們今天想要回傳同時符合「GOOPI」與「帽T」的價格。我們可以怎麼做呢？
其實我們只要「將 2 欄併成 1 欄」，VLOOKUP 就可以一次查找 2 個條件的資料。
Step.1：我們先在資料左邊新增一欄「廠牌&單品」的欄位。
Step.2：利用CONCAT 函數（舊稱 CONCATENATE）合併廠牌與單品的名稱：
=CONCAT(B2,"-",C2)
Step.3：最後再利用 VLOOKUP 查找連結後的資料名稱就可以一次查找兩個條件了：
=VLOOKUP("GOOPI-帽T",A:D,4,0)
VLOOKUP 進階用法 2 – 多條件查找資料（十字條件）
有時候資料一多起來，我們會不知道 VLOOKUP 到底該回傳第幾個欄位，如下圖：
如果想要查找同時符合「RO逆滲透濾水器」以及「Aug」的資料，可以怎麼做呢？
其實，我們可以利用 VLOOKUP+MATCH 函數，直接回傳 8 月的資料，而不需要數出 8 月在第幾欄。
Step. 1：利用MATCH 函數先算出「Aug」位於範圍中的第幾個欄位：
=MATCH("Aug",A1:M1,0)
Step. 2：取得「Aug」位於第幾個欄位後，將這段 MATCH 函數丟進 VLOOKUP 回傳欄位的引數中：
=VLOOKUP("RO逆滲透濾水器",A1:M6,MATCH("Aug",A1:M1,0),0)
VLOOKUP 進階用法 3 – 回傳多筆資料
一般情況下，VLOOKUP 只會回傳一筆資料。
但如果你的 EXCEL 是支持「動態陣列」版本（2021 或 365 以上），就可以利用動態陣列一次回傳多個欄位！
使用範例：利用 VLOOKUP 回傳「李大華」的「部門」與「薪水」：
=VLOOKUP("李大華",A:C,{2,3},0)
{ 2,3 } 表示「先回傳第 2 欄，再回傳第 3 欄」。而如果要回傳更多筆資料，直接在矩陣裡面繼續新增欄位即可：
使用範例：利用 VLOOKUP 回傳「李大華」的「部門」、「薪水」、「評級」：
=VLOOKUP("李大華",A:D,{2,3,4},0)
VLOOKUP 進階用法 4 – 比對兩個工作表差異
我們可以利用 VLOOKUP 找不到完全符合的資料名稱時，會回傳 #N/A 的原理，來找兩筆資料的差異。
使用範例：假設現在有資料 A 與資料 B 兩筆資料，我們想要確認資料 B 中的每一筆資料在資料 A 中有沒有出現過：
Step. 1：在資料 B 的比對結果中，輸入 VLOOKUP 函數，來查找資料 A 中的「apple」，並回傳第 1 欄：
=VLOOKUP(A2,資料A!A:A,1,0)
Step. 2：接著，將 VLOOKUP 函數丟進 ISNA 函數，將「#N/A」回傳「TRUE」，將「非 #N/A」回傳 FALSE：
=ISNA(VLOOKUP(A2,資料A!A:A,1,0))
Step. 3：再把 ISNA 放進IF 函數的第一個引數中，讓 IF 函數去判斷如果為 TRUE，則回傳「X」，否則回傳「V」。
=IF(ISNA(VLOOKUP(A2,資料A!A:A,1,0)),"X","V")
VLOOKUP 函數排除 #N/A 方法
以下列舉了我遇到的 4 種出現 #N/A 的原因與解決方法，如果你有發現其他可能導致 #N/A 的原因，也都非常歡迎留言和我說。
可能性 1：VLOOKUP 確實查無相符資料，有可能是因為資料打錯字而沒有發現。
解決方法：針對出現#N/A的資料名稱片段，利用尋找與取代功能（CTRL+F）去確認是否有相符資料。
可能性 2：資料中有出現多餘空格而沒有發現（如文字的最後面），造成「有資料卻找不到」的情況出現。
解決方法：用 TRIM 函數將多餘的空格移除後再查找。
=TRIM(A3)
可能性 3：VLOOKUP 的查找範圍最左邊並不是要查找的資料。
解決方法：重新設定函數，要查找的資料一定要在查找範圍的最左邊。
可能性 4：可能是資料格式的問題。如 GA4 下載的數據有時候 VLOOKUP 就是找不到。（這個真的會發瘋）
解決方法：建議可以開啟新的欄位或工作表，並且用「=A1」、「=A2」、「=A3」，或者結合 TEXT 或 VALUE 函數將資料傳到其他的地方。
VLOOKUP 常見錯誤（#N/A、#REF! 、#VALUE!）
- #N/A：指 VLOOKUP 函數找不到對應的資料，此時應該注意大小寫、字之間的空白格，或者確定欲查找的資料名稱確實存在。
- #REF!：通常發生在 col_index_num 的值> table_array 欄數，或者原本的資料有遭到更動或刪除過。
- #VALUE!：通常發生在 col_index_num 的值 <1時，或者 [range_lookup] 沒有辦法被有效判讀是 TRUE 還是 FALSE。
VLOOKUP 溫馨提醒
- 如果資料是呈水平排序，可以將資料旋轉貼上（又稱轉置）再執行 VLOOKUP，或者使用 HLOOKUP 函數
- 在輸入下一個引數時記得加上「,」
- 如果有重複的資料名稱，VLOOKUP 只會回傳第一個找到的資料
- 選取範圍時，要特別留意有沒有欄位被隱藏