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

本主題說明 VLOOKUP 失敗的最常見可能原因。

提示: 請參閱快速參考卡:VLOOKUP 疑難排解提示,這份便利的 PDF (可攜式文件格式) 檔案說明了 VLOOKUP 之 #NA 問題的常見原因。您可以與其他人共用 PDF,或列印此檔案以備日後參考。

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

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

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

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

此錯誤是因為查閱值「羽衣甘藍」是在 table_array 引數 A2:C10 的第二欄 (農作物) 中,因此 Excel 在欄 A (而非欄 B) 中尋找它。

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

使用 INDEX/MATCH (而非 VLOOKUP)

當 VLOOKUP 不符合您的需求時,可以使用 INDEX/MATCH。INDEX/MATCH 的最大優點是可以在查閱表格的任何位置查詢欄中的值。INDEX 會根據其位置從指定的表格/範圍傳回值,而 MATCH 則會傳回值在表格/範圍中的相對位置。在公式中結合使用 INDEX 和 MATCH,可以指定值在表格/陣列中的相對位置,藉此在表格/陣列中查詢值。

使用 INDEX/MATCH 有一些勝過使用 VLOOKUP 的優勢:

  • 若使用 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 函數使用陣列/參照引數,然後在它之內使用巢狀 MATCH 語法。因此,它看起來像這樣:

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

讓我們用 INDEX/MATCH 取代上述範例中的 VLOOKUP。語法看起來會像這樣:

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

簡言之,這表示:

=INDEX(我想要來自 C2:C10 的傳回值,它將 MATCH(羽衣甘藍,這是 B2:B10 陣列中的某個位置,其中傳回值是對應羽衣甘藍的第一個值))

INDEX 和 MATCH 函數可用來取代 VLOOKUP

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

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

當 range_lookup 引數為 FALSE,而 VLOOKUP 無法在您的資料中找到完全相符的項目,則會傳回 #N/A 錯誤。

解決方式:如果您確定您的試算表中有相關資料存在,而 VLOOKUP 無法找到資料,請確認參照的儲存格沒有隱藏的空格或非列印字元。也請確認儲存格遵循正確的資料類型。例如,您應將含有數字的儲存格的格式設定為 [數字],而非 [文字]。

使用 CLEANTRIM 函數來清除儲存格中的資料。

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

如果 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中的主題。

請參閱

修正 #N/A 錯誤

VLOOKUP:不再出現 #NA

在 Excel 中 HLOOKUP、VLOOKUP、LOOKUP 傳回不正確的值

在 Excel 中浮點算數可能會呈現不正確的結果

快速參考卡:VLOOKUP 進修課程

VLOOKUP 函數

Excel 公式概觀

如何避免公式出錯

使用錯誤檢查偵測公式中的錯誤

所有 Excel 函數 (依字母順序)

所有 Excel 函數 (依類別)

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×