超級促銷週Claude Skills — 20% 折扣
Tips

如何在 Excel 中使用 VLOOKUP:完整指南

Powerdrill Bloom·
如何在 Excel 中使用 VLOOKUP:完整指南

若要在 Excel 中執行 VLOOKUP,請在儲存格中輸入 =VLOOKUP( 並加入四個部分。分別是要尋找的值、要搜尋的表格、要傳回的欄號,以及代表完全符合的 FALSE。例如,=VLOOKUP(B2,$F$2:$H$5,3,FALSE) 會尋找 B2 中的值,並傳回該表格的第三欄。

本指南將介紹 VLOOKUP 的運作方式、如何逐步建立公式,以及如何從另一個工作表查閱值。此外,還會說明完全符合與大約符合、常見錯誤,以及何時該改用 XLOOKUP。操作步驟源自 Microsoft 的支援頁面,擷取日期為 2026 年 11 月 13 日。

VLOOKUP 的功能

VLOOKUP 會在表格的第一欄中尋找特定值,並傳回同一列中的值。「V」代表垂直(vertical),因為它是向下搜尋整欄。

Microsoft 的支援頁面提供了兩個典型用途。您可以「透過零件編號查閱汽車零件的價格」,也可以「根據員工 ID 尋找員工姓名」。

常見的應用場景包括:

  • 價格。 透過產品代碼將產品價格帶入訂單清單中。
  • 姓名。 透過員工 ID 將員工姓名帶入報表中。
  • 類別。 在匯出的銷售資料中,為每一列新增地區或部門。
  • 費率。 從查閱表中尋找稅率或佣金比例。
  • 比對清單。 檢查某個清單中的項目是否出現在另一個清單中。

VLOOKUP 有一個決定性的規則。Microsoft 稱之為「VLOOKUP 的秘密」。您必須整理資料,使您要查閱的值「位於您要尋找的傳回值(金額)的左側」。

VLOOKUP 公式如何運作

此語法包含四個部分。Microsoft 將其列為:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

部分 代表意義 範例
lookup_value 您想要尋找的值 B2,產品代碼
table_array 要搜尋的範圍,且查閱值必須位於該範圍的第一欄 $F$2:$H$5
col_index_num 該範圍中要傳回的欄位,從 1 開始計算 3,代表價格欄
range_lookup FALSE 代表完全符合,TRUE 代表大約符合 FALSE

Microsoft 以一個範例來解釋欄數計算。若以 B2:D11 作為範圍,「您應該將 B 算作第一欄,C 算作第二欄,依此類推。」

最後一個部分是選填的,但省略它會有風險。Microsoft 表示:「如果您未指定任何內容,預設值將一律為 TRUE 或大約符合。」對於大多數的查閱(例如 ID 和代碼),您會需要使用 FALSE。

先整理您的資料

大多數 VLOOKUP 的問題都出在資料本身,而非公式。事先進行幾項檢查可以節省之後的時間。

  • 將查閱欄放在第一欄。 您要搜尋的值必須位於表格範圍的最左側欄。
  • 每列僅保留一個值。 每個代碼或 ID 在查閱表中應該只出現一次。
  • 將數字儲存為數字。 Microsoft 警告,如果數字被儲存為文字,「VLOOKUP 可能會傳回錯誤或非預期的值。」
  • 移除多餘的空格。 開頭或結尾的空格會導致無法比對。TRIM 函數可以移除這些空格。
  • 使用標題列。 清晰的標題有助於更輕鬆地計算欄號。

如果您的查閱欄位於您所需數值的右側,VLOOKUP 將無法讀取它。您可以移動該欄,或使用稍後會介紹的 XLOOKUP。

還有另外兩個習慣可以讓查閱更容易維護。第一,將查閱表格式化為 Excel 表格,或為其命名。Microsoft 指出,對於 table_array,「您可以使用具名範圍或表格。」像 Products 這樣的具名範圍比 $F$2:$H$5 更容易閱讀。第二,將查閱表放在獨立的工作表上。這樣一來,在編輯主要資料時,就不會有人不小心排序或刪除列。

如何逐步執行 VLOOKUP

以下是基本完全符合查閱的完整流程。在此範例中,訂單清單位於 A 到 C 欄,而產品表格位於 F 到 H 欄。

  1. 按一下要呈現結果的儲存格。 例如第一筆訂單旁邊的 D2。
  2. 開始輸入公式。 輸入 =VLOOKUP(。
  3. 加入查閱值。 按一下產品代碼 B2,然後輸入逗號。
  4. 加入表格範圍。 選取 F2:H5,按下 F4 將其變更為 $F$2:$H$5,然後輸入逗號。
  5. 加入欄號。 輸入 3 代表價格欄,然後輸入逗號。
  6. 選擇符合類型。 輸入 FALSE,關閉括號,然後按下 Enter。

完成的公式為 =VLOOKUP(B2,$F$2:$H$5,3,FALSE)。

接著向下填滿該欄的公式。按兩下 D2 角落的填滿控制點,Excel 就會將公式複製到最後一列。

步驟 4 對於填滿非常重要。Microsoft 的最佳做法建議使用絕對參照,這樣您就可以「向下填滿公式,使其始終指向完全相同的查閱範圍。」如果沒有錢字號($),範圍每次都會向下移動一列,導致後續的查閱失效。

在兩個工作表之間進行 VLOOKUP

查閱表通常位於另一個工作表上。VLOOKUP 可以透過在範圍中加入工作表名稱來處理這種情況。

Microsoft 的頁面包含了這個範例:

=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)

在這裡,'Client Details' 是工作表名稱,因為包含空格所以加上單引號。A:F 是該工作表上的範圍。此公式會查閱 A2 並傳回第三欄。

最簡單的建立方式是透過點選:

  1. 開始輸入 =VLOOKUP( 並按一下查閱儲存格。
  2. 輸入逗號,然後按一下另一個工作表的索引標籤。
  3. 選取該工作表上的表格範圍,然後按下 F4 將其鎖定。
  4. 完成欄號與 FALSE 的輸入,然後按下 Enter。

Excel 會自動為您寫入工作表名稱。只要在建立公式時同時開啟兩個檔案,相同的方法也適用於另一個活頁簿。

完全符合 vs. 大約符合

第四個引數會改變 VLOOKUP 的搜尋方式。選錯引數是導致錯誤結果的常見原因。

完全符合(FALSE 或 0)。 Microsoft 表示它會「在第一欄中搜尋完全相同的值」。如果該值不存在,結果將顯示 #N/A。適用於 ID、代碼、姓名以及任何必須完全一致的內容。

大約符合(TRUE 或 1)。 Microsoft 表示它會「假設表格中的第一欄已按數字或字母順序排序,然後搜尋最接近的值」。適用於範圍,例如稅率級距或成績等等界限。

以下是一個典型的大約符合案例。佣金表列出了 0、10,000 和 50,000 的銷售額門檻,對應的費率分別為 2%、3% 和 5%。一筆 32,000 的銷售額介於 10,000 到 50,000 之間,因此 VLOOKUP 會傳回 3%。

若要使其正常運作,門檻必須由小到大進行排序。Microsoft 警告,如果第一欄未經排序,「傳回的值可能會出乎您的意料。」

完全符合也支援文字的萬用字元。Microsoft 表示,在使用 FALSE 且查閱值為文字時,您可以使用問號和星號。「問號可比對任何單一字元。星號可比對任何字元序列。」其範例 =VLOOKUP("Fontan?",B2:E7,2,FALSE) 可以找到最後一個字母為任何字元的 Fontana。若要搜尋實際的問號或星號,請在前面輸入波浪號(~)。

修正常見的 VLOOKUP 錯誤

Microsoft 的頁面列出了最常見的問題及其原因。

錯誤 問題原因 如何修正
#N/A 使用 FALSE 時,找不到完全相同的值 檢查拼字、空格以及儲存為文字的數字
#REF! 欄號大於範圍中的總欄數 擴大範圍或減少欄號
#VALUE! 引數小於 1 檢查欄號是否為 1 或以上
#NAME? 通常是公式中的文字漏掉了引號 將文字加上引號,例如 "Fontana"
錯誤的值 在未排序的欄位上使用 TRUE 或空白的符合類型 使用 FALSE,或對第一欄進行排序

對於預期會出現的 #N/A 結果(例如尚未加入表格的新產品),可以將公式包裝在 IFERROR 中:

=IFERROR(VLOOKUP(B2,$F$2:$H$5,3,FALSE),"找不到")

這會顯示清晰的標籤而非錯誤訊息。請謹慎使用,因為標籤可能會隱藏真實的資料問題,例如拼錯的代碼。

Microsoft 也提到了較新版本 Excel 中的 #SPILL! 錯誤。當查閱值指向整欄(例如 A:A)時,就會發生此錯誤。請改為指向單一儲存格(例如 A2)。

VLOOKUP vs. XLOOKUP

Microsoft 現在建議使用較新的函數。其 VLOOKUP 頁面將 XLOOKUP 稱為「VLOOKUP 的改進版本」。並補充說明 XLOOKUP 「支援任何方向的搜尋,且預設傳回完全符合的結果」。

XLOOKUP 的語法為:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

在 XLOOKUP 中,相同的價格查閱公式為 =XLOOKUP(B2,$F$2:$F$5,$H$2:$H$5)。不需要計算欄數。

功能特色 VLOOKUP XLOOKUP
搜尋方向 查閱欄必須位於左側 傳回欄可以位於任一側
預設符合類型 大約符合 (TRUE) 完全符合
欄位參照 需要計算的欄數 直接選取的範圍
找不到時的訊息 需要搭配 IFERROR 內建 if_not_found 引數
適用版本 所有版本 不支援 Excel 2016 或 Excel 2019

最後一列非常關鍵。Microsoft 指出「XLOOKUP 不適用於 Excel 2016 和 Excel 2019」。如果您會與使用這些版本的人共用檔案,VLOOKUP 仍然是較安全的選擇。

針對較舊的版本,還有第三種選擇。Microsoft 的查閱指南指出「VLOOKUP 函數只能由左至右查閱值」。若要向左查閱,它建議「改為結合 INDEX 和 MATCH 函數」。MATCH 會尋找查閱值的列號,而 INDEX 則會傳回該列中任何欄位的值。此公式雖然較長,但適用於所有版本的 Excel。以價格範例來說,公式會是 =INDEX($H$2:$H$5,MATCH(B2,$F$2:$F$5,0))。

VLOOKUP 範例

這是一個簡單的辦公用品訂單。產品表格位於 F1:H5,標題列在第 1 列。

產品 ID 名稱 價格
P-101 筆記本 4.50
P-102 原子筆 1.20
P-103 釘書機 8.75
P-104 資料夾 2.30

訂單清單位於 A1:C5。每筆訂單包含訂單編號、產品 ID 和數量。

訂單 產品 ID 數量 價格 (VLOOKUP) 小計
1001 P-103 2 8.75 17.50
1002 P-101 10 4.50 45.00
1003 P-104 5 2.30 11.50
1004 P-105 1 #N/A —

D2 中的公式為 =VLOOKUP(B2,$F$2:$H$5,3,FALSE),並向下填滿至 D5。E2 中的小計為 =C2*D2。

訂單 1004 傳回 #N/A,因為產品表格中沒有 P-105。這是很有用的資訊,而不是公式出錯。這代表有人需要新增該產品或修正代碼。

三個有效項目的總額為 17.50 + 45.00 + 11.50,即 74.00。一旦將 P-105 新增至表格中,其價格就會自動顯示。

透過 AI 在 Excel 中執行 VLOOKUP

當您要比對具有許多欄位的大型檔案時,撰寫和檢查查閱公式非常耗時。AI 工具可以為您比對表格,並標記不符合的列。

以下是使用 Powerdrill Bloom 的三個步驟。

步驟 1:上傳您的 Excel 檔案

上傳這兩個檔案,或包含這兩個表格的一個活頁簿。合併 CSV 檔案頁面指出,您可以「根據特定的欄位比對、排序順序或您定義的其他條件」來量身定制合併方式。

確保兩個表格共享一個關鍵欄位,例如產品 ID 或員工 ID。

上傳兩個 Excel 檔案以在 Powerdrill Bloom 中進行 VLOOKUP 樣式的比對

步驟 2:使用自然語言要求進行比對

描述您需要的查閱需求。例如:「根據產品 ID 將產品表格中的價格新增至每筆訂單,並列出任何未比對成功的訂單。」

對於比對後的分析,Excel AI 助手會「自動識別您 Excel 資料中的趨勢、相關性和關鍵指標」。

步驟 3:檢查結果並匯出

抽樣檢查幾列與來源表格的對比,特別是任何未比對成功的列。然後下載合併後的檔案或要求提供摘要報告。未比對成功的 ID 簡短清單通常是最有用的輸出,因為它顯示了哪些紀錄需要在來源端進行修正。

關於另一種無需公式即可合併檔案的方法,請參閱我們的指南:如何在沒有 VLOOKUP 的情況下合併兩個 Excel 檔案。

在匯出合併檔案前檢查已比對的列

應避免的常見錯誤

這些是使用 VLOOKUP 時最常出現的問題。

  • 遺漏符合類型。 預設為大約符合。對於 ID 和代碼,請輸入 FALSE。
  • 使用相對表格範圍。 如果沒有 $ 符號,範圍會隨著您向下填滿而移動。
  • 從工作表開始計算欄數。 請從範圍的第一欄開始計算,而不是從 A 欄開始。
  • 查閱欄位於右側。 VLOOKUP 只能向右搜尋。請移動該欄或使用 XLOOKUP。
  • 隱藏的空格。 帶有結尾空格的 "P-101 " 將無法與 "P-101" 比對。
  • 將數字儲存為文字。 輸入為文字的代碼將無法與儲存為數字的相同代碼比對。
  • 重複的鍵值。 VLOOKUP 僅會傳回第一個符合項,因此重複項會隱藏後續的列。
  • 隱藏所有錯誤。 IFERROR 非常實用,但在共用檔案之前,請先檢查它隱藏了什麼。

當您準備好在不使用公式的情況下比對和分析大型檔案時,可以搭配訂單匯出資料和產品清單來試用 Powerdrill Bloom。

常見問題

如何在 Excel 中執行 VLOOKUP?

輸入 =VLOOKUP(查閱值, 表格範圍, 欄號, FALSE)。例如,=VLOOKUP(B2,$F$2:$H$5,3,FALSE) 會在 F2:H5 的第一欄中尋找 B2,並傳回第三欄。

為什麼我的 VLOOKUP 會傳回 #N/A?

在完全符合的情況下,#N/A 代表找不到該值。請檢查是否有拼字錯誤、多餘的空格以及儲存為文字的數字。

如何從另一個工作表進行 VLOOKUP?

在範圍中包含工作表名稱,例如 =VLOOKUP(A2,'Client Details'!A:F,3,FALSE)。在建立公式時按一下另一個工作表,系統會自動為您加入名稱。

VLOOKUP 中的 FALSE 代表什麼意思?

FALSE 代表要求完全符合。TRUE 或省略該引數則代表要求大約符合,且需要對第一欄進行排序。

我應該使用 VLOOKUP 還是 XLOOKUP?

如果所有人都使用 Excel for Microsoft 365、Excel 2021 或更新版本,請使用 XLOOKUP。它支援任何方向的搜尋,且預設為完全符合。當檔案需要提供給使用 Excel 2016 或 Excel 2019 的使用者時,請使用 VLOOKUP。

資料來源: Microsoft 支援服務,VLOOKUP 函數 · Microsoft 支援服務,XLOOKUP 函數 · Microsoft 支援服務,使用 VLOOKUP、INDEX 或 MATCH 查閱值。