You need to find a single value from a list in Excel, but your formula returns multiple results or an error. This happens when your lookup range contains duplicate values. The XLOOKUP function is designed to handle this by default. This article explains how to use XLOOKUP to reliably return only the first matching item.
Key Takeaways: Using XLOOKUP for the First Match
- XLOOKUP with default arguments: Automatically finds and returns the value from the first matching row in your data.
- match_mode argument set to 0: Ensures the function looks for an exact match, which is the most common use.
- search_mode argument omitted or set to 1: Directs the function to search from the first item to the last, guaranteeing the first match is found.
How XLOOKUP Finds the First Match by Default
XLOOKUP is a modern replacement for functions like VLOOKUP and HLOOKUP. Its primary design is to search for a lookup value within a lookup array. When it finds a match, it returns a corresponding value from a return array located in the same position. A key behavior is that XLOOKUP stops its search as soon as it encounters the first matching value in the lookup array. You do not need to sort your data for this to work correctly. The function will search from the top of your specified range downward by default, making the first occurrence it finds the one it returns.
Steps to Write an XLOOKUP Formula for the First Match
The basic syntax for XLOOKUP is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). To return the first match, you typically only need the first three arguments. The optional arguments control behavior for missing values, approximate matches, and search direction.
- Identify your lookup value
Select the cell that contains the value you want to find, or type the value directly into the formula enclosed in quotes. - Define the lookup array
Select the range of cells where Excel should search for your lookup value. This range should contain the potential duplicates. - Define the return array
Select the range of cells from which you want to pull the result. This range must be the same size as your lookup array. - Enter the basic formula
In your result cell, type =XLOOKUP(, then click on your lookup value cell, type a comma, select your lookup array, type a comma, and select your return array. Close the formula with a parenthesis and press Enter.
Using Optional Arguments for Control
For precise control, you can use the optional arguments. The match_mode argument is critical for ensuring an exact match.
- Add the if_not_found argument
After the return_array, type a comma and then your custom error message in quotes, like “Not Found”. This makes the output cleaner if no match exists. - Set the match_mode to 0
Add another comma after the if_not_found text, then type the number 0. This explicitly tells XLOOKUP to find an exact match. - Confirm the search_mode
You can usually omit the final search_mode argument. If you include it, use 1 to search first-to-last or -1 to search last-to-first. The default is 1, which finds the first match.
Common Mistakes and Limitations to Avoid
Formula Returns #N/A Error
This error means XLOOKUP cannot find your lookup value. First, check for typos or extra spaces in either the lookup value or the data within the lookup array. Use the TRIM function to remove spaces. Ensure your match_mode is set to 0 for an exact match, or consider using 1 for an approximate match if working with sorted numerical data.
Formula Returns the Wrong Value
If the result is incorrect, your lookup_array and return_array are likely misaligned. Verify that both selected ranges start on the same row and contain the same number of rows. One range being a single cell while the other is a column is a common mistake. Also, confirm you are looking in the correct column for your lookup value.
Need to Find the Last Match Instead
XLOOKUP can find the last matching duplicate by changing the search_mode argument. Use a formula like =XLOOKUP(F2, A2:A100, B2:B100, , 0, -1). The -1 at the end tells Excel to search from the bottom of the list upward, returning the last match it finds.
XLOOKUP vs. VLOOKUP for First Match
| Item | XLOOKUP | VLOOKUP |
|---|---|---|
| Default search behavior | Returns first match automatically | Returns first match automatically |
| Search direction flexibility | Can search first-to-last or last-to-first | Can only search top-to-bottom |
| Column reference method | Uses a separate return array range | Requires a static column index number |
| Handling of not-found values | Has a dedicated [if_not_found] argument | Requires wrapping in IFERROR function |
| Lookup array location | Lookup array can be any column, left or right | Lookup value must be in the first column of the table |
You can now use XLOOKUP to cleanly extract the first matching value from a list containing duplicates. Try using the [if_not_found] argument to handle missing data gracefully in your reports. For more complex scenarios, remember you can use XLOOKUP to perform a two-way lookup by nesting it inside another XLOOKUP as the lookup_value argument.