XLOOKUP vs VLOOKUP: Differences, Examples and When to Use Each
TLDR
XLOOKUP is the better choice in Excel for Microsoft 365, 2021 and 2024: it defaults to an exact match, looks left or right, survives inserted columns and handles “not found” results on its own. Use VLOOKUP only when a file must work in Excel 2016 or 2019. Our one-day Formulas, Functions & PivotTables course covers XLOOKUP hands-on for $345.
Pro Tip
Whenever you still write a VLOOKUP, type FALSE as the fourth argument. If you leave it blank, Excel uses approximate match and can return the wrong row with no error message at all.
XLOOKUP vs VLOOKUP: The Difference Between VLOOKUP And XLOOKUP
Both functions find a value in one place and return a related value from another, such as looking up an employee ID to return a salary or a product code to return a price. VLOOKUP has done that job in Excel for decades, and it still sits in millions of business workbooks. XLOOKUP arrived in Excel for Microsoft 365 and is now included in Excel 2021 and Excel 2024. It was designed to fix the specific problems that make VLOOKUP formulas break or return wrong answers.
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match type | Approximate, unless you enter FALSE | Exact |
| Return a value to the left | No | Yes |
| How the return column is chosen | A column number you type, such as 3 | A range you select, such as D2:D101 |
| Inserting a column in the table | Can break the result | References adjust automatically |
| Value not found | #N/A, needs IFERROR or IFNA | Built-in if_not_found argument |
| Search last to first | No | Yes, with search_mode -1 |
| Horizontal lookups | Needs HLOOKUP | Same function works both ways |
| Return several columns at once | No, one formula per column | Yes, results spill across |
| Excel versions | Every version | Microsoft 365, 2021, 2024, web and mobile |
The short list of real-world consequences is what matters at work. A VLOOKUP that someone forgot to set to FALSE can return a plausible but incorrect salary or price, and nobody notices until a report is questioned. A VLOOKUP pointing at column 4 returns the wrong field the day a colleague inserts a new column into the source table. XLOOKUP removes both risks by default.
Want To Practice XLOOKUP With An Instructor?
Our one-day Excel 365 Formulas, Functions & PivotTables course covers XLOOKUP, XMATCH, FILTER and dynamic arrays in 8 hours of live instruction.
How To Use VLOOKUP: Syntax And Example
Every example uses the same simple employee table. Column A holds the employee name, column B the department, column C the employee ID and column D the annual salary, with data in rows 2 to 101. The ID you want to look up is typed into cell G2. VLOOKUP searches the first column of the range you give it, then counts across to the column number you specify.
VLOOKUP Syntax And Example
- Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Example: =VLOOKUP(G2, C2:D101, 2, FALSE) returns the salary for the ID in G2
- C2:D101 starts at the ID column, because VLOOKUP always searches the first column of the range
- 2 means “return the second column of that range,” which is column D
- FALSE forces an exact match; leaving it blank means approximate match
The limits show up quickly. To return the employee name from column A, VLOOKUP cannot help, because the name sits to the left of the ID. You would need to rearrange the source data or switch to INDEX and MATCH. If the ID does not exist, the cell shows #N/A, so most reports wrap the formula in IFERROR to display something friendlier.
How To Use XLOOKUP: Syntax, Match Mode And Example
XLOOKUP asks for the column to search and the column to return as two separate ranges. That single change is why it can look left, why inserted columns do not break it, and why the formula is easier to read six months later. The optional arguments then control what happens when nothing is found, how matches are made and which direction Excel searches.
XLOOKUP Syntax And Examples
- Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- Salary: =XLOOKUP(G2, C2:C101, D2:D101, “ID not found”)
- Name, to the left of the ID: =XLOOKUP(G2, C2:C101, A2:A101)
- Name and department together: =XLOOKUP(G2, C2:C101, A2:B101), which spills into two cells
| Argument | Options | When To Use It |
|---|---|---|
| match_mode 0 (default) | Exact match | IDs, codes, names, invoice numbers |
| match_mode -1 | Exact match or next smaller | Commission tiers, tax brackets, shipping bands |
| match_mode 1 | Exact match or next larger | Finding the next available size or price break |
| match_mode 2 | Wildcard match with * and ? | Partial customer or product names |
| search_mode -1 | Search last to first | Most recent order, latest status or last price paid |
Unlike VLOOKUP’s approximate mode, XLOOKUP’s -1 and 1 match modes do not require the lookup column to be sorted. That makes tier tables and price bands far safer to maintain, because adding a row out of order no longer produces a wrong result. It also means the finance team can append a new commission tier at the bottom of the table without re-sorting anything. Those small differences add up across a workbook that dozens of people update every month.
XLOOKUP Examples, Including XLOOKUP With Multiple Criteria
These are the lookups our Excel instructors see most often in finance, HR, operations and sales teams. Each one either takes several steps with VLOOKUP or cannot be done with it at all. Try them on a copy of one of your own reports, and replace the ranges with your own columns.
Five XLOOKUP Formulas Worth Keeping
- Latest order for a customer: =XLOOKUP(G2, B2:B500, E2:E500, “No orders”, 0, -1)
- Commission rate from a tier table: =XLOOKUP(G2, J2:J6, K2:K6, , -1)
- Two conditions, region and product: =XLOOKUP(1, (A2:A500=G2)*(B2:B500=H2), D2:D500)
- Partial product name: =XLOOKUP(“*”&G2&”*”, A2:A500, C2:C500, “No match”, 2)
- Monthly value from a row of month headers: =XLOOKUP(G2, B1:M1, B5:M5)
The two-condition formula works because each comparison returns TRUE or FALSE for every row, and multiplying them gives 1 only where both conditions are true. XLOOKUP then finds the first 1. With VLOOKUP, the same task usually means building a helper column that joins region and product into one key, which adds another place for a report to go wrong.
From Our Community
“Our team attended the Microsoft Excel and Power BI training through Knowledge Source Inc., and it was worth every penny. The classes were hands-on, and the instructor adapted the material to match our skill levels. We left with practical knowledge that improved our productivity immediately.”
Isabella R. · Excel and Power BI Team Training
Explore Excel CoursesWhen To Use XLOOKUP vs VLOOKUP
The deciding factor is usually who opens the file, not which function you prefer. XLOOKUP is not available in Excel 2016 or Excel 2019, and a workbook that uses it will show errors on those versions. If every colleague, client and auditor who receives the workbook uses Microsoft 365 or Excel 2021 or later, choose XLOOKUP for new work. If even one regular recipient is on an older version, VLOOKUP or INDEX and MATCH is the safer choice for that file.
Choose XLOOKUP When
- Everyone using the file has Microsoft 365, Excel 2021 or Excel 2024
- You need a value to the left of the lookup column
- The source table changes shape as people add columns
- You want a clear message instead of #N/A without wrapping the formula
- You need the most recent match, a tier lookup or several columns at once
Keep VLOOKUP When
- The workbook is shared with people on Excel 2016 or Excel 2019
- You are maintaining an established template that works and is locked down
- The lookup column is already first and the table structure never changes
Many teams are in a mixed position during upgrades. A practical rule is to write XLOOKUP in new analysis files that stay inside the team, and leave VLOOKUP in shared templates until everyone has moved to a current version. Our Excel Level II Intermediate course still teaches VLOOKUP exact and closest match for exactly this reason.
Excel Lookup Functions: How To Convert VLOOKUP To XLOOKUP
Converting an existing formula takes less than a minute once you know which part of VLOOKUP maps to which part of XLOOKUP. Test each conversion beside the original before deleting anything, so you can compare results row by row. When a whole department needs to update shared templates at once, our on-site training can work through your own files as part of the class.
Step 1: Identify The Lookup Column
In =VLOOKUP(G2, C2:D101, 2, FALSE), the lookup column is the first column of the table range, C2:C101. That becomes XLOOKUP’s lookup_array.
Step 2: Turn The Column Number Into A Range
Count across the table range to the column number. Column 2 of C2:D101 is D2:D101, which becomes the return_array.
Step 3: Replace IFERROR With if_not_found
If the old formula was wrapped in IFERROR, move that message into the fourth argument: =XLOOKUP(G2, C2:C101, D2:D101, “ID not found”). Drop the FALSE, since exact match is already the default.
One caution applies if the original VLOOKUP used TRUE or left the last argument blank on purpose, for example to find a tax bracket. In that case, add match_mode -1 to the XLOOKUP so it keeps returning the next smaller value instead of #N/A. Compare a handful of boundary values, such as an income exactly on a bracket limit, to confirm both formulas agree.
XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use?
Before XLOOKUP existed, experienced analysts combined INDEX and MATCH to get around VLOOKUP’s limits. The combination looks left, ignores inserted columns and works in every version of Excel, which is why it still appears in many financial models. It is harder for new users to read, though, and XLOOKUP now does the same job in one function. Our four-hour Logicals and Lookups course compares INDEX and MATCH with VLOOKUP side by side and carries 4 CPE hours. It is one of several short, focused classes in our Microsoft® Office hands-on courses.
| Function | Best For | Trade-Off |
|---|---|---|
| VLOOKUP | Simple lookups in files shared with older Excel versions | Right-only, fragile column numbers, approximate by default |
| INDEX and MATCH | Flexible lookups that must work in every version | Two nested functions, harder to audit |
| XLOOKUP | Most new work in Microsoft 365, 2021 and 2024 | Not available in Excel 2016 or 2019 |
Accredited Training Since 1993
Knowledge Source Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
Live virtual CPE classes include at least three engagement prompts per CPE hour (50 minutes). Participants must respond to all three to receive credit for that hour.
VLOOKUP Not Working? Common XLOOKUP And VLOOKUP Mistakes
Most lookup errors in business reports come from the data rather than the formula. A lookup value stored as text will not match the same number stored as a number, and a trailing space in a product code is enough to return #N/A. Checking these first saves time before you rewrite a formula that is actually correct.
Lookup Checklist Before You Share A Report
- Numbers stored as text: look for the green triangle and convert the column
- Extra spaces: clean the source with TRIM or Power Query
- Different range sizes: XLOOKUP’s lookup_array and return_array must be the same height
- Missing $ signs: lock ranges with absolute references before copying a formula down
- Blank VLOOKUP last argument: add FALSE unless you truly need approximate match
- Duplicates: both functions return the first match, so check whether the key is unique
- Spill errors: clear any cells in the way when XLOOKUP returns several columns
Formatting the source data as an Excel table helps with several of these at once. Table references such as Employees[ID] grow automatically as rows are added, and they make formulas readable for whoever inherits the workbook. For professionals who want the full picture from basics to PivotTables, our two-day Excel crash course covers formulas, tables and data tools in 16 hours.
Related Courses From Knowledge Source Inc.
Book Your Place In Modern Excel Formulas Training
Microsoft Excel 365/2026: Formulas, Functions & PivotTables is a one-day, live instructor-led class from 8:30 a.m. to 4:15 p.m., covering XLOOKUP, dynamic arrays, PivotTables, Power Query basics and Copilot in Excel. Tuition is $345 for 8 hours of training, delivered virtually or on-site for groups. Tell us your preferred dates and we will confirm the next class.