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

本主題描述函數的錯誤結果最常見的VLOOKUP原因,並提供使用索引相符的建議。

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

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

VLOOKUP 的一個限制是它只能在表格陣列中的最左邊的欄上尋找值。 如果您的查閱值不在陣列的第一欄中,您會看到 #N/A 錯誤。

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

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

Table_array引數 A2: C10 的第二欄(農產品)中出現 [羽衣甘藍] ,則會產生 #N/a 錯誤。 在這種情況下,Excel 會在欄 A (而非欄 B)中尋找它。

解決方式:您可以將 VLOOKUP 調整為參照正確的欄,以嘗試修正此問題。 如果無法這樣做,請嘗試移動欄。 這也可能是高度 impracticable,如果您有大型或複雜的試算表,其中的儲存格值是其他計算的結果,或者可能是您無法移動欄的其他邏輯原因。 解決方法是使用 INDEX 和 MATCH 函數組合,如此一來,不論值位於查閱表格中的哪個位置,都能夠查詢欄中的值。 請參閱下一節。

請考慮改為使用索引/相符

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

使用 INDEX/MATCH 而不是 VLOOKUP 有數個好處:

  • 使用 INDEX 和 MATCH 時,傳回值不需要與查閱欄位於同一欄中。 這與 VLOOKUP 不同,即傳回值必須位於指定範圍內。 這有多重要? 若使用 VLOOKUP,您必須知道包含傳回值的欄號。 雖然這看起來可能不具挑戰性,但是當您有大型表格且必須計算欄數時,可能會很麻煩。 此外,如果您在資料表中新增/移除資料行,則必須重新列舉並更新col_index_num引數。 若使用 INDEX 和 MATCH,則無須計算,因為查閱欄與有傳回值的欄不同。

  • 使用 INDEX 和 MATCH,您可以在陣列中指定一列或一欄,或指定兩個數據行。 也就是說,您可以透過垂直和水平的方式查詢值。

  • INDEX 和 MATCH 可用來查詢任何欄中的值。 與 VLOOKUP 不同的是,您只能在表格的第一欄中尋找值,如果查閱值是在第一欄、最後一欄或其間的任何位置,就能使用 [索引] 與 [相符]。

  • [索引與相符] 提供對包含傳回值的資料行進行動態參照的彈性。這表示您可以在表格中新增欄,而不會中斷索引及相符。 另一方面,如果您需要將資料行新增至資料表,則 VLOOKUP 會中斷,因為它會建立對資料表的靜態參照。

  • [索引與相符] 可讓您更靈活地搭配相符。INDEX 和 MATCH 可以找到完全符合的值,或是大於或小於查閱值的值。 VLOOKUP 只會尋找與值最接近的相符項目 (根據預設) 或完全相符的項目。 根據預設,VLOOKUP 也假設表格陣列中的第一欄是依字母順序排序,而且假設您的表格不是依照這種方式設定,VLOOKUP 將傳回表格中第一個最接近的相符項目,這可能不是您要尋找的資料。

語法

若要建立索引/相符的語法,您需要使用 INDEX 函數的 array/reference 引數,並將 MATCH 語法嵌套在其中。 這會採用下列形式:

=INDEX(陣列或參照、MATCH(lookup_value,lookup_array,[match_type])

讓我們使用索引/相符來取代上述範例中的 VLOOKUP。 語法看起來會像這樣:

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

簡言之,這表示:

= INDEX (傳回 C2: C10 中的值,該值將會相符(羽衣甘藍,也就是 B2: B10 陣列中的某個位置,其中的傳回值是對應至羽衣甘藍的第一個值))。

INDEX 和 MATCH 函數可用來取代 VLOOKUP

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

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

range_lookup引數為 FALSE 時,且 VLOOKUP 無法在您的資料中找到完全符合的專案,就會傳回 #N/a 錯誤。

解決方案:如果您確信試算表中存在相關資料,且 VLOOKUP 並未將其捕獲,請花時間驗證參照的儲存格沒有隱藏空格或非列印字元。 此外,請確定儲存格遵循正確的資料類型。 例如,含有數位的儲存格應該格式化為數位,而不是文字

此外,您也可以考慮使用 [清除] 或 [修剪] 函式來清除儲存格中的資料。

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

如果range_lookup引數設定為 TRUE,且查閱值小於陣列中的最小值,您就會看到 #N/a 錯誤。 TRUE 會尋找陣列中的概略相符項目,然後傳回小於查閱值之最接近的值。

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

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

解決方式

  • 請視需要更正查閱值。

  • 如果您無法變更查閱值,且需要具有相符的值的更大彈性,請考慮使用 INDEX/MATCH (而不是 VLOOKUP),請參閱本文上方的章節。 若使用 INDEX/MATCH,就能查詢大於、小於或等於查閱值的值。 如需有關使用 INDEX/MATCH (而非 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 支援專員連絡以深入了解您的意見。

×