XLOOKUP searches a row or column and returns the corresponding value from another range. It uses exact matching by default and can look to either side of the lookup column. You can also return several columns from one formula.
XLOOKUP is available in Excel for Microsoft 365, Excel for the web, Excel 2021 and Excel 2024, including the supported Mac editions. It is not available in Excel 2016 or Excel 2019. For those versions, use VLOOKUP or INDEX and MATCH. See Microsoft’s XLOOKUP documentation for compatibility and argument details.
Exact Match Example You Can Try
Enter these headers in A1:C1 and the four data rows in A2:C5 on a blank worksheet. Enter 103 in F2.
| A: ID | B: Name | C: Department |
|---|---|---|
| 101 | Ana | Sales |
| 102 | Ben | Finance |
| 103 | Cara | Sales |
| 104 | Dan | IT |
=XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,"Not found") returns Cara. Change F2 to 999 and the result is Not found. Without the fourth argument, a missing match returns #N/A. The dollar signs keep the data ranges fixed when you copy the formula.
To return both the name and department, use =XLOOKUP(F2,$A$2:$A$5,$B$2:$C$5). With F2 set to 103, the results are Cara and Sales in adjacent cells. Keep the second output cell empty; a blocked output produces #SPILL!.
Exact and Approximate Match Modes
Use 0 for exact matching, -1 for an exact match or the next smaller value, 1 for an exact match or the next larger value, and 2 for wildcard matching. These are the fifth argument, match_mode. Enter this quantity table in H1:I5; enter the discounts as percentages.
| H: Minimum quantity | I: Discount |
|---|---|
| 0 | 0% |
| 10 | 5% |
| 20 | 10% |
| 50 | 15% |
=XLOOKUP(17,$H$2:$H$5,$I$2:$I$5,"Not found",-1) returns 5%, using the lower threshold of 10. Changing -1 to 1 returns 10%, using the upper threshold of 20. Approximate matching chooses a value on the specified side; it does not choose whichever number is nearest.
The normal search does not require sorted data. Binary search modes 2 and -2 do: sort the lookup array ascending for 2 or descending for -2, keeping the return data aligned. An unsorted binary search can give an incorrect result.
Duplicates and Common XLOOKUP Errors
XLOOKUP returns the first matching row by default. To search from the last row upward, use =XLOOKUP(103,$A$2:$A$5,$B$2:$B$5,"Not found",0,-1). If you change A5 from 104 to 103 in the sample, this returns Dan; the default search returns Cara.
For an unexpected #N/A, compare number and text types and check for extra spaces. For #VALUE!, check that the lookup and return ranges have compatible dimensions. An _xlfn.XLOOKUP prefix or #NAME? can indicate an unsupported Excel version. Keep multi-cell output formulas outside Excel Tables, with clear space for their results.
Basics of XLOOKUP
Let’s focus on the basics of using the XLOOKUP function first. We’ll be using a simple example to demonstrate how the function works. Let’s do this by looking up a last name based on a row number with the XLOOKUP function.
Step 1: Understand the Arguments of the XLOOKUP Function
You’ll need to know its arguments to understand how the XLOOKUP function works. There are three main arguments: the lookup value, the lookup array, and the return array. There are also three optional arguments which we will ignore for now. The formula for this function is
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Step 2: Enter the XLOOKUP Function in the Worksheet
Once you understand the arguments of the XLOOKUP function, you can enter them into the worksheet. To do this, simply start typing “=XLOOKUP” in the cell where you want the result to appear, and Excel will automatically show you the function’s arguments.
Step 3: Enter the Lookup Value and Lookup Array
In our example, the lookup value will be the individual rolls, the lookup array will be the row numbers (roll numbers), and the return array will be the last names. The formula will become =XLOOKUP($I$5,$A$4:$A$16,$C$4:$C$16)
Make sure to make the references absolute by selecting and using the F4 key. This will prevent the data from moving around and ensure the formula always references the correct cells.
Step 4: Close the Parenthesis and Check the Result
Once you’ve entered all the arguments, close the parenthesis and press Enter. The result of the XLOOKUP function should appear in the cell where you entered the formula. In our example, it should return the last name corresponding to the roll we entered.
Left Lookup Using XLOOKUP Function
VLOOKUP searches the first column of its table and returns a value from a column to the right. XLOOKUP uses separate lookup and return ranges, so it can also return a value from the left.
Step 1: Enter the XLOOKUP Function in the Worksheet
Once you understand the left lookup function, you can enter it into the worksheet. To do this, simply start typing “=XLOOKUP” in the cell where you want the result to appear, and Excel will automatically show you the function’s arguments.
Step 2: Enter the Lookup Value and Lookup Array
In the first argument of the XLOOKUP function, enter the lookup value (in our example, the last name is the lookup value). Enter the lookup array (the last name column) in the second. Enter the return array (the first name column) in the third argument. Make sure to make the references absolute by using the F4 key. The formula will =XLOOKUP($I$6,$C$4:$C$16,$B$4:$B$16)
Step 3: Close the Parenthesis and Check the Result
Once you’ve entered all the arguments, close the parenthesis and press Enter. You should get the result after that.
Multiple Values Using XLOOKUP Function
XLOOKUP can return several columns from the matching row. Use a return array containing all the columns you need, and leave enough empty cells beside the formula for the results.
Step 1: Identify the Values
The first step in using the XLOOKUP function to retrieve multiple values is to identify the lookup value and lookup array. In our example, the lookup value is row number A2 (a specific roll), and the lookup array is the range of roll number (1 to 13).
You’ll also need to identify the return array. In our example, the return array is the range of the data in the three columns next to the lookup value (B4:D16).
Step 2: Enter the XLOOKUP Function in the Worksheet
Once you’ve identified the lookup value, lookup array, and return an array, you can enter the XLOOKUP function in the worksheet. To do this, start by typing “=XLOOKUP” in the cell. Then, enter the lookup value (A2) and lookup array (A4:A16). In the third argument, enter the return array (B4:D16).The formula will look like this in our case: =XLOOKUP(A2,A4:A16,B4:D16).
Step 3: Close the Parenthesis and Check the Result
Once you’ve entered all the arguments, close the parenthesis and press Enter. The result of the XLOOKUP function should appear in the cell where you entered the formula.
Conclusion
Use the three required arguments for an exact lookup. Add if_not_found for a missing record, match_mode for thresholds or wildcards, and search_mode when you need the last matching row.
We hope you found this tutorial helpful and that you better understand how to use the XLOOKUP function in your Excel worksheets. Check out our video on the same topic, which shows several examples and the use of the function in action.
