The XLOOKUP formula in Excel is =XLOOKUP(lookup_value, lookup_array, return_array). For example, =XLOOKUP("A-100", A2:A10, B2:B10) finds “A-100” in cells A2:A10 and returns the matching value from B2:B10. XLOOKUP is available in Excel 2021 and Microsoft 365, and it replaces older functions like VLOOKUP and HLOOKUP with a simpler, more flexible syntax.
What Is XLOOKUP and Why Was It Introduced?
XLOOKUP is a lookup function that searches for a value in one column or row and returns a corresponding value from another column or row. Microsoft introduced it in 2019 as part of Excel for Microsoft 365, and it later appeared in Excel 2021. It was designed to overcome the limitations of VLOOKUP, which could only look to the right of the lookup column, required the lookup column to be the first column in the table, and broke when columns were inserted or deleted. XLOOKUP works in any direction, handles missing values gracefully, and supports approximate matches, wildcards, and multiple return columns.
How XLOOKUP Works: Syntax and Arguments
The full syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Here is what each argument does:
- lookup_value – the value you want to find (a number, text, or cell reference).
- lookup_array – the range to search in (one column or row).
- return_array – the range to return a value from (must be the same size as lookup_array).
- if_not_found – optional text or value to show if no match is found (e.g., “Not found”).
- match_mode – optional: 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, 2 for wildcard match.
- search_mode – optional: 1 for search from first (default), -1 for search from last, 2 for binary search (ascending), -2 for binary search (descending).
For example, with product names in A2:A20 and prices in B2:B20, =XLOOKUP("Widget", A2:A20, B2:B20, "No price") returns the price of the Widget, or “No price” if it is absent.
How to Use XLOOKUP: Step-by-Step Example
Suppose you have an employee table with Employee ID in column A (A2:A10) and Department in column B (B2:B10). To find the department for ID “E104”:
- Click a cell where you want the result, say D2.
- Type
=XLOOKUP(. - Click cell C2 (where you typed “E104”) or type the value directly.
- Type a comma, then select A2:A10.
- Type a comma, then select B2:B10.
- Close the parenthesis and press Enter:
=XLOOKUP(C2, A2:A10, B2:B10).
The result is the department name. If you type an ID that does not exist, you get #N/A unless you add the fourth argument: =XLOOKUP(C2, A2:A10, B2:B10, "ID not found").
XLOOKUP vs. VLOOKUP vs. INDEX-MATCH
XLOOKUP is generally the best choice for new workbooks because it is simpler and more powerful. Here is how it compares:
- XLOOKUP – looks left or right, returns any column, handles missing values, supports wildcards, and does not break when columns are added.
- VLOOKUP – only looks to the right, requires the lookup column to be first, and returns #N/A for missing values unless you wrap it in IFERROR. Still works in all Excel versions.
- INDEX-MATCH – a two-function combo that can look left or right and is flexible, but it is longer and harder to read. Some users prefer it for very large datasets because it can be faster, though XLOOKUP is usually fast enough.
If you share a workbook with people using Excel 2019 or earlier, XLOOKUP will not work; use VLOOKUP or INDEX-MATCH instead. In Excel 2021 and Microsoft 365, XLOOKUP is the recommended function.
Common Problems and How to Fix Them
Several issues come up frequently:
- #N/A error – the lookup value is not found. Check for extra spaces, different data types (text vs. number), or use the if_not_found argument.
- #VALUE! error – the lookup_array and return_array have different sizes. Make sure they are the same number of rows or columns.
- #REF! error – the return_array is not the same shape as lookup_array, or the ranges are not valid.
- Incorrect approximate match – if you use match_mode -1 or 1, ensure your lookup_array is sorted correctly, or you may get unexpected results.
- XLOOKUP not available – you are on Excel 2019 or older. Use VLOOKUP or upgrade to Microsoft 365.
For text lookups, trim spaces with =XLOOKUP(TRIM(C2), A2:A20, B2:B20) to avoid mismatches.
Advanced XLOOKUP Techniques
XLOOKUP can do more than simple lookups:
- Return multiple columns – use a return_array that spans several columns, e.g.,
=XLOOKUP(C2, A2:A10, B2:D10)returns all three values in a spill range (Excel 365 only). - Find the last match – set search_mode to -1:
=XLOOKUP(C2, A2:A10, B2:B10, , , -1)returns the value from the last row where the lookup value appears. - Wildcard matching – set match_mode to 2 and use * or ? in the lookup value, e.g.,
=XLOOKUP("A*", A2:A10, B2:B10, , 2)finds the first value starting with A. - Approximate match for grades – set match_mode to -1 to find the largest value less than or equal to the lookup value, useful for scoring bands.
- Combine with IFERROR – although XLOOKUP has its own if_not_found, you can still use IFERROR for more complex logic.
Frequently Asked Questions
Is XLOOKUP available in Excel 2019? No. XLOOKUP is only in Excel 2021 and Microsoft 365 (and Excel for the web). If you have an older version, use VLOOKUP or INDEX-MATCH.
Can XLOOKUP return multiple values? Yes, in Excel 365 you can specify a return_array with multiple columns, and the results spill into adjacent cells automatically. In Excel 2021, it also works, but you need to select multiple cells and press Ctrl+Shift+Enter.
How do I handle missing values with XLOOKUP? Use the fourth argument, for example =XLOOKUP(C2, A2:A10, B2:B10, "Not found"). If you omit it, you get #N/A.
What is the difference between XLOOKUP and INDEX-MATCH? XLOOKUP is a single function that is easier to read and write. INDEX-MATCH uses two functions and is more verbose, but it works in all Excel versions. For most users, XLOOKUP is preferred.
Why does XLOOKUP return #VALUE!? This usually means the lookup_array and return_array are not the same size. Check that both ranges have the same number of rows (for vertical lookups) or columns (for horizontal lookups).
Can XLOOKUP look up from right to left? Yes, unlike VLOOKUP, XLOOKUP can return a value from any column, even one to the left of the lookup column. Just set the return_array to that column.
Related
- Excel Eye Center: Services, Costs & What to Expect
- Excel Energy Group: What It Is and How It Works
- Excel Employee Rotation Schedule Template Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIF in Excel: Step-by-Step Formula, Examples, and TipsTo total values that match one condition in Excel, use SUMIF. If categories are in A2:A7 and amounts…
- Split Cells in Excel: Text to Columns and FormulasTo split the contents of a cell in Excel, select the cells, then choose Data > Text to…
- How to Close the Formula Bar in Excel on Any DeviceTo close—or hide—the formula bar in Excel, open the View tab and clear the Formula Bar checkbox. In…
- Python String Functions: Methods, Examples, and UsesPython string functions are mainly string methods—operations such as upper(), strip(), replace(), and split() that work on text.…
- XLOOKUP Example Table: Formulas, Results, and Tips in ExcelWith product IDs in A2:A6, prices in C2:C6, and the ID to find in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found").…
- What-If Analysis in Excel: Scenarios, Goal Seek & MoreIn Excel for Microsoft 365 on Windows, what-if analysis helps you see how changing input values affects a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.