不少朋友問:明明表格中有我要查找的數據,可用Excel Vlookup函數總是出錯,為什麼?
讓我們先來回憶一下Vlookup函數的使用方法。
雖說我們看到的錯誤返回值常見的只有“#N/A”、“#REF!”和錯誤的結果。不過實際上有多種原因會造成這些錯誤。
錯誤代碼解釋:
#N/A:找不到要查找的內容。
#REF!:引用了無效的單元格。
單元格引用出錯
這大概是新手最容易犯的一個錯誤了。
新手們經常會將Vlookup函數的參數都設置成相對引用,然後看到第一個Vlookup公式正確了,就直接下拉填充,結果後面的公式的查找範圍分別下移了一行,導致查找到了錯誤的數據或“#N/A”。
所以,通常Vlookup函數的第二個參數,也就是查找的範圍我們建議使用絕對引用。
返回值引用超出了數據範圍
這個錯誤通常發生在表格中有很多列的數據,或要查找的數據範圍不在第一列時。
第二個參數查找範圍的第一列就是Vlookup使用時可以返回的第一列。也就是說,查找範圍是“$A$2:$C$18”時A列是第一列,查找範圍是“$B$2:$C$18”時B列是第一列。
Vlookup第一個參數不是查找範圍的第一列
這也是新手們不瞭解的一個知識點。
Vlookup函數第一個參數必須是在第二個參數指向的區域的第一列。如果不是第一列,可以通過調整第二個參數的範圍,或剪切單元格的方法來實現。當然也可以使用lookup等其他函數,或使用Vlookup逆序查找公式:“=VLOOKUP(E14,IF({1,0},$B$2:$B$18,$A$2:$A$18),2,0)”。
匹配設置出錯
Vlookup函數的最後一個參數是0(精確匹配)或1(模糊匹配),省略時表示模糊匹配。不建議省略,即使要省略,也建議在第三個參數後添加一個分號。
空格導致出錯
被查找的內容前後有空格,這時可以使用trim函數處理被查找的數據。
注意公式“=VLOOKUP(E14,TRIM($A$2:$C$18),3,0)”輸入完畢,必須同時按“Ctrl + Shift + Enter”鍵輸入,否則將得到“#VALUE!”。
另外,我更推薦大家修改原始的數據,將這些不必要的空格去掉。
數據格式不匹配導致錯誤
這個常見於數字與文本型數字之間。
當你要查找的是數字,而被查找的區域中顯示的是文本型數字;或者相反的情況時,即使你的Vlookup函數沒有錯誤,你仍然會得到錯誤的結果。
這種時候,應該修改單元格的格式。
通配符衝突導致的錯誤
當Vlookup函數的第一個參數包含“*、?、~”等通配符時,Vlookup函數就會出錯。這時需要使用SUBSTITUTE函數進行處理,將這些符號替換為“~*”、“~?”以及“~~”。
總結
上面說了那麼多,其實就一條:Vlookup出錯後,我們應該逐一排查它的4個參數,看看是哪個參數出了錯。
真正的錯誤
排除了這些Vlookup函數語法上的錯誤後,如果表格中確實沒有我們要查找的數據,就會得到“#N/A”。
相關閱讀:《Excel下拉菜單錯誤彙總:為什麼你的下拉菜單總出錯?》。
學習,為了更好的生活。歡迎點贊、評論、關注和點擊頭像。