VLOOKUP and XLOOKUP are both lookup functions in Excel, but XLOOKUP is the modern replacement that solves most of VLOOKUP’s limitations. If you have Excel 2021 or Microsoft 365, use XLOOKUP; if you’re on an older version, VLOOKUP is still your only option unless you use INDEX/MATCH.
What Is VLOOKUP?
VLOOKUP (Vertical Lookup) has been in Excel since the 1980s. It searches for a value in the first column of a table and returns a value in the same row from a column you specify. Its syntax is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
For example, with employee IDs in A2:A10 and names in B2:B10, to find the name for ID 104:
=VLOOKUP(104, A2:B10, 2, FALSE)
This returns the name from column B. The fourth argument, FALSE, forces an exact match; TRUE (or omitted) allows approximate match, which requires the first column to be sorted.
What Is XLOOKUP?
XLOOKUP, introduced in 2020 with Microsoft 365, searches a range and returns a value from any column to the left or right. Its syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Using the same example, with IDs in A2:A10 and names in B2:B10:
=XLOOKUP(104, A2:A10, B2:B10)
By default, XLOOKUP does an exact match. It also has optional arguments for approximate match, wildcard match, and search direction.
Key Differences Between VLOOKUP and XLOOKUP
- Lookup column position: VLOOKUP can only look in the leftmost column of the table; XLOOKUP can look in any column.
- Return column position: VLOOKUP can only return values to the right of the lookup column; XLOOKUP can return from any column, left or right.
- Default match type: VLOOKUP defaults to approximate match (which can cause errors if not sorted); XLOOKUP defaults to exact match.
- Error handling: XLOOKUP has a built-in
if_not_foundargument; VLOOKUP returns #N/A, which you must wrap in IFERROR. - Column insertions: Adding a column to the table breaks VLOOKUP’s column index number; XLOOKUP uses a return range, so it adjusts automatically.
- Speed: XLOOKUP is generally faster, especially on large datasets, because it does not scan the entire table.
How to Choose Between VLOOKUP and XLOOKUP
Your choice depends on your Excel version and your specific task. Here’s a quick guide:
- If you have Excel 2021 or Microsoft 365, use XLOOKUP for almost everything.
- If you need to return a value to the left of the lookup column, use XLOOKUP (or INDEX/MATCH).
- If you need to handle missing values gracefully, use XLOOKUP’s
if_not_foundargument. - If you are sharing files with users on Excel 2019 or earlier, stick with VLOOKUP to avoid #NAME? errors.
Common Problems and Solutions
VLOOKUP returns #N/A: This usually means the lookup value is not found or the match type is wrong. Check for extra spaces, inconsistent data types (text vs. number), or use FALSE for exact match.
VLOOKUP returns wrong results with approximate match: If you use TRUE without sorting, you get unpredictable results. Always sort the first column ascending or use FALSE.
Column index number breaks after inserting a column: This is a classic VLOOKUP pitfall. Use XLOOKUP with a return range, or use INDEX/MATCH, to avoid this.
XLOOKUP returns #NAME? error: This means your Excel version does not support XLOOKUP. Check if you have Excel 2019 or earlier; if so, use VLOOKUP or INDEX/MATCH.
Comparison with INDEX/MATCH
INDEX/MATCH is an older alternative that also overcomes VLOOKUP’s limitations. For example, to find a name based on ID in A2:B10:
=INDEX(B2:B10, MATCH(104, A2:A10, 0))
INDEX/MATCH works in all Excel versions and is more flexible than VLOOKUP, but it is more complex to write. XLOOKUP is simpler and faster, so if you have the newer Excel, use it.
Frequently Asked Questions
Can XLOOKUP replace VLOOKUP completely? Yes, in Excel 2021 and Microsoft 365, XLOOKUP can do everything VLOOKUP does and more. It is the recommended function going forward.
Does XLOOKUP work in Excel 2019? No. XLOOKUP requires Excel 2021 or Microsoft 365. In Excel 2019, you can use VLOOKUP or INDEX/MATCH.
How do I use XLOOKUP to return multiple columns? You can use an array formula. For example, with IDs in A2:A10 and data in B2:D10, to return columns B through D for ID 104: =XLOOKUP(104, A2:A10, B2:D10) returns an array of three values. In Excel 365, this spills automatically.
Why does VLOOKUP not work when I insert a column? Because the col_index_num is a fixed number. If you insert a column before the return column, the index shifts and you get the wrong data. XLOOKUP uses a return range, so it adjusts automatically.
Is XLOOKUP faster than VLOOKUP? Generally, yes, especially on large datasets. XLOOKUP only searches the lookup array, while VLOOKUP scans the entire table range.
Can I use wildcards with XLOOKUP? Yes, set match_mode to 2, which allows wildcard characters like * and ?. For example, to find the first name starting with “J”: =XLOOKUP("J*", A2:A10, B2:B10, , 2).
Related
- Excel Template for Project Expense Tracking
- Excel Template for Paying Off Debt: Free & DIY
- Excel Template for Personal Expense Tracking
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Calculate Hours WorkedFor a shift that starts in B2, ends in C2, and has an unpaid break entered as a…
- Rank Names in Excel: Formulas and Tie RulesIf names are in A2:A20 and their scores are in B2:B20, enter =RANK.EQ(B2,$B$2:$B$20,0) in C2 and fill it…
- Excel Formula for Calculating Days Between DatesTo calculate the number of elapsed days between two dates in Excel, subtract the earlier date from the…
- How to Concatenate a Long List in Excel with TEXTJOINTo concatenate a long list in Excel into one cell, use TEXTJOIN: with your items in A2:A1000, enter…
- Sort a String in Java: Methods and ExamplesJava has no String.sort() method. To sort the characters in one string, convert it to a char[], call…
- Remove Formula in Excel: Shortcut Key and StepsTo remove a formula but keep its current result in Excel for Microsoft 365 on Windows, select the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.