VLOOKUP 函數

如果您需要在表格中尋找專案或依列尋找範圍,請使用 VLOOKUP。 例如,依元件編號查看汽車元件的價格,或根據員工識別碼尋找員工名稱。

提示: 如需使用 VLOOKUP 的相關說明,請參閱這些 Excel 社群專家的 YouTube 影片

以其最簡單的形式而言,VLOOKUP 函數表示:

= VLOOKUP (您想要查詢的專案、您要在其中尋找資料的範圍、包含要傳回值的範圍內的欄號),會傳回近似或完全相符-表示為 1/TRUE,或 0/FALSE)。

您的瀏覽器不支援視訊。 請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

提示: 使用 VLOOKUP 的訣竅,便是整理您的資料,讓要查閱的值 (Fruit) 位於要尋找的傳回值 (Amount) 左側。

使用 VLOOKUP 函數來查閱表格中的值。

語法

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

例如:

  • = VLOOKUP (A2,A10: C20,2,TRUE)

  • = VLOOKUP("連",B2:E7,2,FALSE)

  • = VLOOKUP (A2,「用戶端詳細資料」!A:F,3,FALSE)

引數名稱

描述

lookup_value    (必填)

您要查閱的值。 您要查閱的值必須位於您在table_array引數中指定之儲存格範圍的第一欄中。

例如,如果資料表陣列跨越儲存格 B2: D7,則您的 lookup_value 必須在 B 欄中。

請見下圖,Lookup_value 可以是值或儲存格參照。

table_array    (必填)

VLOOKUP 會針對 lookup_value 搜尋及傳回值的儲存格範圍。 您可以使用命名的範圍或表格,而您可以在引數中使用名稱,而不是儲存格參照。 

儲存格範圍中的第一欄必須包含lookup_value。 儲存格範圍也必須包含您要尋找的傳回值。

了解如何選取工作表中的範圍

col_index_num    (必填)

包含傳回值的欄號(從table_array最左邊的欄開始為1)。

range_lookup    (選填)

這是用以指定要 VLOOKUP 尋找大約符合或完全符合值的邏輯值:

  • 大致相符-1/真假設表格中的第一欄是以數值或字母順序排序,然後搜尋最接近的值。 如果您沒有指定方法,則預設為 TRUE。 例如,= VLOOKUP (90,A1: B100,2,TRUE)。

  • 完全符合-0/FALSE會在第一欄中搜尋確切值。 例如,= VLOOKUP ("Smith",A1: B100,2,FALSE)。

如何開始

建置 VLOOKUP 語法需要四項資訊:

  1. 您想要查閱的值,也稱為「查閱值」。

  2. 查閱值所在的範圍。 請記住,查閱值必須一律位於範圍的第一欄內,VLOOKUP 才能正確運作。 例如,如果您的查閱值是位於儲存格 C2 中,您的範圍便應該從 C 開始。

  3. 範圍中包含傳回值的欄號。 例如,如果您指定 B2: D11 作為範圍,您應該將 B 計算為第一欄,以 C 為第二欄,依此類推。

  4. 此外,如果您想要取得大約符合回傳值,可以指定 TRUE,如果想要取得完全符合回傳值,可以指定 FALSE。 若您不指定,預設值一律為 TRUE 或稱為大約符合值。

現在將上述內容組合在一起:

= VLOOKUP (查閱值,包含查閱值的範圍,範圍中包含傳回值的欄號,大約符合(TRUE)或完全符合(FALSE))。

範例

以下是 VLOOKUP 的一些範例:

範例 1

VLOOKUP 範例 1

範例 2

VLOOKUP 範例 2

範例 3

VLOOKUP 範例 3

範例 4

VLOOKUP 範例 4

範例 5

VLOOKUP 範例 5

您可以使用 VLOOKUP 將多個資料表合併成一個,只要其中一個資料表有與其他所有欄位相同的欄位。 如果您需要與不支援多個資料表做為資料來源之資料功能的舊版 Excel 的人員共用活頁簿,這會特別有用,只要將來源合併成一個表格,並將資料功能的資料來源變更為新的表格中,資料功能可用於舊版 Excel (前提是較舊的版本支援資料功能本身)。

資料工作表中的欄使用 VLOOKUP 來取得其他表格中的資料

在這裡,欄 A-F 和 H 的值或公式只會在工作表上使用值,其餘的欄則是使用 VLOOKUP 以及欄 A (用戶端程式代碼)和 B 欄(律師)的值來從其他資料表取得資料。

  1. 將包含通用欄位的資料表複製到新的工作表上,並為其命名。

  2. 按一下 [資料>資料工具] >關聯],開啟 [管理關聯] 對話方塊。

    [管理關聯] 對話方塊
  3. 針對每個列出的關聯,請注意下列事項:

    • 連結資料表的欄位(列在對話方塊中的括弧內)。 這是 VLOOKUP 公式的lookup_value

    • 相關的查閱表格名稱。 這是 VLOOKUP 公式中的table_array

    • 在新資料行中有所需資料的相關查閱表格中的欄位(欄)。 [管理關聯] 對話方塊中不會顯示這項資訊,您必須查看相關的查閱表格,才能查看您要檢索的欄位。 您想要記下欄號(A = 1),這是公式中的col_index_num

  4. 若要將欄位新增至新資料表,請使用您在步驟3中收集的資訊,在第一個空白欄中輸入 VLOOKUP 公式。

    在我們的範例中,欄 G 使用律師( lookup_value)從律師工作表表格(col_index_num = 4)中取得帳單比率資料table_array,公式= VLOOKUP ([@Attorney],tbl_Attorneys,4,FALSE)

    公式也可以使用儲存格參照和範圍參照。 在我們的範例中,它是= VLOOKUP (A2,"律師費"!A:D,4,FALSE)。

  5. 繼續新增欄位,直到您擁有所有所需欄位為止。 如果您嘗試準備包含使用多個資料表之資料功能的活頁簿,請將 data 功能的資料來源變更為新資料表。

問題

錯在哪裡

傳回錯誤值

如果 range_lookup 為 TRUE 或省略,則第一欄必須依字母順序或數字順序排列。 如果第一欄沒有排列,傳回值可能會出錯。 您可以排序第一欄,或以 FALSE 找尋完全符合的值。

儲存格中出現 #N/A

  • 如果 range_lookup 為 TRUE,則如果 lookup_value 的值小於 table_array 第一欄的最小值,您會接獲 #N/A 錯誤值。

  • 如果 range_lookup 是 FALSE,則 #N/A 錯誤值代表找不到完全符合的值。

如需解決 VLOOKUP 中 #N/A 錯誤的詳細資訊,請參閱如何修正 VLOOKUP 函數中的 #N/A 錯誤

#REF! (在儲存格中)

如果 col_index_num 大於 table-array 中的欄數,您會接獲 #REF! 的錯誤值。

如需解決 VLOOKUP 中 #REF! 錯誤的詳細資訊,請參閱如何修正 #REF! 錯誤.

#VALUE! (在儲存格中)

如果 table_array 小於 1,您會接獲 #VALUE! 錯誤值。

如需解決 VLOOKUP 中 #VALUE! 錯誤的詳細資訊,請參閱如何修正 VLOOKUP 函數中的 #VALUE! 錯誤.

#NAME? (在儲存格中)

#NAME? 錯誤值通常表示公式漏掉雙引號。 若要查詢人名時,請務必在公式中的人名前後加上雙引號。 例如,在 =VLOOKUP("連",B2:E7,2,FALSE) 中,請將名字寫成 "連"

如需詳細資訊,請參閱如何修正 #NAME? 錯誤.

Excel 中的 #SPILL! (在儲存格中)

這個特殊#SPILL!錯誤 通常表示您的公式依賴于查閱值的隱含交集,且使用整個資料行作為參照。 例如,= VLOOKUP (A:A,A:C,2,FALSE)。 您可以用 @ 運算子(如下所示)錨定查閱參考,以解決問題: = VLOOKUP (@A: A、A:C、2、FALSE)。 或者,您也可以使用傳統的 VLOOKUP 方法,而不是整個資料行: = VLOOKUP (A2,A:C,2,FALSE)。

請執行此動作

理由

range_lookup 使用絕對參照

使用絕對參照可讓您填滿公式,使它永遠查閱完全相同的範圍。

進一步了解如何使用絕對儲存格參照

不要將數字或日期儲存為文字。

搜尋數字或日期值時,請確定 table_array 第一欄中的資料並未儲存成文字值。 否則,VLOOKUP 可能會傳回不正確或非預期的值。

將第一欄排序

如果 range_lookup 是 TRUE,請在使用 VLOOKUP 之前,先將 table_array 的第一欄排序。

使用萬用字元

如果 range_lookup 是 FALSE,而且 look_up value 是文字,則您可在 look_up value 中使用萬用字元 (問號 (?) 和星號 (*))。 問號可比對任何一個字元。 星號可比對任何一串字元。 如果您要尋找實際的問號或星號,請在該字元前面輸入波狀符號 (~)。

例如,= VLOOKUP ("Fontan?",B2: E7,2,FALSE)會搜尋所有Fontana的所有實例,其最後一個字母可能會有所不同。

請確定您的資料沒有包含錯誤的字元。

在第一欄中搜尋文字值時,請確定第一欄中的資料不包含前置空格、結尾空格、不成對的直引號 ( ' 或 " ) 及彎引號 ( ‘ 或 “ ),以及非列印字元。 否則 VLOOKUP 可能會傳回不在預期之內的值。

若要得出正確的結果,可嘗試使用 CLEAN 函數TRIM 函數,移除儲存格中表格值後面的結尾空格。

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

快速參考卡:VLOOKUP 複習
快速參考卡:VLOOKUP 疑難排解提示
YouTube:Excel 社群專家的 VLOOKUP 影片
有關 VLOOKUP 您需要知道的事項
如何更正 VLOOKUP 函數中的 #VALUE! 錯誤
如何更正 VLOOKUP 函數中的 #N/A 錯誤
Excel 公式概觀
如何避免公式出錯
偵測公式中的錯誤
Excel 函數 (按字母順序排序)
Excel 函數 (依類別)
VLOOKUP (免費預覽)

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×