使用XLOOKUP在多個不連續範圍裡查找資料的技巧。
有時候受限於查詢範圍的設計，要查詢的範圍可能不止一處，這時候運用多次的XLOOKUP巢串式公式，也是可以輕鬆解決的喔！例如：以下的實作範例中，我們將輸入員工工號，並以此工號至公布的測驗成績資料範圍中進行查找，以顯示該員工是否通過測驗。其中我們先儲存格C3命名為「查詢工號」，而建立的查詢公式將輸入在儲存格C4裡。此外，由於員工工號有點冗長與複雜，因此，可以透過XLOOKUP的萬用字元查詢特性，即可在僅輸入局部字串的狀態下，也能順利找到資料。常用的萬用字元有「?」與「*」等等。例如：「A*公司」即可尋找「A」開頭、「公司」結尾且之間可包含任意數量字元的內容。在此範例的實作演練中，筆者將更進一步的透過範圍名稱的定義，事先建立一個包含萬用字元的比對準則字串，讓後續的查詢操作顯得更加容易與簡便。例如：先利用Excel定義名稱的功能建立一個比對公式，來架構僅需輸入部分工號就可以進行查詢的準則，例如：點按［公式］索引標籤，然後點按［已定義之名稱］群組裡的［定義名稱］命令按鈕，在開啟的［新名稱］對話方塊裡，輸入名稱為「比對」，其參照的公式為：
="*"&查詢工號&"*"
也就是利用前後兩個"*"萬用字元來串接所輸入的工號。
公布的測驗成績資料範圍共有三欄，由左至右分別是「工號」、「分數」與「狀態」，其中「工號」欄便是我們要查找的查詢陣列(lookup_array)，而「狀態」欄即是所要傳回的查詢陣列(return_array)，顯示著通過與未通過的訊息。接著，我們就可以在儲存格C4裡建立如下的XLOOKUP函數，嘗試一下員工成績的查詢，其中，剛剛所建立的名稱「比對」即是此XLOOKUP函數裡的第1個參數(lookup_value)，採用的是萬用字元的比對，因此，第5個參數必須設定為「2」：
=XLOOKUP(比對,B7:B16,D7:D16,,2)
我們嘗試著輸入工號0193(輸入至已經設定為文字格式的儲存格C3)，立即可顯示出此員工並未通過測驗。再換換另一位員工，例如：改輸入工號為210，則可發覺此員工有通過測驗。但是，再輸入工號220時，竟顯示著「#N/A」，果真沒有此員工嗎？其實不然，那是因為此範例的測驗成績資料範圍並不是只存在於儲存格範圍B7:D16而已，如下圖所示，儲存格範圍G7:I16，以及儲存格範圍L7:N16，也都記載著員工的測驗成績資料。而剛剛建立的XLOOKUP函數僅在於儲存格範圍B7:D16裡進行查找而已，難怪為位於儲存格G11裡的*222*會找不到！
若要解決可以在多個查詢範圍裡查找資料的問題，則可以在XLOOKUP函數裡的第4個參數if_not_found，建立另一個XLOOKUP函數的查找，也就是外層的XLOOKUP函數若找不到資料的話，便進行內層的XLOOKUP函數其另一階段的查找。以此例為例，內層的XLOOKUP函數便可以相同的查找準則，也就是與外層XLOOKUP函數之第1個參數相同(比對)，而第2個參數與第3個參數，則分別設定為第二個查找工號陣列(G7:G16)與第二個傳回狀態結果陣列(I7:I16)，依此類推，再進行第三層的XLOOKUP函數的建立，設定第三個查找工號陣列(L7:L16)與第三個傳回狀態結果陣列(N7:N16)。總共三個XLOOKUP的巢串套用，形成如下的函數公式：
=XLOOKUP(比對,B7:B16,D7:D16,XLOOKUP(比對,G7:G16,I7:I16,XLOOKUP(比對,L7:L16,N7:N16,,2),2),2)
試試看吧！輸入工號「222」、輸入工號「256」，是否都可以順利且正確的查找到測驗是否通過的狀態了！
若查找的結果，也想顯示出其測驗分數，例如：「通過(990分)」或「未通過(570分)」，此時，只要利用&串接字串的算式，雖然公式冗長了些，但未嘗不是簡單的解決方案。
=XLOOKUP(比對,B7:B16,D7:D16&"("&C7:C16&"分)",XLOOKUP(比對,G7:G16,I7:I16&"("&H7:H16&"分)",XLOOKUP(比對,L7:L16,N7:N16&"("&M7:M16&"分)",,2),2),2)
(本文實作範例下載)