Introduction

For many years, the preferred methods for solving complicated lookup issues have been INDEX and MATCH. INDEX and MATCH, in contrast to VLOOKUP, are based on numerical positions: the INDEX function retrieves a value at that position, while the MATCH function finds the position of a value. This method increases the versatility of INDEX and MATCH at the expense of additional configuration.

However, Excel users now have access to a potent new lookup option thanks to the 2019 release of XLOOKUP. XLOOKUP is capable of much more than VLOOKUP (see this article for a thorough comparison). But what about INDEX and MATCH? Can XLOOKUP do everything that INDEX and MATCH can do? Let's examine how these two choices compare to one another.

INDEX AND MATCH: The Classic

A traditional way to do lookups in Excel is to combine INDEX and MATCH. It offers a very adaptable and customizable solution for a variety of lookup problems, even though it necessitates two distinct functions. The syntax for MATCH and INDEX is as follows:

To put it briefly, the INDEX function is used to retrieve a value at the numeric position of a match in a set of data, and the MATCH function is used to find that position. An example of INDEX and MATCH set up to locate an email address based on ID can be seen in the screen below. Cell H6's formula is:

In the MATCH function, match_type is set to 0 to require an exact match, and lookup_array is B6:B14, which contains IDs. Email addresses are contained in the (return) array, which is provided as E6:E14 inside the INDEX function. The INDEX function receives the numeric position of ID 869 (7) from MATCH, and as a result, INDEX returns the value at that position.

MATCH vs XMATCH

We must first discuss the XMATCH function before we begin comparing XLOOKUP to INDEX and MATCH. Released concurrently with XLOOKUP, XMATCH is an improved substitute for the MATCH function. XMATCH does a lookup and returns a numerical position, just like the MATCH function. Similar to MATCH, XMATCH supports both exact and approximate matches, allows wildcards (*?) for partial matches, and can perform lookups in vertical or horizontal ranges.

There are 5 key differences between XMATCH and MATCH:

1. Whereas MATCH defaults to an approximate match, XMATCH defaults to an exact match.
2. The next bigger or smaller item can be found using XMATCH.
3. Reverse searches, or searches from last to first, are possible with XMATCH.
4. When doing an approximate match, XMATCH does not require values to be sorted.
5. A binary search, which is specially designed for speed, can be carried out by XMATCH.

In summary, XMATCH works like MATCH, but it is more flexible and powerful.

XLOOKUP and XMATCH were released simultaneously. You have XMATCH if you have XLOOKUP. Therefore, comparing XLOOKUP to INDEX and MATCH without taking XMATCH into account is illogical. For the purposes of this article, "INDEX and MATCH" may also refer to "INDEX and XMATCH" depending on the specifications.

INDEX and MATCH Pros

Compatibility: The fundamental INDEX and MATCH combination has long been the go-to solution for challenging lookup issues and is compatible with all Excel versions. This method is used in millions and millions of spreadsheets worldwide.

Flexibility: The combination of INDEX and MATCH is incredibly versatile and can resolve almost any Excel lookup issue, including lookups in vertical or horizontal ranges, exact and approximate match lookups, lookups using wildcards, and more.

Numerical index: INDEX returns a value at the numerical position that MATCH returns. This value is easily manipulable since MATCH returns a numerical index. For instance, based on additional data in a worksheet, some sophisticated INDEX and MATCH formulas make quick, straightforward changes to row or column index values 

Horizontal or vertical: INDEX and MATCH are equally effective with both types of ranges.