How it works.
XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It searches one column or row for a lookup value and returns the value in the matching position from another column or row. Unlike VLOOKUP, it does not require counting columns, can search to the left, defaults to exact match, and handles missing values gracefully.
💡 In simple terms.
Searches for a value in one list (lookup array) and returns matching information from another list (return array) in the exact same row.
"Think of XLOOKUP like looking up a word in a dictionary. You have a search word (Employee ID), you scan the dictionary list (ID Column), and you get the corresponding definition (Department Column)."
Syntax guide.
Parameters
The value you want to search for.
The range or array to search in.
The range or array to return values from.
Text or value to return if no match is found.
Default: "Not Found"0 = Exact match (default), -1 = Exact or next smaller, 1 = Exact or next larger, 2 = Wildcard match.
Default: 01 = Search first-to-last (default), -1 = Search last-to-first, 2 = Binary search (sorted asc), -2 = Binary search (sorted desc).
Default: 1Interactive formula builder.
Fill in the input arguments below to construct your custom formula string. Copy it directly to paste into your Excel spreadsheets.
Best Practices
- Use XLOOKUP to return multiple items simultaneously by selecting a multi-column return range.
- Unlike VLOOKUP, XLOOKUP does not break when columns are inserted or deleted in the spreadsheet.
- Leave match_mode blank if you want an exact match, which is what is needed 95% of the time.
Common Gotchas
- XLOOKUP is only available in Excel 365, Excel 2021, and Excel for the Web. It will return a #NAME? error in older versions.
- Ensure the lookup_array and return_array are of equal length; otherwise, XLOOKUP returns a #VALUE! error.