如何在 Excel 2016 中使用 XLOOKUP 函數

適用於 Windows 和 Mac 上的 Office 365 訂閱者的 Excel 2016 現在支持新的 XLOOKUP 函數，吹捧它是非常流行（但經常受到誹謗）垂直查找函數 VLOOKUP（不知道 XLOOKUP 中的 X代表；可能是擴展性的？）。

對於那些還不熟悉 VLOOKUP（被認為是僅次於 SUM 和 AVERAGE 的第三個最常用的函數）的人，該函數從上到下在指定查找表的最左列中逐行垂直搜索，直到找到一個值由匹配或超過您查找的偏移編號指定的查找列。儘管對於在工作表中的數據表的長列表或列中定位特定項目非常有用，但 VLOOKUP 函數具有一些此新查找函數沒有的限制，如 XLOOKUP：

• 默認為查找範圍內查找值的精確匹配
• 可以在表格中垂直（按行）和水平（按列）搜索，從而在按列水平搜索時無需使用 HLOOKUP 函數
• 可以向左或向右搜索，以便查找表中的查找範圍不必位於指定為返回範圍的左側的列中，以便函數正常工作
• 使用精確匹配默認值時，即使查找範圍中的值未按特定順序排序也能正常工作
• 可以使用可選的搜索模式參數從查找數組範圍的底部行到頂部進行搜索

XLOOKUP 函數有五個可能的參數，其中前三個是必需的，最後兩個是可選的，使用以下語法：

XLOOKUP( lookup_value , lookup_array , return_array ,[ match_mode ],[ search_mode ])

必需的lookup_value參數指定您要搜索的值或項目。所需的look_up 數組參數指定要為該查找值搜索的單元格範圍，而return_array參數指定包含 Excel 找到完全匹配時要返回的值的單元格範圍。

* 請記住，在 XLOOKUP 函數中指定 lookup_array 和 return_array 參數時，兩個範圍的長度必須相等，否則 Excel 將返回 #VALUE！你的公式錯誤。這也是您在定義這些參數時使用指定數據表的範圍名稱或列名稱而不是指出它們或輸入它們的單元格引用的更多原因。

可選的match_mode參數可以包含以下四個值中的任何一個：

• 0表示完全匹配（默認值，與未指定match_mode參數時相同）
• -1表示完全匹配或下一個較小的值
• 1表示完全匹配或下一個更大的值
• 2用於在lookup_value參數中使用連接到單元格引用的通配符進行部分匹配

可選的search_mode參數可以包含以下四個值中的任何一個：

• 1從頭到尾搜索，即從上到下（默認，與不指定search_mode參數時相同）
• -1 從後到先搜索，即從下到上
• 2升序二分查找
• -2降序二分查找

了解新 XLOOKUP 函數的強大功能和多功能性的最佳方法是在 Excel 工作表中查看它的運行情況。在下圖中，我有一個工作表，其中包含按國家/地區排列的簡單 2019 年銷售數據表。要使用 XLOOKUP 根據您在工作表的單元格 D4 中輸入的國家/地區，在單元格 E4 中返回此表中的總銷售額，請執行以下步驟：

將單元格光標定位在工作表的單元格 E4 中

單擊“公式”選項卡上的“查找和引用”選項，然後單擊下拉菜單底部附近的 XLOOKUP 以打開其“函數參數”對話框。

單擊工作表中的單元格 D4 以將其單元格引用輸入到 Lookup_value 參數文本框中。

按 T​​ab 鍵選擇 Lookup_array 參數文本框，然後單擊單元格 A4 並按住 Shift 鍵同時按 Ctrl-向下箭頭選擇 A4:A8 作為要搜索的範圍（因為範圍 A3:B8 被定義為 Excel 數據表， Table1[Country] 出現在文本框中，代替範圍 A4:A8)。

按 T​​ab 鍵選擇 Return_array 參數文本框，然後單擊單元格 B4 並按住 Shift 鍵，同時按 Ctrl-向下箭頭選擇 B4:B8 作為包含要根據搜索結果返回的值的範圍（顯示為表 1[Total Sales] 在文本框中）。

單擊確定以在單元格 E4 中輸入 XLOOKUP 公式。

在單元格 E4 中使用 XLOOKUP 創建一個公式，該公式根據在單元格 D4 中輸入的國家/地區返回銷售額。

Excel 將 XLOOKUP 公式輸入到工作表的單元格 E4 中並返回 4900 作為結果，因為哥斯達黎加當前輸入到查找單元格 D4 中，正如您在 2019 年銷售表中看到的那樣，這確實是該國家/地區的總銷售額。

因為 XLOOKUP 從右到左和從左到右一樣工作，所以您也可以使用此函數根據特定的銷售數字從該銷售表中返回國家/地區。下圖顯示瞭如何執行此操作。這一次，您在單元格 D4 中創建 XLOOKUP 公式並將在單元格 E4 中輸入的值（在本例中為 11,000）指定為 lookup_value 參數。

此外，您輸入 -1 作為 match_mode 參數以覆蓋函數的完全匹配默認值，以便 Excel 返回與在查找單元格 E4 中輸入的銷售值完全匹配的國家/地區或總銷售額下一個較低的國家（墨西哥與在這種情況下為 10,000 美元，因為此表中沒有總銷售額為 11,000 美元的國家/地區）。如果沒有為此公式指定 match_mode 參數，Excel 將返回 #NA 作為結果，因為此銷售表中沒有與 $11,000 的完全匹配。

在單元格 D4 中使用 XLOOKUP 創建一個公式，根據單元格 E4 中輸入的銷售額返回國家/地區

由於 XLOOKUP 函數在按列水平搜索和按行垂直搜索同樣方便，因此您可以使用它來創建一個執行雙向查找的公式（無需創建一個將 INDEX 和 MATCH 函數組合在一起的公式）在過去）。下圖包含 4 月到 12 月的 AB-100 到 AB-103 零件編號的 2019 年生產計劃表，向您展示了這是如何完成的。

使用嵌套的 XLOOKUP 函數創建一個公式，以返回特定月份為零件生產的單位數

在單元格 B12 中，我創建了以下公式：

=XLOOKUP(part_lookup,$A$3:$A$6,XLOOKUP(date_lookup,$B$2:$J$2,$B$3:$J$6))

此公式首先定義一個 XLOOKUP 函數，該函數按行垂直搜索與生產表的單元格區域 $A$3:$A$6 中名為 part_lookup 的單元格（在本例中為單元格 B10）中創建的零件條目的精確匹配. 但是請注意，這個原始 LOOKUP 函數的 return_array 參數本身就是第二個 XLOOKUP 函數。

第二個嵌套的 XLOOKUP 函數按列水平搜索單元格區域 $B$2:$J$2，以查找與名為 date_lookup 的單元格（在本例中為單元格 B11）中創建的日期條目的精確匹配。第二個嵌套 XLOOKUP 函數的 return_array 參數是 $B$3:$J$6，即表中所有生產值的單元格範圍。

此公式的工作方式是 Excel 首先通過執行水平搜索來計算第二個嵌套 XLOOKUP 函數的結果，在本例中，返回單元格區域 D3：D6 中 Jun-19 列的數組（具有值： 438、153、306 和 779）作為其結果。反過來，此結果成為原始 XLOOKUP 函數的 return_array 參數，該函數按行執行垂直搜索，以精確匹配在單元格 B11 中創建的零件編號條目（名為 part_lookup）。因為在此示例中，此 part_lookup 單元格包含 AB-102，因此該公式僅從第二個 XLOOKUP 函數的結果返回 Jun-19 的生產值 306。

你有它！首先看一下 XLOOKUP，這是一個功能強大、通用且相當易於使用的新查找函數，它不僅可以執行 VLOOKUP 和 HLOOKUP 函數執行的單值查找，還可以執行組合INDEX 和 MATCH 函數也是如此。

* 不幸的是，XLOOKUP 函數不向後兼容僅支持 VLOOKUP 和HLOOKUP 函數的早期版本的 Microsoft Excel，也不兼容尚未將其作為其查找功能之一的當前版本，例如 Excel 2019 和 Excel Online 。這意味著，如果您與使用不包含此新查找功能的 Excel 版本的同事或客戶共享包含 XLOOKUP 公式的工作簿，所有這些公式都將返回 #NAME? 打開工作表時的錯誤值。