XLOOKUP使用基礎：傳回單一值？還是傳回整個陣列？
一提到查找函數，Excel的愛好者一定會立即聯想到VLOOKUP、HLOOKUP、LOOKUP、MATCH與INDEX等函數，在標準的使用方式下，這些函數都可以順利傳回尋獲的資料值。但是，XLOOKUP可就不一樣了，它的查詢結果可以傳回單一的資料值，也可以傳回整欄、整列、甚至多欄多列的內容。加上Excel 365的動態陣列參照特性，使用者並不需要預先估量並選取結果範圍的可能大小位置，僅需將公式輸入在單一儲存格裡，若結果是多欄、多列，便會自動擴增結果輸出範圍。我就們以下列的範例資料為例，在一份禮盒基本資料表中，記錄了每一種禮盒的編號、禮盒名稱、重量、單價、庫存、贈品等資訊，我們想建立一個查詢介面，能夠輸入指定的禮盒編號(例如：輸入在儲存格J6)後，在此資料表中進行查找與該禮盒相關的資訊。譬如想要傳回該禮盒的名稱至儲存格I8，因此，我們可以在儲存格I8建立以下的XLOOKUP函數：
=XLOOKUP(J6,$B$3:$B$20,$C$3:$C$20)
函數裡的各參數設定及說明如下：
- lookup – 要查照的值。此例為J6。
- lookup_array – 要在哪一個資料範圍或陣列裡進行查找。此例為禮盒編號欄位內容$B$3:$B$20。
- return_array – 要傳回的資料範圍或陣列。此例為禮盒名稱欄位內容$C$3:$C$20。
也就是將查詢值J6與查詢陣列$B$3:$B$20(禮盒編號)進行比對，查找到完全符合和的禮盒編號後，再傳回指定的傳回陣列$C$3:$C$20(禮盒名稱)裡相對應的內容，即所查詢的禮盒編號之禮盒名稱。
如果想要傳回的是禮盒的單價，則剛剛的XLOOKUP函數僅需修改第3個參數return_array，從禮盒名稱欄位內容的所在處$C$3:$C$20，改成禮盒單價欄位內容的所在位置$E$3:$E$20即可。
=XLOOKUP(J6,$B$3:$B$20,$E$3:$E$20)
所以，當XLOOKUP函數裡的第3個參數return_array，是一個單欄或單列的陣列時，若找到所要查詢的資料，將會傳回return_array陣列裡的某一個值，也就是傳回單一值。換個角度，如果想要在輸入禮盒編號後，可以傳回該禮盒編號的整筆資料記錄呢？那麼，就要在XLOOKUP函數裡的第3個參數return_array動動手腳了，也就此參數不再是一個單欄或單列的陣列，而是多欄多列的陣列範圍。例如以下的XLOOKUP函數：
=XLOOKUP(J6,$B$3:$B$20,$B$3:$G$20)
第3個參數return_array設定的範圍大小是$B$3:$G$20，包含了頂端標題列以外的整個資料表內容，也就是囊括了每一筆資料記錄。
如此，查獲資料後所傳回的內容，便是陣列裡相對應的整列資料了。雖然只是將此XLOOKUP函數輸入在儲存格I13而已，可是，依據動態陣列參照的特性，傳回的結果是一個陣列，便自動擴充延伸結果至相鄰的儲存格。
當然，如果事先能夠設定各資料欄、列的範圍位名稱，甚至轉換成資料表結構，不用實質的儲存格及範圍位址進行參照，那麼XLOOKUP函數的建立肯定更簡潔也更具結構化與閱讀性。
例如：
=XLOOKUP(J6,$B$3:$B$20,$C$3:$C$20)
可寫成：
=XLOOKUP(輸入編號,禮盒編號,禮盒名稱)
=XLOOKUP(J6,$B$3:$B$20,$E$3:$E$20)
可寫成：
=XLOOKUP(輸入編號,禮盒編號,單價)
=XLOOKUP(J6,$B$3:$B$20,$B$3:$G$20)
可寫成：
=XLOOKUP(輸入編號,禮盒編號,禮盒基本資料)
是不是更棒呢？
(本文實作範例下載)