How XLOOKUP Works
The XLOOKUP function is much easier to use than the VLOOKUP function, because instead of specifying a value for the results column, you can specify the entire range. The function also allows you to search both a column and a row, locating the value at the intersecting cell. The parameters of the XLOOKUP function are as follows:
lookup_value: The value you want to search forlookup_array: The array (column) you want to searchreturn_array: The result (column) you want to retrieve a value frommatch_mode (optional): Select an exact match (0), an exact match or next smallest value (-1), or a wildcard match (2).search_mode (optional): Select whether to search starting with the first item in the column (1), the last item in the column (-1), binary search ascending (2) or binary search descending (-2).
The following are a few of the most common lookups you can do with the XLOOKUP function.
How to Search for a Single Result Using XLOOKUP
The easiest way to use XLOOKUP is to search for a single result using a data point from one column.
Perform Vertical and Horizontal Match with XLOOKUP
Another capability of XLOOKUP that VLOOKUP isn’t capable of is the ability to perform both a vertical and horizontal search, meaning you can search for an item down a column, and across a row as well.
I2: Points to the Rep Name search cellC2:C44: This is the Rep column, which is the lookup arrayG2:G33: This is the Total column, which is the return array0: Selects an exact match1: Selects the first match in the results
D2: Points to the Item search cellD2:D44: This is the Item column, which is the lookup arrayC2:C44: This is the Rep column, which is the return array to the left of the lookup array0: Selects an exact match1: Selects the first match in the results
Using the XLOOKUP Function
The XLOOKUP function is only available to Office Insider subscribers, but will soon be rolled out to all Microsoft 365 subscribers.
J2: Points to the Rep search cellB2:B42: This is the Item column, which is the column lookup arrayK2: Points to the Quarter search cellC1:H1: This is the row lookup arrayC2:H42: This is the lookup array for the dollar amount in each quarter
This nested XLOOKUP function first identifies the sales rep, and the nexted XLOOKUP function identifies the desired quarter. The return value will is the cell where those two intercept. If you want to test the function out yourself, you can become an Office Insider. Select File > Account, then select the Office Insider drop-down to subscribe. Once you join the Office Insider program, your installed version of Excel will receive all of the latest updates, and you can start using the XLOOKUP function.
