Excel 解鎖 - 資料查找概念1

進入正題以前，想像以下兩個生活的情境:

手上拿者老婆交代要買的幾項物品進入大賣場，心中想著要怎麼找到這些物品的位置?考試當天看著准考證上的號碼進入考場找尋我在那間教室?

這些平常在發生的例子其實就是我們本篇要說明的資料查找功能，在Excel裡，因為不斷推陳出新，出現了許多強大的資料查找公式和公式連招，可以根據需求來查找資料。

1. VLOOKUP
2. HLOOKUP
3. XLOOKUP
4. MATCH + INDEX
5. XMATCH + INDEX

看完公式裡的引數(Argument)，你一定覺得霧煞煞，讓我們先來對焦一些名詞到底是什麼意思?

1. lookup_value: 這其實就是老婆要你買的物品、你的准考證號碼，也就是你要查閱的值。
2. lookup_array / table_array : 大賣場的產品的位置資訊、考場教室資訊，也就是你要搜尋的陣列或範圍。
3. return_array/ col_index_num : 你想要知道的資訊，比如說你大概知道物品在第幾樓，但忘記是在第幾個通道，或是考場教室是在學校哪一區。
4. match_mode / match_type : 搜索的精準度、配對方式。舉例來說，老婆想要買的物品型號是AA-02，但目前買場沒有這建商品，但有AA-01或AA-03，老婆可以接受嗎? 老婆想要買的是”富士”蘋果，但賣場只有”加州”蘋果，可以接受嗎? 以另一個例子為例，准考證是10065，如果考場沒有這個號碼，我總不能去坐在10066或10064考生的位置吧? 這時候搜索的精準度就很重要。
5. search_mode: 搜尋模式，簡單來說就是你希望Excel怎麼幫你搜尋資料? 要從頭搜尋?還是從尾巴開始找?

現在，讓我們把上述的生活範例轉化到Excel裡，我們先以VLOOKUP& XLOOKUP講解，下圖是大賣場產品的位置資訊(左表)和老婆交代要買的幾項物品(右表)

問題1: 如果要買到一模一樣的東西，要去哪個通道買?

• VLOOKUP 搜尋器會拿F5 (富士蘋果 aka lookup_value) 去 $B$5:$D$9 (賣廠產品位置資訊 aka table_array)的第一欄搜尋，由於賣場沒有賣富士蘋果，因此會回傳錯誤。但賣場有賣豬肉，因此會回傳$B$5:$D$9 (賣廠產品位置資訊)裡的第三個欄位(3 aka col_index_num)裡對應到的值(23)。

• XLOOKUP 搜尋器會拿F5 (富士蘋果 aka lookup_value) 去 $B$5:$B$9 (賣場產品資訊 aka lookup_array)搜尋，由於賣場沒有賣富士蘋果，因此會回傳錯誤。但賣場有賣豬肉，因此會回傳指定欄位$D$5:$D$9 (賣廠位置資訊 aka return_array)裡對應到的值(23)。

• 這裡可以感受到VLOOKUP和XLOOKUP的第一個不同，VLOOKUP是拿lookup_value去table_array的第一欄搜尋，並傳回指定的欄位，且只能傳回第一欄右側的欄位(1-3欄，賣場產品/樓層/通道 )；而XLOOKUP是拿lookup_value去lookup_array搜尋並傳回指定的return_array，指定欄位不一定要落在lookup_array的左側或右側，因此限制較少，也不用屬目標回傳欄位在table_array的第幾欄。

問題2: 如果只是要買到類似的東西，要去哪個通道買?

• VLOOKUP 搜尋器會拿F5 (富士蘋果 aka lookup_value) 去 $B$5:$D$9 (賣廠產品位置資訊 aka table_array)的第一欄搜尋，這次我們在match_type設定為1，搜尋最接近值，這次搜尋器會在$B$5:$D$9 (賣廠產品位置資訊 aka table_array)的第一欄找到加州蘋果，並回傳$B$5:$D$9 (賣廠產品位置資訊)裡的第三個欄位(3 aka col_index_num)裡對應到的值(21)。

• XLOOKUP 搜尋器會拿F5 (富士蘋果 aka lookup_value) 去 $B$5:$B$9 (賣場產品資訊 aka lookup_array)搜尋，這次我們在match_mode 設定為-1，傳回下一個較小的值，這次搜尋器會在$B$5:$B$9 裡找到加州蘋果，並會回傳指定欄位$D$5:$D$9 (賣廠位置資訊 aka return_array)裡對應到的值(21)。

問題3: 必較靠前的通道人可能比較多，我想從靠後的通道搜索豬肉位置怎麼找?

• 由於VLOOKUP 搜尋器不像XLOOKUP 有search_mode，可以指定搜尋的方式，我們在XLOOKUP的search_mode 設定為-1，這樣搜尋器就會從資料尾端開始搜尋並回傳指定欄位$D$5:$D$9 (賣廠位置資訊 aka return_array)裡第一個對應到的值(25)。

看完這三個問題，希望您對於Excel的資料查找功能有個初步的了解，我們會繼續探討MATCH和XMATCH、搜尋的細節，以及一些新的案例，希望能跟您在資料分析的世界持續交流。