如何在Excel中使用XLOOKUP函數

Excel 中的 Xlookup 函數是什麼？如何在Excel中使用Xlookup？讓我們透過 LuckyTemplates.com 來了解一下吧！

如果您經常使用 Excel，您可能對尋找功能非常熟悉。如果你不知道，你只需要把它簡單地理解為一個Excel函數，用於搜尋電子表格中的行或列上的特定資料或值。

當您必須處理 Excel 電子表格中的大量資料時，此功能特別有用。在 Excel 中，有多種類型的 LOOKUP 函數。XLOOKUP 就是其中之一。那什麼是XLOOKUP函數，XLOOKUP公式是什麼以及如何使用呢？讓我們透過 LuckyTemplates.com 來了解一下吧！

有關 Excel 中 XLOOKUP 函數的知識

• 什麼是XLOOKUP？
  • 如何存取 XLOOKUP 函數
  • XLOOKUP 函數語法
• Excel中XLOOKUP函數的優缺點
  • XLOOKUP 函數的優點
  • XLOOKUP 函數的缺點
• 如何使用 XLOOKUP 函數
  • 沒有額外的列索引號
  • 預設精確匹配
  • 左邊可以查到XLOOKUP
  • 使用 XLOOKUP 搜尋範圍
  • XLOOKUP 替代 HLOOKUP 函數
  • XLOOKUP可以從下往上查看

Excel中的XLOOKUP函數是什麼？

從本質上講，XLOOKUP 取代了 HLOOKUP、VLOOKUP 和 LOOKUP 等舊函數。它支援垂直和水平查找資料。

換句話說，Excel中的XLOOKUP函數可以幫助您在提供的資料集中快速查找值，無論是水平還是垂直。然後根據不同的行和列傳回對應的值。

有許多方法可以最大限度地利用 XLOOKUP 函數。

如何存取 XLOOKUP 函數

目前，XLOOKUP 僅適用於 Insiders 計畫用戶。加入 Insider 計劃的任何人都可以存取最新的 Excel 功能（當這些功能可用時）。微軟很快就會向所有 Office 365 用戶推出此功能。不幸的是，運行 Office 2010、2013、2016 和 2019 版本的用戶無法使用 XLOOKUP。

如果您的電腦上沒有使用 Microsoft 365 套件，則可能需要升級至 Microsoft 365 才能存取 XLOOKUP 功能。如果您使用的是 Microsoft 365，您將看到 Excel 中啟用了該選項。XLOOKUP 也可在 Office 365 Online 上使用。

XLOOKUP 函數語法

XLOOKUP 函數的語法與 VLOOKUP 和 HLOOKUP 函數的語法類似。如果您以前使用過它們，您會發現使用 XLOOKUP 更方便。以下是 Excel 中 XLOOKUP 函數的語法：

= XLOOKUP (查找值,查找數組,返回數組, [if_not_found], [match_mode], [search_mode])

XLOOKUP 函數最多支援 6 個參數，以下是它們的值。

1.lookup_value （必填）：要尋找的值。

2.lookup_array （必需）：要搜尋尋找值的陣列。

3. return_array（必要）：找到查找值時要從中取得並傳回值的陣列。

4. [if_not_found]（可選）：當未找到匹配項時，這是傳回的值。

5. [match_mode]（可選）：此參數可讓您指定要尋找的符合類型。有不同的值來指定它：

• 0 - 尋找完全匹配，且該值必須​​與lookup_array 中的值完全匹配。當未提及時，該值也設定為預設值。
• -1 - 尋找完全匹配，一旦找到，將返回下一個較小的值。
• 1 - 尋找完全匹配，找到後返回下一個較大的值。
• 2 - 使用通配符執行部分匹配，其中*, ? 和〜有特殊意義。

6. [search_mode]（可選）：用於指定XLOOKUP在lookup_array中的搜尋模式。有不同的值來指定相同的：

• 1 - 從第一項開始執行搜尋。當未指定任何內容時，該值設定為預設值。
• -1 - 從最後一項開始執行反向搜尋。
• 2 - 在lookup_array中執行二分搜索，其中資料需要按升序排序。如果資料未排序，可能會產生錯誤或錯誤的結果。
• -2 - 在lookup_array中執行二分搜索，其中資料需要按降序排序。如果資料未排序，可能會產生錯誤或錯誤的結果。

Excel中XLOOKUP函數的優缺點

與VLOOKUP和INDEX/ATCH相比，XLOOKUP仍然是一個有利的功能。但它也有其自身的缺點。

XLOOKUP 函數的優點

• 下巴可以垂直和水平工作。
• 只需要 3 個參數，而不是像 VLOOKUP 和 INDEX MATCH 中需要 4 個參數。
• 始終預設為完全匹配。
• 可以使用通配符執行部分查找。
• 可依降序執行查找。
• 在 INDEX MATCH 中使用一個函數而不是兩個。

XLOOKUP 函數的缺點

• 對於初學者來說，可選參數可能看起來很複雜。
• 當選擇兩個範圍並且電子表格中的儲存格過多時，可能會花費更多時間。
• 尋找並返回長度不同的陣列時傳回錯誤。
• 記住查找範圍和返回範圍。

使用 XLOOKUP Excel 函數的好處

• XLOOKUP 讓 Excel 中最常用的公式變得更簡單且不易出錯。您只需寫=XLOOKUP（要尋找的資料、清單、結果清單）即可獲得答案，如果找不到該值，您將獲得答案或#N/A。
• 預設查找結果是準確的。VLOOKUP 的缺點之一是您必須提及 FALSE 作為最後一個參數才能獲得正確的結果。XLOOKUP 透過預設執行精確匹配來解決這個問題。如果需要，您可以使用匹配模式參數來變更查找行為。
• 第四個參數支援未找到值的情況。在大多數業務情況下，您將被迫使用 IFERROR 或 IFNA「包裝」來尋找公式以消除錯誤。XLOOKUP 提供了第四個參數，因此如果找不到該值，您可以知道所需的預設結果。
• XLOOKUP提供選擇參數來尋找特殊情況。您可以透過特殊字元從上方或下方搜索，以及更快的選項來尋找​​已過濾的資料。

如何使用 XLOOKUP 函數

請參閱下面的範例以了解 XLOOKUP 的工作原理。在此範例中，我們需要為 A 列中的每個 ID 傳回 F 列中的部分。

這是一個尋找精確結果的範例，但 XLOOKUP 函數只需要三種類型的資訊。

下圖顯示了 5 個參數的 XLOOKUP，但需要前三個參數才能得到正確的結果。那麼讓我們來關注它們：

• Lookup_value：查找值
• Lookup_array：搜尋範圍
• Return_array：包含要傳回的值的範圍

在此範例中，我們將使用以下公式：

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

現在讓我們來探討一下 XLOOKUP 相對於 VLOOKUP 的一些優點。

沒有額外的列索引號

VLOOKUP 的第三個參數是指定從表範圍傳回的資訊列數。這對 XLOOKUP 來說不再是問題，因為該函數允許使用者選擇返回範圍。（本例中的 F 列）。

並且不要忘記，XLOOKUP 可以看到所選單元格的剩餘數據，這與 VLOOKUP 不同。

新增列時您也不會遇到公式錯誤。如果電子表格中發生這種情況，則返回範圍將自動調整。

預設精確匹配

使用 VLOOKUP 時，如果需要，使用者必須指定精確匹配。但對於 XLOOKUP，預設值是完全匹配。這減少了第四個參數，並確保新用戶減少錯誤。簡而言之，XLOOKUP 比 VLOOKUP 需要的問題更少，而且用戶友好。

左邊可以查到XLOOKUP

選擇尋找範圍的能力使 XLOOKUP 比 VLOOKUP 更具彈性。使用 XLOOKUP，表中列的順序並不重要。

VLOOKUP 的限制是搜尋表的最左邊的列，然後從右邊指定數量的列傳回。

在下面的範例中，我們需要搜尋 ID（E 列）並傳回人員姓名（D 列）。

使用下面的公式：

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

使用 XLOOKUP 搜尋範圍

雖然不像精確匹配那麼常見，但您可以使用搜尋公式來尋找範圍內的值。例如，我們希望根據花費的金額返回折扣。

這次我們不是在尋找具體的值，而是需要知道B列中的值屬於E列中的什麼範圍來決定折扣。

XLOOKUP 有一個可選的第四個參數（記住，它預設為絕對匹配），稱為 match_mode。

您可以看到，XLOOKUP 可以比 VLOOKUP 更好地找到近似匹配。

有一個選項可以找到小於 (-1) 的最接近的匹配項或大於 (1) 搜尋值的最接近的匹配項。還有一個使用通配符 (2) 的選項，例如 ? 或 *。預設情況下，此設定不會像 VLOOKUP 那樣啟用。

如果找不到精確匹配，本範例中的公式將傳回小於搜尋值的最接近值：

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

但是，C7 單元格出現錯誤，應該返回 0% 折扣，因為它只消費了 64，不符合獲得折扣的標準。

XLOOKUP函數的另一個優點是它不像VLOOKUP那樣需要升序排列的搜尋範圍。

在查找表底部輸入一個新行，然後開啟公式。透過點擊並拖曳角落來擴大使用範圍。

該公式立即糾正了錯誤。範圍表末尾有 0 沒有問題。

XLOOKUP 替代 HLOOKUP 函數

如前所述，XLOOKUP 函數也可以取代 HLOOKUP 函數。一個功能可以取代兩個功能，還有什麼更好的呢？

HLOOKUP函數水平查找，用於按行查找。

此函數不像 VLOOKUP 那麼出名，但在標題位於 A 列且資料位於第 4 行和第 5 行的情況下非常有用，如下例所示。

XLOOKUP 可以雙向搜索，向下列和沿行。

在此範例中，使用公式傳回與 A2 列中的名稱關聯的銷售值。它在第 4 行中尋找名稱並傳回第 5 行的值：

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP可以從下往上查看

通常，您需要在清單中從上到下尋找以查找某個值第一次出現的位置。XLOOKUP 有第五個參數，名為 search_mode。此參數允許將搜尋重定向為從清單底部開始查找最後出現的值。

在下面的範例中，我們需要查找 A 列中每種產品的庫存水準。

查找表按日期順序排列。我們想要返回上次檢查（最後一次出現的產品 ID）的庫存水準。

XLOOKUP 函數的第五個參數提供四個選項。在這裡，我們將使用“搜尋從最後到第一個”選項。

本例所使用的公式：

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

在此公式中，第四個參數被省略。它是可選的，我們希望預設值是完全匹配的。

XLOOKUP 函數是 VLOOKUP 和 HLOOKUP 函數的預期後繼函數。本文使用了一系列範例來示範 XLOOKUP 函數的優點。

XLOOKUP 在尋找功能方面是一個很大的改進。XLOOKUP 函數的唯一限制是它不向後相容。如果您與舊版的 Microsoft Office 套件共用電子表格，則可能會收到錯誤。

但是，您可以從 Office 365 Online 存取 XLOOKUP。XLOOKUP 仍在開發中，因此 Excel 用戶需要一段時間才能採用它。

祝您成功！

XLOOKUP 和 VLOOKUP 的差別

	查找表	X查找
預設完全匹配	氮	是
傳回尋找右側的值	是	是
傳回尋找左側的值	氮	是
按行尋找	氮	是
傳回多個值	氮	是
需要對查找列進行分類	僅近似匹配	僅二分查找
從上到下搜尋	是	是
從下往上搜尋	氮	是
二分查找	氮	是
自訂未找到查找值時的消息	氮	是
透過通配符搜尋	是	是
精確搜尋	是	是
近似搜尋傳回下一個較小的值	是	是
近似搜尋傳回下一個較大的值	氮	是
如果查找值未排序，可能會傳回不正確的值	Y - 近似值	N - 近似值， Y - 二進位
新增列來尋找表格可能會破壞公式	是	氮

為什麼 XLOOKUP 比 VLOOKUP 更好？

儘管VLOOKUP有其自身的用途，但它比XLOOKUP的功能更有限。這使得 XLOOKUP 成為需要處理大量資料時最受歡迎的選擇。因此，它運行得更快並且錯誤更少。

• XLOOKUP不限制資料範圍：XLOOKUP比VLOOKUP更有彈性。VLOOKUP 可以尋找僅位於表格最左側欄位的值，並從右側對應欄位傳回值。同時，XLOOKUP只需要更簡單的步驟，並且可以在任一方向傳回任何列的值。
• XLOOKUP的預設結果是最準確的值。
• 預設情況下，Excel 中的 VLOOKUP 函數總是會尋找近似結果。但是，如果您需要精確的結果，則應在 range_lookup 參數中輸入 FALSE。
• XLOOKUP 函數在特定範圍內搜尋查找值，並且始終預設為精確值。如果找不到，您可以編輯參數以查找小於或大於查找值的近似匹配。

其他原因：

• XLOOKUP可以處理列插入或刪除。
• XLOOKUP 實作垂直和水平查找。
• XLOOKUP 提供更聰明的近似匹配。
• XLOOKUP 需要較少的儲存格參考。