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)."
Formula syntax.
Standard Excel Notation=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])[like_this] are optional; all other arguments are required.Formula arguments.
Every parameter accepted by the XLOOKUP function is detailed below.
The value you want to search for.
Example value:D2The range or array to search in.
Example value:A2:A6The range or array to return values from.
Example value:B2:B6Text 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: 1Simple example.
Step-by-Step ScenarioDepartment Directory Lookup
Retrieve an employee's Department by searching their Employee ID.
| Emp ID | Department | Salary | Search ID | Result Dept |
|---|---|---|---|---|
| 101 | Engineering | 105000 | 103 | — |
| 102 | Marketing | 88000 | — | — |
| 103 | Sales | 95000 | — | — |
=XLOOKUP(D2, A2:A6, B2:B6, "Not Found")Evaluates XLOOKUP over the sample table cells, placing the verified output in cell E2.
Formula generator.
Full Generator WizardCustomize the argument fields below to generate a tailored XLOOKUP formula ready to paste directly into your worksheet.
=XLOOKUP()Expected result behavior.
Returns "Sales" based on sample evaluation criteria and active cell references.
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 Mistakes
- 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.
Platform & version compatibility.
Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.
In Excel 2019 or legacy versions, use INDEX & MATCH (=INDEX(return_range, MATCH(lookup_val, lookup_range, 0))) which works in all versions.
Introduced in Excel 365 and Excel 2021. Not supported in Excel 2019 or Excel 2016 (#NAME? error).
Fully supported in Google Sheets with identical parameters, default exact match, and search modes.
Frequently asked questions.
Technical Q&ACan XLOOKUP look up values to the left?↓
Yes. Unlike VLOOKUP which requires the return column to be to the right of the lookup column, XLOOKUP uses separate lookup_array and return_array ranges, allowing you to return data located in any column or row.
What is the primary difference between XLOOKUP and VLOOKUP?↓
XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate match), does not break when columns are inserted into the table, handles missing values gracefully with the if_not_found parameter, and supports both vertical and horizontal searches.
Why does XLOOKUP return a #VALUE! error?↓
The #VALUE! error in XLOOKUP happens when lookup_array and return_array have incompatible dimensions (for example, a 5-row lookup range paired with an 8-row return range). Ensure both arrays cover the exact same number of rows or columns.