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

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

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

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

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

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

#N/A 錯誤的結果,因為尋找值 "Kale" 會顯示在 (產生) 引數 A2:C10 table_array欄。 在此案例中,Excel欄 A 中尋找,而不是在欄 B 中尋找。

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

請考慮改為使用 INDEX/MATCH

INDEXMATCH 是許多 VLOOKUP 不符合您需求的好選項。 INDEX/MATCH 的主要優點是您可以在資料表的任何位置中尋找欄中的值。 INDEX 會根據指定資料表/範圍的位置,從指定資料表/範圍中傳回值。 MATCH 會返回資料表/範圍中值的相對位置。 在公式中一起使用 INDEX 和 MATCH,指定資料表/陣列中值的相對位置,以尋找資料表/陣列中的值。

使用 INDEX/MATCH 而非 VLOOKUP 有幾個優點:

  • 使用 INDEX 和 MATCH 時,return 值不需要與尋找欄在同一欄中。 這和 VLOOKUP 不同,在 VLOOKUP 中,return 值必須位於指定的範圍內。 這有多重要? 若使用 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/reference 引數,並巢中巢中 MATCH 語法。 此為表單:

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

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

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

簡言之,這表示:

=INDEX (會從 C2:C10 中返回一個值,該值會符合 (Kale,這是 B2:B10 陣列中的某處,其中 return 值是對應到 Kale) )

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而#N/A 錯誤。 浮點數是小數點之後的數位。 (Excel時間值儲存為浮點數。) Excel儲存浮動點非常大的數位,因此若要讓函數正確運作,浮點數字必須四捨五入至 5 個小數位數。

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

需要更多協助嗎?

您可以隨時詢問 Excel 技術社群中的專家,或是在 Answers 社群取得支援。

另請參閱

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×