How to Use the XLOOKUP Function in Excel

The VLOOKUP function has always been one of Excel's most powerful functions. It let you search for values in the first column of a table, and return values from fields on the right. But Excel also has a function called XLOOKUP, which allows you to search for a value in any column or row, and return data from any other column.

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:

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

• lookup_value: The value you want to search for
• lookup_array: The array (column) you want to search
• return_array: The result (column) you want to retrieve a value from
• match_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.

1. This example spreadsheet is a list of orders submitted by sales representatives, including the item, number of units, cost, and total sale.

   

2. If you want to find the first sale in the list submitted by a specific sales rep, you could create an XLOOKUP function that searches the Rep column for a name. The function will return the result from the Total column. The XLOOKUP function for this is:

    =XLOOKUP(I2,C2:C44,G2:G44,0,1)
   
   

   • I2: Points to the Rep Name search cell
   • C2:C44: This is the Rep column, which is the lookup array
   • G2:G33: This is the Total column, which is the return array
   • 0: Selects an exact match
   • 1: Selects the first match in the results

3. When you press Enter and type the name of a sales rep, the Total result cell will show you the first result in the table for that sales rep.

   

4. If you want to search for the most recent sale (since the table is ordered by date in reverse order), change the last XLOOKUP argument to -1, which will start the search from the last cell in the lookup array and provide you with that result instead.

   

5. This example shows a similar search that you could perform with a VLOOKUP function by using the Rep column as the first column of the lookup table. However, XLOOKUP lets you search for any column in either direction. For example, if you want to find the sales rep who sold the first Binder order of the year, you would use the folloing XLOOKUP function:

    =XLOOKUP(I2,D2:D44,C2:C44,0,1)
   
   

   • D2: Points to the Item search cell
   • D2:D44: This is the Item column, which is the lookup array
   • C2:C44: This is the Rep column, which is the return array to the left of the lookup array
   • 0: Selects an exact match
   • 1: Selects the first match in the results

6. This time, the result will be the name of the sales rep who sold the first binder order of the year.

   

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.

1. In the following example spreadsheet, the sales for each sales rep are split by quarter. If you wanted to see the third quarter sales for a specific sales rep, without the XLOOKUP function, this kind of search would be difficult.

   

2. With the XLOOKUP function, this kind of search is easy. Using the folloing nexted XLOOKUP function, you can search for the third quarter sales for a specific sales rep:

    =XLOOKUP(J2,B2:B42,XLOOKUP(K2,C1:H1,C2:H42))
   
   

   • J2: Points to the Rep search cell
   • B2:B42: This is the Item column, which is the column lookup array
   • K2: Points to the Quarter search cell
   • C1:H1: This is the row lookup array
   • C2: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.

3. The result for this formula is the quarter one earnings for the representative with the name Thompson.

   

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.

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.