VLOOKUP vs XLOOKUP: Which to Use

Formulas & functions · Updated November 22, 2026

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_found argument; 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_found argument.
  • 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.