XLOOKUP vs INDEX MATCH: Which Should You Use?

Formulas & functions · Updated November 22, 2026

XLOOKUP is the modern replacement for INDEX MATCH in Excel, offering a simpler formula with built-in error handling and the ability to search left or right. However, INDEX MATCH still works in all Excel versions and is useful for certain advanced lookups. This article explains both functions, how they compare, and when to choose one over the other.

Background: Why INDEX MATCH Was the Standard

Before XLOOKUP, VLOOKUP was the most common lookup function, but it had a major limitation: it could only return values to the right of the lookup column. To look up values to the left, users combined INDEX and MATCH. INDEX returns a value from a specific row and column, while MATCH finds the position of a lookup value in a range. Together, they could perform flexible lookups in any direction.

For example, to find a product price when the product name is in column B and the price is in column A, you would use:

=INDEX(A:A, MATCH("Widget", B:B, 0))

This formula finds the row where “Widget” appears in column B and then returns the value from column A in that same row. This was the go-to solution for many years because it worked in every Excel version from 2007 onward.

How XLOOKUP Works

XLOOKUP was introduced in Excel 2021 and Microsoft 365. It takes up to six arguments, but only three are required:

  • lookup_value – the value you are searching for
  • lookup_array – the range where you search
  • return_array – the range from which to return a value

The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

For example, with product names in A2:A20 and prices in B2:B20, to find the price of “Widget”:

=XLOOKUP("Widget", A2:A20, B2:B20)

This returns the price from column B where column A matches “Widget”. XLOOKUP defaults to an exact match, and if no match is found, it returns a #N/A error. You can add a fourth argument to specify what to return if not found, like “Not found”.

Key Differences Between XLOOKUP and INDEX MATCH

The main differences are:

  • Simplicity: XLOOKUP uses one function; INDEX MATCH requires two nested functions.
  • Direction: XLOOKUP can return values to the left or right of the lookup column; INDEX MATCH also can, but with more complex formulas.
  • Error handling: XLOOKUP has a built-in “if not found” argument; INDEX MATCH requires wrapping with IFERROR.
  • Speed: XLOOKUP is generally faster on large datasets because it uses a more efficient search algorithm.
  • Compatibility: INDEX MATCH works in all Excel versions; XLOOKUP requires Excel 2021 or Microsoft 365.

For example, to find a value and return a custom message if not found, XLOOKUP is simpler:

=XLOOKUP("Widget", A2:A20, B2:B20, "Not found")

With INDEX MATCH, you would need:

=IFERROR(INDEX(B2:B20, MATCH("Widget", A2:A20, 0)), "Not found")

When to Use XLOOKUP

Use XLOOKUP when you have Excel 2021 or Microsoft 365 and you want a cleaner, more readable formula. It is ideal for:

  • Most everyday lookups, especially when you need to return values from columns to the left.
  • When you want to avoid wrapping formulas with IFERROR.
  • When you need to perform a reverse search (find the last occurrence) using the optional sixth argument.

For instance, to find the last sale price for a product, you can use:

=XLOOKUP("Widget", A2:A100, B2:B100, "Not found", 0, -1)

The -1 makes the search go from bottom to top, returning the last match.

When to Use INDEX MATCH

INDEX MATCH still has its place, particularly when:

  • You are sharing workbooks with users who have older Excel versions (pre-2021).
  • You need to perform a two-way lookup (looking up both row and column headers).
  • You are working with very large datasets and want to avoid any potential performance issues (though XLOOKUP is generally faster, INDEX MATCH can be more memory-efficient in some cases).

For a two-way lookup, you combine INDEX with two MATCH functions. For example, with row headers in A2:A10, column headers in B1:F1, and data in B2:F10, to find the value at the intersection of “Product” and “Q2”:

=INDEX(B2:F10, MATCH("Product", A2:A10, 0), MATCH("Q2", B1:F1, 0))

This returns the value from the row where “Product” appears and the column where “Q2” appears.

Common Problems and How to Avoid Them

Both functions can throw errors if not used correctly. Common issues include:

  • #N/A error: This occurs when the lookup value is not found. With XLOOKUP, you can add a fourth argument to handle this. With INDEX MATCH, wrap with IFERROR.
  • Mismatched ranges: The lookup array and return array must have the same number of rows (for XLOOKUP) or the INDEX range must align with the MATCH range. For example, if you use =XLOOKUP(A2, B2:B20, C2:C20), both B and C ranges must start and end at the same row.
  • Approximate match issues: If you omit the match mode argument in XLOOKUP, it defaults to exact match. For INDEX MATCH, you must specify 0 for exact match, or you may get incorrect results.
  • Data type mismatches: If your lookup value is stored as text but the lookup array contains numbers, you may get no match. Ensure consistent data types.

To debug, use the Evaluate Formula tool (Formulas tab > Formula Auditing > Evaluate Formula) to step through the calculation.

Performance and Compatibility

XLOOKUP is generally faster than INDEX MATCH on large datasets because it uses a binary search algorithm when you specify an exact match. However, INDEX MATCH can be more memory-efficient because it only references the specific columns you need, whereas XLOOKUP references entire arrays. In practice, for most spreadsheets, the difference is negligible.

Compatibility is the biggest factor. XLOOKUP is only available in Excel 2021 and Microsoft 365. If you are using Excel 2019 or earlier, you must use INDEX MATCH or VLOOKUP. On Mac, XLOOKUP works the same as on Windows, but the keyboard shortcut for entering formulas is Cmd+Enter instead of Ctrl+Enter.

Frequently Asked Questions

Is XLOOKUP always better than INDEX MATCH?

Not always. XLOOKUP is easier to write and read, but if you need to share your workbook with people using older Excel versions, INDEX MATCH is the safer choice. Also, for two-way lookups, INDEX MATCH is more straightforward because it directly references both row and column headers.

Can XLOOKUP replace VLOOKUP?

Yes, XLOOKUP can replace VLOOKUP in almost all cases. It can look up to the left, handle errors, and use exact match by default. The only limitation is version compatibility.

How do I use XLOOKUP to return multiple columns?

You can return multiple columns by using a return array that spans multiple columns. For example, =XLOOKUP(A2, B2:B20, C2:E20) returns a three-column array. However, to display the results, you may need to use the formula in multiple cells or use it with other functions like FILTER.

Does XLOOKUP work with wildcards?

Yes, you can use wildcards like * and ? in the lookup value if you set the match mode to 2 (wildcard match). For example, =XLOOKUP("W*", A2:A20, B2:B20, "Not found", 2) finds the first product starting with “W”.

Can I use INDEX MATCH with wildcards?

Yes, you can use wildcards in the MATCH function by setting the match type to 0, but you must use the tilde (~) to escape wildcards if you want to match a literal asterisk or question mark. For example, =INDEX(B2:B20, MATCH("W*", A2:A20, 0)) works the same as XLOOKUP with wildcard match.

Which function is easier for beginners?

XLOOKUP is much easier for beginners because it has a simpler syntax and a built-in error handler. You only need to understand three arguments to get started. INDEX MATCH requires understanding how INDEX and MATCH work separately, which can be confusing at first.

Conclusion

In summary, XLOOKUP is the modern choice for most lookup tasks in Excel 2021 and Microsoft 365, offering simplicity and flexibility. INDEX MATCH remains a valuable skill for compatibility and advanced scenarios like two-way lookups. Choose based on your Excel version and the complexity of your lookup needs.

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.