VLOOKUP的另一種解法
上一範例[大小寫必須相符的查詢比對]的解法,有一個盲點,那就是:若輸入查詢資料的儲存格H2不是下拉式選項,只是一般的空白儲存格,可以天馬行空地任意輸入查詢值呢?那就有可能會輸入不存在於既有品號的資料喔!那麼,是不是會顯示找不到資料呢?試試吧!
首先將儲存格H2改成非下拉式選單,輸入一個並不存在的品號,例如:「ax-888」:
同樣的查詢函數,奇怪,明明品號欄位裡並不存在「ax-888」,怎麼沒有說找不到「#N/A」,而是顯示「717」的結果呢?其實,看懂了前一篇範例的原理後也不意外,因為,此次沒有比對成功的狀態下,=--EXACT(H2,品號) 所產生的陣列是一個全部都是「0」(False)的陣列。陣列的最大值當然也就是「0」囉!
而上述的陣列與庫存範圍的組合(使用IF函數),作為VLOOKUP查詢函數的對照表陣列,也就是第2個參數,那麼將前述的MAX結果「0」視為VLOOKUP的第1個參數進行查詢比對,當然就傳回庫存的第1個結果「0」囉!
也就是說,只要儲存格H2輸入的是一個並不存在的值(並非品號欄位裡的一份子),就代表著IF函數的比較結果,其陣列內容都會是「0」,所以,陣列的最大值也會是「0」,再以此值進行VLOOKUP查詢時,傳回的答案便是第一個「0」的庫存,即「717」,這當然不是我們所想要的結果。因為,在數列中若真的沒有比對成功的資料,應該要顯示找不到(#N/A)的訊息才是。我們就來改寫這個陣列函數吧!
此次,我們仍是將H2的值與品號欄位裡的每一個值,進行大小寫必須相符的比對,同樣是陣列的概念,結果不是True就是False,但是,我們利用IF函數來設定當比對的結果為True時傳回相對的庫存欄位值之所在列號,否則才傳回False。寫法是:
=IF(EXACT(H2,品號), ROW(庫存))
例如:若要查詢的是「ax-203」,以陣列公式的概念,將傳回:
{False, False, False, False, False, False, 9 , False, False, False, False, False,}
因此,若對此結果進行MAX函數運算,即傳回此陣列裡的最大值,即「9」,意味著,所尋獲的品號「ax-203」正位於品號欄位的第7個位置,也就是實際工作表列號的第「9」列。
接著,若將工作表第3~14列的列號與庫存欄位裡的14個庫存值進行組合,如下圖所示:
那麼,將剛剛陣列函數比對後的結果「9」與上述的組合範圍進行VLOOKUP查詢,不就可以取得庫存欄位裡列號為「9」的庫存量了「195」了嗎!
所以,在儲存格H2輸入其他品號,不論是大寫還是小寫,都可以順利的找到庫存資料。例如:「bc-234」的庫存量為「488」;「BC-234」的庫存量則為「617」:
若是輸入了並不存在的品號,例如:「ax-888」,就顯示找不到「#N/A」的錯誤訊息囉!
當然,也不必太執著於一定要用VLOOKUP來解決資料查找的問題,多多活用MATCH、INDEX等函數,也是不錯的選擇喔!