XLOOKUP Excel: Syntax, Examples, and Why It Beats VLOOKUP
XLOOKUP Excel guide with syntax, exact-match and wildcard examples, common errors, and when to use it instead of VLOOKUP or INDEX/MATCH.
XLOOKUP Excel is the lookup formula most analysts should learn first. It fixes the two VLOOKUP problems that waste the most time: left-to-right limits and wrong-match defaults.
For this guide, we reviewed the top 5 Google results for "xlookup excel" on May 9, 2026, checked 4 current Microsoft support pages plus 2 research sources, and mapped 9 common lookup jobs against XLOOKUP, VLOOKUP, and INDEX/MATCH. Most ranking pages cover syntax and one toy example. The bigger gaps are version limits, multi-column returns, missing-value handling, and where AI helps without hiding the workbook logic.
This guide covers the syntax, the examples that matter in business workbooks, and when a workbook tool such as Deckary becomes more useful than another one-cell formula.
| If you need to... | Use this pattern | Why it is better |
|---|---|---|
| Return one exact match | =XLOOKUP(A2,IDs,Owners) | Exact match is the default |
| Show a custom missing-value message | =XLOOKUP(A2,IDs,Owners,"Not found") | Cleaner than wrapping everything in IFERROR |
| Look left | =XLOOKUP(A2,IDs,Names) | No table-array restriction |
| Return multiple columns | =XLOOKUP(A2,IDs,Budget:Actual) | One formula can spill several columns |
| Find the last matching value | =XLOOKUP(A2,IDs,Status,"",0,-1) | Reverse search is built in |
| Use a threshold table | =XLOOKUP(B2,Bands,Rates,"",-1) | Better approximate-match control |
What XLOOKUP Excel Does#
XLOOKUP Excel searches one range or array and returns a matching value from another range or array in the same row or column. Microsoft's XLOOKUP function documentation says you can search in one column and return from another regardless of which side the return column is on.
Version support matters before you rewrite a model. Microsoft's support page lists XLOOKUP for Microsoft 365, Excel 2021, Excel 2024, Mac, and web versions, and separately notes that the function is not available in Excel 2016 or Excel 2019.
XLOOKUP Excel Syntax and the Six Arguments#
XLOOKUP Excel syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Microsoft's XLOOKUP page says the function returns the first match it finds, and if you do not specify a match mode, exact match is the default.
| Argument | What it means | Most common analyst use |
|---|---|---|
lookup_value | The value you want to find | Employee ID, SKU, account code, month |
lookup_array | The range to search | The column or row containing the key |
return_array | The range to return from | Owner, price, department, metric |
if_not_found | Custom output when no match exists | "Not found" or "" |
match_mode | Exact, approximate, or wildcard behavior | Usually leave blank or use -1, 1, or 2 when needed |
search_mode | Search order | Leave blank for first-to-last or use -1 for last-to-first |
Most business formulas only need the first 3 or 4 arguments. Reach for match_mode or search_mode only when you need thresholds, wildcard text matching, or the last matching row.
How to Use XLOOKUP Excel for Exact Match, Left Lookup, and Multiple Returns#
XLOOKUP Excel is easiest to learn through three patterns: exact match, left lookup, and multi-column return.
Suppose A2 contains an employee ID, H:H contains IDs, and J:J contains manager names:
=XLOOKUP(A2,$H$2:$H$20,$J$2:$J$20,"Not found")
That formula searches the ID list and returns the manager name from the same row. There is no column-number counting step.
If the ID is in column J and the name is in column H, the formula still works:
=XLOOKUP(A2,$J$2:$J$20,$H$2:$H$20,"Not found")
Microsoft's lookup guidance says XLOOKUP and XMATCH are improved versions of older lookup functions because they work in any direction and return exact matches by default.
Microsoft also shows that one XLOOKUP formula can return multiple adjacent columns. If B:D contains Name, Department, and Region, you can use:
=XLOOKUP(A2,$H$2:$H$20,$B$2:$D$20,"Not found")
That spills the returned values across columns, which is useful for reporting tabs where one lookup should populate several fields.
| Scenario | Formula pattern | Why it helps |
|---|---|---|
| Owner by ID | =XLOOKUP(A2,IDs,Owners,"Not found") | Clean exact match |
| Name from a right-side key | =XLOOKUP(A2,IDs,Names) | Left lookup without INDEX/MATCH |
| Return name, department, and region | =XLOOKUP(A2,IDs,Details,"Not found") | One formula, several outputs |
Blank instead of #N/A | =XLOOKUP(A2,IDs,Owners,"") | Cleaner user-facing sheet |
If your lookup pattern still relies on legacy formulas, read VLOOKUP Excel. If you want the broader formula shortlist, Excel Formulas is the better hub.
Continue reading: PowerPoint Master Slide · Best Fonts for PowerPoint · AI for Excel
Build and audit Excel workbooks with AI
Describe the model, cleanup, audit, or output sheet you need. Deckary's AI Excel agent works through the task inside your workbook.
XLOOKUP Excel vs VLOOKUP and INDEX MATCH#
XLOOKUP Excel is the better default for new workbooks, but VLOOKUP and INDEX/MATCH still matter in legacy files.
Microsoft's lookup support page says XLOOKUP works in any direction and returns exact matches by default. Microsoft's VLOOKUP documentation still shows that leaving the fourth argument blank allows approximate matching, which is why inherited models can return wrong answers without throwing errors.
| Dimension | XLOOKUP | VLOOKUP | INDEX + MATCH |
|---|---|---|---|
| Exact match by default | Yes | No | Only if you set MATCH(...,0) |
| Can look left | Yes | No | Yes |
| Return several columns at once | Yes | No | Not as clean |
| Built-in missing-value message | Yes | No | No |
| Works in older Excel versions | No | Yes | Yes |
| Best use now | New workbooks in modern Excel | Inherited legacy models | Backward-compatible advanced lookups |
Use this rule:
- Use
XLOOKUPfor new work in Microsoft 365, Excel 2021, or Excel 2024. - Keep
VLOOKUPwhen you are maintaining a legacy file that other users open in Excel 2016 or Excel 2019. - Use
INDEX/MATCHwhen you need backward-compatible left lookups or when the workbook already uses that pattern heavily.
How to Use XLOOKUP for Approximate Match, Wildcards, and Last Match#
Most XLOOKUP tutorials stop after the exact-match example. The three advanced patterns below save more time in real workbooks.
| Pattern | Formula pattern | Best use |
|---|---|---|
| Next smaller match | =XLOOKUP(B2,$M$2:$M$8,$N$2:$N$8,"",-1) | Rate cards, tax bands, commission tables |
| Wildcard match | =XLOOKUP("*"&A2&"*",$H$2:$H$20,$J$2:$J$20,"Not found",2) | Partial text search |
| Last match | =XLOOKUP(A2,$H$2:$H$200,$J$2:$J$200,"",0,-1) | Latest status or last repeated value |
Microsoft's match_mode documentation says -1 returns the next smaller item, 1 returns the next larger item, and 2 enables wildcard matching with *, ?, and ~. Microsoft also says search_mode=-1 starts from the last item and searches upward.
Common XLOOKUP Errors and Limits#
XLOOKUP failures usually come from workbook conditions, not the syntax itself.
| Symptom | Likely cause | Fix |
|---|---|---|
#N/A | No valid match and no if_not_found argument | Add if_not_found, then inspect the source key |
| Wrong result from an approximate lookup | Threshold table is unsorted or wrong match mode | Recheck the sort order and match_mode |
| No formula autocomplete | Excel version does not support XLOOKUP | Confirm the user is on Microsoft 365, Excel 2021, or Excel 2024 |
| Missing match that should exist | Text-number mismatch or hidden spaces | Align data types and clean the source |
| Spill problem on multi-column return | Neighboring cells are not empty | Clear the destination range before using a multi-column return |
Binary search deserves one warning. Microsoft says search_mode values 2 and -2 rely on the lookup array being sorted, and invalid results will be returned if it is not.
When AI Helps With XLOOKUP#
Microsoft and LinkedIn's 2024 Work Trend Index surveyed 31,000 knowledge workers across 31 markets and found that 75% use AI at work. The 2023 paper InstructExcel: A Benchmark for Natural Language Instruction in Excel introduced a benchmark with over 10,000 samples, 170-plus Excel operations, and 2,000 public spreadsheets, showing that spreadsheet instruction following is still hard for strong models.
Use AI to draft an XLOOKUP, rewrite a VLOOKUP, or explain why one set of rows fails while another works. If the real task is "write 300 lookups across tabs, create an output sheet, and verify mismatches," Deckary is a better fit because it can work inside the workbook and check the result. For adjacent workflows, read Best AI for Excel, ChatGPT for Excel, and How to Use Copilot in Excel.
Summary#
XLOOKUPis the best default lookup formula in modern Excel.- It is safer than
VLOOKUPbecause exact match is the default and left lookup works natively. if_not_found, wildcard mode, approximate match, and reverse search solve most advanced cases.- AI can draft and explain lookup formulas, but workbook-wide jobs need a tool that can verify the result inside the file.
Sources#
- Microsoft Support: XLOOKUP function
- Microsoft Support: Look up values with VLOOKUP, INDEX, or MATCH
- Microsoft Support: VLOOKUP function
- Microsoft and LinkedIn: 2024 Work Trend Index Annual Report
- Justin Payan et al.: InstructExcel: A Benchmark for Natural Language Instruction in Excel
Related Guides#
Build and audit Excel workbooks with AI
Describe the model, cleanup, audit, or output sheet you need. Deckary's AI Excel agent works through the task inside your workbook.