Introduction
For decades,INDEX and MATCHhave been the go-to solution for handling complex lookup problems.
This approach makes INDEX and MATCH highly versatile, at the cost of more configuration.
However, with the introduction ofXLOOKUPin 2019, Excel users have a powerful new lookup option available.
But what about INDEX and MATCH?
Can XLOOKUP do everything that INDEX and MATCH can do?
Let’s take a look at how these two options stack up against each other.
The screen below shows an example of INDEX and MATCH configured to find an email address based on ID.
For a full explanation of INDEX and MATCH step-by-step see:How to use INDEX and MATCH.
XMATCH is an upgraded replacement for the MATCH function, released at the same time as XLOOKUP.
Like the MATCH function, XMATCH performs a lookup and returns a numeric position.
XMATCH was released at the same time as XLOOKUP.
If you have XLOOKUP, you also have XMATCH.
As a result, it doesn’t make sense to compare XLOOKUP to INDEX and MATCH without including XMATCH.
There are millions and millions of spreadsheets in the world that use this approach.
Numeric index:MATCH returns a numeric position, and INDEX returns a value at that position.
Because MATCH returns a numeric index, this value can be easily manipulated.
Vertical or horizontal:INDEX and MATCH work equally well with vertical or horizontal ranges.
Multiple criteria: The behavior of INDEX and MATCH makes it relatively straightforward to apply multiple criteria.
The standard approach is to create alookup_arraywithBoolean algebra, then set thelookup_valuein MATCH to 1 (See example).
This isnota problem with INDEX + XMATCH, since XMATCH returns anexact matchby default.
It is a flexible and versatile function that can be used in a wide variety of situations.
For more XLOOKUP examples and videossee this page.
Safe defaults:unlike INDEX + MATCH, XLOOKUPdefaults to an exact match.
This is a much safer default because a user must explicitly enable approximate match behavior when needed.
Note that INDEX + XMATCHwilldefault to an exact match because this is the default behavior for XMATCH.
Vertical or horizontal:Like INDEX and MATCH, XLOOKUP can use a vertical or horizontal lookup array.
Entire rows and columns: XLOOKUP can easily returnentire rowsorentire columns.
In both cases,data does not need to be sorted.
With INDEX and MATCH, you must add another function like IFERROR or IFNA to handle errors.
Multiple criteria: The structure of XLOOKUP makes it relatively straightforward to apply multiple criteria.
XLOOKUP cons
Limited availability:XLOOKUP is only available in the latest versions of Excel.
This means XLOOKUP will not work if a worksheet is opened in an older version of Excel.
Two-way lookups are more complex:Compared to INDEX and MATCH, a two-way lookup (i.e.
looking up both a row and column in the same formula) with XLOOKUP is more complicated.
Feature comparison
The table below summarizes the key differences mentioned above.
- Requires XMATCH, available in Excel 2021+.
Summary
XLOOKUP and INDEX and MATCH are both flexible and powerful lookup solutions in Excel.
However, when we compare XLOOKUP to INDEX + XMATCH, the contest is much closer.
XLOOKUP has an edge with built-in error handling and a friendly learning curve.
In addition, many users will likely find an INDEX and MATCH formula more intuitive for two-way lookups.
With the above in mind, here are a few general recommendations and thoughts:
