VLOOKUP 函數

若您需要在表格或範圍中按列搜尋資料,可以使用 VLOOKUP 這個查閱與參照函數。例如用員工的電話號碼查詢員工的姓氏,或按姓氏查詢電話號碼 (就像電話簿一樣)。

VLOOKUP 可組織您的資料,讓您查閱的值 (員工的姓氏) 位於您想要尋找之傳回值 (員工的電話號碼) 的左方。

語法

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

例如:

  • =VLOOKUP(105,A2:C7,2,TRUE)

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

引數名稱

描述

lookup_value    (必填)

您要查閱的值。您想要查閱的值必須位於表格陣列中所指定之儲存格範圍的第一欄。

例如,如果表格陣列橫跨 B2:D7,那麼您的 lookup_value 必須位於欄 B。請見下圖,Lookup_value 可以是值或儲存格參照。

table_array    (必填)

VLOOKUP 會針對 lookup_value 搜尋及傳回值的儲存格範圍。

儲存格範圍中的第一欄必須包含 lookup_value (例如下圖中的 [姓氏]。) 儲存格範圍也必須包含要尋找的傳回值 (例如下圖中的 [名字])。

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

col_index_num    (必填)

包含傳回值的欄號 (從「表格陣列」最左方的欄開始為 1)。

range_lookup    (選填)

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

  • TRUE 假設表格第一欄依數字順序或字母順序排列,然後搜尋最接近值。如果您沒有指定方法,則預設為 TRUE。

  • FALSE 會在第一欄搜尋精確值。

下圖將為您示範如何用 =VLOOKUP("Akers",B2:D5,2,FALSE) 設定讓工作表傳回

在 Excel 中建立 VLOOKUP 公式時所需建立的值與陣列範例

範例

若要在 Excel 中使用這些範例,請複製下表中的資料,將其貼到新工作表的儲存格 A1 中。

識別碼

姓氏

名字

標題

出生日期

101

莉華

業務代表

1968/12/8

102

百勝

銷售 副總

1952/2/19

103

祖君

業務代表

1963/8/30

104

立民

業務代表

1958/9/19

105

棟材

業務經理

1955/3/4

106

文杉

業務代表

1963/7/2

公式

描述

= VLOOKUP ("周",B2:E7,2,FALSE)

table_array B2:E7 的第一欄 (B 欄) 中,尋找這個值,然後傳回在 table_array 的第二欄 (C 欄) 中找到的芳達這個值。 range_lookup FALSE 會傳回完全相符的項目。

=VLOOKUP(102,A2:C7,2,FALSE)

在 A 欄搜尋與 lookup_value102 的姓氏完全符合的項目。結果傳回「巫」。如果 lookup_value105,則會傳回「楊」。

=IF(VLOOKUP(103,A1:E7,2,FALSE)="王","找到了","找不到")

檢查識別碼為 103 的員工,其姓氏是不是。 由於 103 實際上是,所以結果是找不到。 如果您把公式中的「王」改成「廖」,則結果就是找到

=INT(YEARFRAC(DATE(2014,6,30), VLOOKUP(105,A2:E7,5, FALSE), 1))

針對 2014 會計年度,尋找識別碼為 105 的員工年齡。使用 YEARFRAC 函數,以會計年度結束日期減去出生日期,並使用 INT 函數以整數顯示,結果為 59

=IF(ISNA(VLOOKUP(105,A2:E7,2,FALSE)) = TRUE, "找不到員工", VLOOKUP(105,A2:E7,2,FALSE))

如果找到識別碼為 105 的員工,則會顯示該名員工的姓氏一原。否則就會顯示找不到員工訊息。當 VLOOKUP 函數傳回錯誤值 #N/A 時,ISNA 函數 (請參閱 IS 函數) 會傳回 TRUE 值。

=VLOOKUP(104,A2:E7,3,FALSE) & " " & VLOOKUP(104,A2:E7,2,FALSE) & "的職稱是" & VLOOKUP(104,A2:E7,4,FALSE)

如果要找識別碼為 104 的員工,則會將三個儲存格的值連接 (組合) 成一個完整的句子于誠雄的職稱是業務代表。

常見問題

問題

錯在哪裡

傳回錯誤值

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

儲存格中出現 #N/A

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

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

深入瞭解工作表中的錯誤,例如 #N/A、#REF 等

儲存格中出現 #REF!

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

儲存格中出現 #VALUE!

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

儲存格中出現 #NAME?

#NAME? 錯誤值通常表示公式漏掉雙引號。 若要查詢人名時,請務必在公式中的人名前後加上雙引號。 例如,在 =VLOOKUP("周",B2:E7,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 Starter, Excel for Mac 2011, Excel 2016 for Mac, Excel 2010, Excel 2013, Excel 2016 Preview, Excel 2007, Excel Online



這項資訊有幫助嗎?

我們應該如何改進?

255 剩餘字元數

若要保護您的隱私,請不要在意見反應中包含連絡資訊。 檢閱我們的 隱私權原則

感謝您的意見反應!

支援資源

變更語言