如何使用Excel的XLOOKUP函數尋找資料

Posted on By Dr. Fish
資料管理

微軟Excel裡的VLOOKUP是個強大的資料搜尋函數，不過存在一些限制，例如無法執行從右向左的搜尋、預設值為約略吻合數值的搜尋。為了排除這些限制，微軟新增了一個XLOOKUP函數，可以執行更多樣化的資料搜尋，是個遠比VLOOKUP函數更強大且實用的函數。

但是XLOOKUP函數只內建在微軟2021年後的版本，之前的版本並不包含這個函數。若您剛好有2021年版的Excel且想運用函數來尋找資料，可參考下面的內容，學習如何使用Excel的XLOOKUP函數尋找資料。若是之前的版本，則可參考如何使用Excel的VLOOKUP函數尋找資料、如何使用Excel的INDEX和MATCH函數向左尋找資料。

下面將先介紹XLOOKUP函數的使用時機和語法，再說明基本的操作方法和稍微進階的資料搜尋方法，並分別舉例示範。

XLOOKUP函數的使用時機和語法

XLOOKUP函數可用來尋找工作表裡的資料，藉由位於某一欄的欲搜尋數值，尋找位在同一列的另一欄的數值，不論是從左向右或從右向左的搜尋都可以。由於VLOOKUP函數無法執行從右向左的搜尋，所以能夠進行向左搜尋的XLOOKUP函數便略勝一籌。

XLOOKUP函數的語法為XLOOKUP(lookup_value, lookup_array, return_arry, [if_not_found], [match_mode], [search_mode])，括弧裡6個引數的意義分別如下：

  • lookup_value：欲搜尋的數值。
  • lookup_array：欲搜尋數值的所在範圍。
  • return_array：欲傳回資料的所在範圍。
  • if_not_found：自訂找不到吻合的數值時，系統傳回的文字。若沒有自訂，當找不到吻合的數值時，系統會傳回#N/A。
  • match_mode：指明資料吻合的類型，有下面4種數字選項。
    • 數字0：完全吻合，此為預設值。
    • 數字1：約略吻合，傳回最接近欲搜尋數值的較大數值。
    • 數字-1：約略吻合，傳回最接近欲搜尋數值的較小數值。
    • 數字2：萬用字元的使用，例如?、*和~。
  • search_mode：指明搜尋的模式，有下面4種數字選項。
    • 數字1：從第1個數值開始搜尋，此為預設值。
    • 數字-1：從最後1個數值開始搜尋。
    • 數字2：執行二元搜尋，須先將欲搜尋數值所在範圍的資料從小至大排序。
    • 數字-2：執行二元搜尋，須先將欲搜尋數值所在範圍的資料從大至小排序。

上面的6個引數裡，前3個為必要的引數，後3個則非必要。也就是說，XLOOKUP函數的基本操作須包含前面3個引數，後面3個引數則視情況而決定是否寫出來。這函數的使用方法並不困難，下面舉例示範這個函數的基本操作和進階操作方法。

運用XLOOKUP函數執行從左向右的搜尋

假設有位SPSS課程訓練的講師有10位學員的資料，包含學員的名字（NAME）、年齡（AGE）、生理性別（SEX）、筆記型電腦品牌（LAPTOP）和使用軟體的版本（SPSS）。若想要查詢學員名字為Peter所使用的軟體版本，可以採取下列的操作方法。

先找2個空白的儲存格，輸入NAME和SPSS，分別代表學員名字和使用軟體的版本。在NAME右邊的儲存格（H2）輸入Peter，然後在SPSS右邊的儲存格輸入=XLOOKUP(H2, A2:A11, E2:E11)。這語法要求XLOOKUP函數從儲存格A2到A11的範圍裡找到名字為Peter的學員，並向右從儲存格E2到E11的範圍裡找到這位學員使用的軟體版本。

right lookup using XLOOKUP function

語法輸入完成後，按下Enter，傳回數值15，這就是名字為Peter的學員所使用的軟體版本。這種從左向右的資料搜尋也可以使用VLOOKUP函數，會得到相同的結果。

在剛輸出結果的下方儲存格裡輸入=VLOOKUP(H2, A2:E11, 5, FALSE)，這語法指出在第1欄找到名字完全吻合Peter的學員，再從第1欄向右數的第5欄找到這位學員使用的軟體版本，最後傳回數值15。這函數的詳細操作方法，請參考如何使用Excel的VLOOKUP函數尋找資料。

right lookup using VLOOKUP function

除了從左向右的搜尋，XLOOKUP函數也可以執行從右向左的搜尋，比之前須合併利用INDEX和MATCH函數的方法還來得簡單，下面示範操作方法。

運用XLOOKUP函數執行從右向左的搜尋

同樣利用上面10位學員的例子，但改執行從右向左的資料搜尋。舉例來說，若想查詢使用筆記型電腦品牌為Apple的學員名字，可以透過下面的操作方法。

先找2個空白的儲存格，輸入LAPTOP和NAME，分別代表電腦品牌和學員名字。在LAPTOP右邊的儲存格（H2）輸入Apple，然後在NAME右邊的儲存格輸入=XLOOKUP(H2, D2:D11, A2:A11)。這語法要求XLOOKUP函數從儲存格D2到D11的範圍裡找到Apple的筆記型電腦品牌，再向左從儲存格A2到A11的範圍裡找到使用這個品牌的學員名字。

left lookup using XLOOKUP function

語法輸入完成後，傳回Steven，這就是使用Apple筆記型電腦的學員名字。若您沒有2021年版本的Excel，這裡可以使用INDEX和MATCH函數進行向左搜尋，會得到相同的結果。

在剛才輸出結果下方的儲存格裡輸入=INDEX(A2:A11, MATCH(H2, D2:D11, 0))，這語法先利用MATCH函數找到筆記型電腦品牌Apple所在的相對位置，也就是從上面數下來這品牌位在第幾列，再利用INDEX函數向左從儲存格A2到A11的範圍裡找到使用這筆記型電腦品牌的學員名字。若您想瞭解詳細的操作方法，請參考如何使用Excel的INDEX和MATCH函數向左尋找資料。

left lookup using INDEX and MATCH functions

除了一次傳回一個數值之外，XLOOKUP函數還可以一次傳回多個數值，若想一次尋找一列或一欄的資料，便相當方便，下面示範操作方法。

運用XLOOKUP函數一次取得數個搜尋結果

XLOOKUP函數能夠一次傳回一列的搜尋結果，若想一次取得多項資訊，是個很實用的工具。同樣以上面10位學員的資料為例，若想查詢名字為Wanda的學員之年齡、生理性別、筆記型電腦品牌和軟體版本等資訊，可以採取下面的操作方法。

先找5個相鄰的空白儲存格，從左至右分別輸入NAME、AGE、SEX、LAPTOP和SPSS，分別代表學員名字、年齡、生理性別、電腦品牌和軟體版本。在NAME下方的儲存格（G3）輸入Wanda，然後在AGE下方的儲存格輸入=XLOOKUP(G3, A2:A11, B2:E11)，這語法指出從儲存格A2到A11的範圍裡尋找名字為Wanda的學員，再從儲存格B2到E11的範圍裡找到這位學員的年齡、生理性別、電腦品牌和軟體版本。

results of multiple values using XLOOKUP function

語法輸入完成後，傳回數值48、女、HP和20。這結果顯示名字為Wanda的學員是一位年齡48歲的生理女性，使用HP的筆記型電腦和第20版的SPSS。這種一次可傳回多個數值的表現稱為「溢出」（spill），而具有這功能的公式則稱為動態陣列公式（dynamic array formula）。

上面3種XLOOKUP函數的使用皆屬於基本的操作，原則上只要在語法裡清楚地寫出前3個引數就可以，但是這函數的功能不僅止於此，下面來示範其他較進階的操作方法。

運用XLOOKUP函數執行雙向搜尋

利用XLOOKUP函數來尋找資料時，必須指出傳回資料所在的儲存格範圍，但不是每一次都能夠快速、清楚地看到傳回資料所在的範圍，尤其是在資料集很大的時候。此時可以運用雙向搜尋的方式，將一個XLOOKUP函數巢套在另一個XLOOKUP函數裡，一個執行「欄」資料的尋找，另一個執行「列」資料的尋找。

以上面10位學員的例子來看，假設想查詢名字為Susan的學員所使用的軟體版本，可以分成2部分來探討。首先，利用上面運用XLOOKUP函數一次取得數個搜尋結果的操作方法，尋找欲傳回資料的所在範圍，也就是欄位名稱為SPSS的一整欄數值。

找一空白的儲存格（G1）輸入SPSS，然後在下方的儲存格輸入=XLOOKUP(G1, A1:E1, A2:E11)。這語法指出從儲存格A1到E1的範圍裡尋找名稱為SPSS的欄，再從儲存格A2到E11的範圍裡傳回這一欄的所有資料。藉由這樣的操作，會傳回與原本SPSS欄一模一樣的數值。

return array using XLOOKUP function

接著，把上面的操作語法帶入至尋找名字為Susan的學員所使用的軟體版本的語法裡。先找2個空白的儲存格，輸入NAME和SPSS，分別代表學員名字和軟體版本。在NAME右邊的儲存格（H2）輸入Susan，然後在SPSS右邊的儲存格輸入下面的語法：

=XLOOKUP(H2, A2:A11, XLOOKUP(G3, A1:E1, A2:E11))

這語法指出從儲存格A1到E1的範圍裡尋找名稱為SPSS的欄且傳回這一欄的所有資料，然後從儲存格A2到A11的範圍裡尋找名字為Susan的學員，並向右從SPSS欄的資料裡找到這位學員所使用的軟體版本。透過這樣的操作，傳回22，也就是Susan使用第22版的SPSS。

two-way lookup using XLOOKUP function

當數據資料很多的時候，確定傳回資料所在的範圍可能變得不容易，此時若利用這裡示範的雙向搜尋來尋找資料，會讓資料尋找變得更有彈性也更簡單。

自訂找不到吻合數值時的文字串

當找不到吻合數值的時候，XLOOKUP函數會傳回#N/A，但這樣的文字不容易讓人理解，所以可利用這個函數的第4個引數來自訂找不到吻合數值時系統傳回的文字串。

同樣以上面10位學員的資料為例，假設要尋找名字為Tom的學員所使用的軟體版本，在下圖中的儲存格H3裡輸入=XLOOKUP(H2, A2:A11, E2:E11)。因為學員裡沒有人是這個名字，找不到吻合的值，所以系統傳回#N/A。

default text of no match using XLOOKUP function

若想把找不到吻合數值時的文字串改成not found（或任何您想要呈現的文字），可以在儲存格H3的語法裡增加第4個引數，變成如下的語法：

=XLOOKUP(H2, A2:A11, E2:E11, “not found”)

因為第4個引數是文字串，所以須將其置於雙引號內。若沒有使用雙引號，會出現#NAME?的錯誤訊息。透過這個語法，當找不到名字為Tom的學員時，系統會傳回not found這個訊息。

argument of no match using XLOOKUP function

這個能夠自訂找不到吻合數值時所傳回的訊息之功能很實用，讓資料搜尋的結果變得更有意義，也更容易理解，您不妨試試！

運用XLOOKUP函數尋找約略吻合的數值

XLOOKUP函數提供4種資料吻合的類型，包括完全吻合、約略吻合和萬用字元。若要改變吻合類型，可在語法的第5個引數match_mode裡指明清楚。預設值為完全吻合的數值搜尋，上面示範的操作都屬於這種類型。由於是預設值，所以不用特地在引數裡寫出來，若想寫出來也可以，數字選項為0。

同樣使用上面10位學員的資料，這裡示範另外3種吻合類型的操作方法。假設要尋找年齡30歲的學員所使用的筆記型電腦品牌，因為資料裡沒有剛好30歲的學員，所以可尋找最接近30歲的年齡，再找出相對應的筆記型電腦品牌。

先找2個空白的儲存格，輸入AGE和LAPTOP，分別代表年齡和筆記型電腦品牌，並在AGE右邊的儲存格（H2）輸入30。這時可以有2種選擇，比30歲年輕或年長。若想尋找最接近30歲的較小年齡，在LAPTOP右邊的儲存格輸入=XLOOKUP(H2, B2:B11, D2:D11, , -1)。由於語法裡省略第4個引數，須用2個逗點表示這個引數的位置，再寫出第5個引數。

這語法指出從儲存格B2到B11的範圍裡尋找最接近但小於30的年齡，然後向右從儲存格D2到D11的範圍裡找到這個約略30歲的學員所使用的筆記型電腦品牌。因為最接近且小於30歲的年齡為29歲，所以這語法會傳回Samsung。

next smaller value using XLOOKUP function

若要尋找最接近30歲的較大年齡，可把上面LAPTOP右邊儲存格的語法改成=XLOOKUP(H2, B2:B11, D2:D11, , 1)。因為最接近且大於30歲的年齡為33歲，所以這語法會傳回Lenovo。

next larger value using XLOOKUP function

最後，若不清楚欲搜尋文字的完整寫法，還可使用萬用字元的吻合類型。假設要尋找名字前面3個字母為Nat的學員所使用的筆記型電腦品牌，可先找2個空白的儲存格，輸入NAME和LAPTOP，分別代表學員名字和筆記型電腦品牌。

在NAME右邊的儲存格（H2）輸入Nat*（星號可代表一連串的文字），然後在LAPTOP右邊的儲存格輸入=XLOOKUP(H2, A2:A11, D2:D11, , 2)。這語法指出從儲存格A2到A11的範圍裡尋找名字開頭為Nat的學員，再向右從儲存格D2到D11的範圍裡找到這位學員使用的筆記型電腦品牌。

wildcard match using XLOOKUP function

因為在所有學員裡，名字開頭為Nat的學員只有Natasha，所以這語法會傳回Dell。如果只有一個字或字母不確定，可以使用問號（?）來取代，例如Jo?n會找到John，其餘的操作和上面示範的步驟一樣。萬用字元的吻合類型可以讓搜尋變得更有彈性，在資料搜尋時相當好用。

運用XLOOKUP函數尋找最後一個吻合的數值

XLOOKUP函數提供了4種不同的搜尋模式，包括從第1個數值開始搜尋、從最後1個數值開始搜尋、須先把資料從小至大排序的二元搜尋和須先把資料從大至小排序的二元搜尋，可在第6個引數裡做修改。預設值為從第1個數值開始搜尋，所以這函數會傳回第1個吻合數值的搜尋結果。

舉例來說，假設10位學員裡有2位學員的名字都是John，若要尋找名字為John的學員所使用的筆記型電腦品牌，在下圖的儲存格H3裡輸入=XLOOKUP(H2, A2:A11, D2:D11)，會傳回儲存格A2到A11的範圍裡從上面數下來第1個吻合的John所使用的筆記型電腦品牌Fujistsu。

在第6個引數裡，從第1個數值開始搜尋的數字選項為1，由於是預設值，所以能夠省略不寫。若想寫出來，可以使用語法=XLOOKUP(H2, A2:A11, D2:D11, , , 1)，會得到相同的結果。但這裡要稍微注意，因為這語法沒有特別寫出第4和第5個引數，所以須用逗點將它們區隔開來後，再寫出第6個引數。

first match using XLOOKUP function

如果想把搜尋模式改成從最後1個數值開始搜尋，可以將第6個引數的數字選項改成-1。同樣用上面的例子，但把語法改成=XLOOKUP(H2, A2:A11, D2:D11, , , -1)，則會傳回從下面數上來第1個吻合的John所使用的筆記型電腦品牌LG。

last match using XLOOKUP function

另外2種搜尋模式為二元搜尋，因為此種搜尋的速度很快，所以適用在資料集非常龐大的時候。但在使用二元搜尋時，須先將欲搜尋數值所在範圍的資料從小至大（引數的數字選項為2）或從大至小（引數的數字選項為-2）排序。一般的情況下，使用從第1個或從最後1個數值開始搜尋的模式即可。

以上為本篇文章對如何使用Excel的XLOOKUP函數尋找資料的介紹，希望透過本篇文章，您瞭解了這個函數的使用時機和語法，也學會了這個函數的基本操作和進階使用方法。

若您喜歡本篇文章，請將本網站加入書籤，作為您的學習資源，並持續回訪本網站喔！另外，您也可以在Facebook和Twitter上找到我們喲！

您可能也會喜歡

featured image of INDEX and MATCH functions
如何使用Excel的INDEX和MATCH函數向左尋找資料
featured image of Excel counting functions
如何使用Excel計算數目
featured image of basic math calculations using Excel
如何使用Excel進行數學計算【基礎篇】
featured image of advanced math calculations using Excel
如何使用Excel進行數學計算【進階篇】