如何修正 VLOOKUP 函數中的 #N/A 錯誤

附註:  我們想要以您的語言,用最快的速度為您提供最新的說明內容。 本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。 此為 英文文章 出處,以供參考。

本主題說明VLOOKUP函數,錯誤的結果的常見原因,並提供建議改為使用INDEXMATCH

提示: 此外,請參閱快速參考卡: VLOOKUP 疑難排解秘訣其呈現 #NA 問題,好方便您在 PDF 檔案中的常見原因。您可以與其他人共用的 PDF,或列印出來供自己參考。

問題:查閱值不在 table_array 引數的第一欄中

VLOOKUP 的一個限制是只可以查詢中的表格陣列最左邊的資料行上的值。如果您要的查閱值不陣列的第一欄中,您會看到 # n/A 錯誤。

在下表中,我們想要擷取羽衣甘藍的銷售數量。

VLOOKUP 中的 #NA 錯誤:查閱值不在表格陣列的第一欄中

因為查閱值」 凱爾 」會顯示在table_array引數 A2:C10 第二個欄 (農產品),就會產生 # n/A 錯誤。在此情況下,Excel 會尋找它在欄 A,欄 b 中

解決方案: 您可以嘗試修正此問題來調整您的 VLOOKUP 參照正確的資料行。如果不可行,請嘗試移動您的資料行。也可能高度了無法實行,如果您有一個大或很複雜的試算表儲存格中的值是其他計算結果的位置,或可能還有其他邏輯的原因為何您只要無法移動資料行。解決方法是使用 INDEX 和 MATCH 函數,不論其位置的位置查閱表格中的資料行中的值可查詢的組合。請參閱下一節。

請考慮改用索引/相符項目

INDEXMATCH是很好的 VLOOKUP 不符合您需求的許多情況下的選項。索引/相符的主要優點是,您可以查詢中查閱表格中的任何位置中的資料行的值。從指定表格/範圍 INDEX 會傳回的值,根據其位置。符合的項目會傳回表格/範圍中值的相對位置。同時使用 INDEX 和 MATCH 在公式中指定表格/陣列中的相對位置之值的查閱表格/陣列中的值。

有數種優點,而不 VLOOKUP 使用 INDEX/相符項目:

  • 使用 INDEX 和 MATCH,傳回的值不需要在同一欄與查閱資料行。這是的 VLOOKUP 中, 傳回的值必須是指定的範圍中的不同。如何執行此問題?使用 VLOOKUP,您必須知道包含傳回值的欄號。雖然看起來似乎沒有挑戰,就很麻煩當您有大型的資料表,並計算的欄數。此外,如果您新增/移除資料行資料表中,您就必須重新計算更新col_index_num引數。使用 INDEX 和 MATCH,沒有計數是必要的查閱資料行是不同的傳回值的資料行。

  • 使用 INDEX 和 MATCH,您可以指定列或欄的陣列中,或兩者。這表示您可以查閱值垂直和水平。

  • INDEX 和 MATCH 可以用來查詢中的任何資料行的值。與 VLOOKUP 不同,在其中您可以只查閱表格中的第一欄中的值,如果您查閱的值是在第一欄、 前,或任何位置之間,則將會使用 INDEX 和 MATCH。

  • INDEX 和 MATCH 提供的動態其中包含傳回值的資料行的參考彈性。這表示,您可以新增欄至表格而不會中斷 INDEX 和 MATCH。相反地,如果您要新增欄至表格會中斷 VLOOKUP,因為這可讓表格的靜態參考。

  • INDEX 和 MATCH 提供更多的彈性,相符的項目與。INDEX 和 MATCH 可以尋找完全符合或大或查閱值較小的值。VLOOKUP 會只尋找符合的值 (預設) 或確切的值。VLOOKUP 也假設預設的字母順序排序的表格陣列的第一欄,假設您的表格沒有設定如此一來,VLOOKUP 會傳回第一個符合在資料表中,可能不是您正在尋找的資料。

語法

若要建立語法索引/相符項目,您需要使用陣列或參照引數,INDEX 函數和 MATCH 語法其巢狀結構。這樣的形式:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

若要取代 VLOOKUP 上述範例中,讓我們來使用索引/相符項目。其語法看起來像這樣:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

簡言之,這表示:

= 編製索引 (從 C2:C10 符合的值傳回 (凱爾,也就是 B2:B10 陣列中傳回的值會對應到凱爾的第一個值中的某處))

INDEX 和 MATCH 函數可用來取代 VLOOKUP

公式會在 C2:C10 中尋找對應「羽衣甘藍」的第一個值 (在 B7 中),然後傳回 C7 中的值 (100),這是符合羽衣甘藍的第一個值。

問題:找不到完全相符的項目

range_lookup引數為 FALSE,及 VLOOKUP 是找不到完全符合您的資料,會傳回 # n/A 錯誤。

解決方案: 如果您確定試算表中有相關的資料和 VLOOKUP 未攔截,需要驗證參照的儲存格沒有隱藏的空格或非列印字元的時間。此外,請確定儲存格,請依照正確的資料類型。例如,數字的儲存格應設定格式數字文字

此外,請考慮使用 [清除] 或 [ TRIM函數來清理儲存格中的資料。

問題:查閱值小於陣列中的最小值

如果range_lookup引數設定為 TRUE,且查閱值小於陣列中的最小值,您會看到 # n/A 錯誤。TRUE 看起來大約符合陣列中,傳回最接近值較小查閱值。

在下列範例中,查閱值為 100,但 B2:C10 範圍中沒有小於 100 的值;因此發生錯誤。

當查閱值小於陣列中的最小值時,VLOOKUP 中的 N/A 錯誤

解決方式

  • 請視需要更正查閱值。

  • 如果您無法變更查閱值,且需要更大的彈性的相符的值,請考慮使用 INDEX/相符項目,而不 VLOOKUP,請參閱本文中的前一節。使用索引/相符項目,您可以查閱值大於,較小,或等於查閱值。如需有關使用 INDEX/相符項目,而不 VLOOKUP 的詳細資訊,請參閱本主題中的前一節。

問題:查閱欄未以遞增的順序排序

如果range_lookup引數設定為 TRUE,其中一個查閱欄不是以遞增 (A 至 Z) 順序排序,您會看到 # n/A 錯誤。

解決方式

  • 變更尋找完全符合 VLOOKUP 函數。若要這樣做,請將range_lookup引數為FALSE。未排序,則需要為 FALSE。

  • 使用 INDEX/MATCH 函數查詢未排序表格中的值。

問題:值是大型的浮點數字

如果您有時間值或大型的十進位數字在儲存格,Excel 會傳回 # n/A 錯誤,因為浮動精確度。浮點數會依照小數點後的數字。(Excel 會儲存時間值為浮點數)。Excel 無法儲存很大的浮點數字,因此函數,無法正常運作,浮點數字需要捨入至 5 個小數位數。

解決方式:使用 ROUND 函數將數字四捨五入到五個小數位數,藉此縮短數字。

對特定函數有任何問題嗎?

在 Excel 社群論壇張貼問題

協助我們改進 Excel

您是否有任何關於下一版 Excel 改善方式的建議?如果有的話,請參閱 Excel User Voice 中的主題。

另請參閱

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×