XLOOKUP vs INDEX MATCH in Excel: which is better and faster?

INDEX MATCH formula – the classic approach

The INDEX and MATCH combination has stood the test of time as a classic method for various lookups in Excel. The syntax is as follows:

Here, the MATCH function determines the relative position of the lookup value in the lookup column, while INDEX retrieves the corresponding value from the return column at that position. The third argument of MATCH (match_type) set to 0 ensures an exact match, which is typically the desired behavior.

While this formula involves two separate functions, it provides great flexibility in handling various lookup scenarios.

For more information, see INDEX & MATCH formula in Excel.

INDEX XMATCH formula – improved version

A modernized alternative to MATCH, the XMATCH function was introduced concurrently with XLOOKUP and is only available in Excel 365 and Excel 2021. Here are the key improvements:

• Unlike MATCH, XMATCH defaults to an exact match, which aligns with most use cases.
• XMATCH does not require sorting the lookup array for an approximate match.
• In contrast to its predecessor, XMATCH can search in reverse order and perform a binary search optimized for speed. This behavior is controlled by the optional search_mode argument.

The syntax looks like this:

To put it briefly, INDEX XMATCH is a more advanced way of doing the same things as INDEX MATCH, which allows you to handle more complex and diverse lookup problems. In this tutorial, we will consider these formulas as essentially interchangeable, recognizing that each has its strengths and may be preferred in different contexts.

XLOOKUP function - the modern solution

The XLOOKUP function, often called the "killer" of VLOOKUP, represents a modern approach to lookup operations. It is only available for Office 365 subscribers and Excel 2021 users, offering a powerful alternative. The syntax for XLOOKUP is as follows:

Overcoming many limitations of VLOOKUP, XLOOKUP can handle a wide variety of scenarios, including left lookups, horizontal and vertical lookups, two-way lookups, and more. Its versatility makes it a go-to choice for users who want a comprehensive and effective lookup technique in the latest Excel versions.

For more details, see Excel XLOOKUP with formula examples.

Basic formula

To illustrate the transition, let's consider a typical example where you have a list of student names in one column and their corresponding exam scores in another. In this scenario, you aim to retrieve the score for a particular student, say, in cell E4.

The INDEX MATCH formula for this task would look like this:

=INDEX(B4:B25, MATCH(E4, A4:A25, 0))

XLOOKUP provides an equally straightforward solution - just supply the required lookup value, lookup range and return range:

=XLOOKUP(E4, A4:A25, B4:B25)

As you can see in the screenshot, both formulas achieve the same result:

Left lookup

As you probably know, one of the greatest limitations of VLOOKUP is its inability to return values from columns located to the left of the lookup column.

Both XLOOKUP and INDEX MATCH excel in handling left lookup tasks, providing a solution that VLOOKUP lacks. Here's how you can perform a left lookup with each method:

=INDEX(A4:A25, MATCH(E4, B4:B25, 0))

=XLOOKUP(E4, B4:B25, A4:A25)

Both formulas search for the specified value (E4) in column B and return the corresponding value from column A.

Horizontal lookup

In older versions of Excel, the HLOOKUP function was specifically designed for searching horizontally in rows. The good news is that both XLOOKUP and INDEX MATCH are equally adept at performing horizontal lookups. To achieve this, you simply need to orient your lookup and return arrays in rows instead of columns.

=INDEX(B4:J4, MATCH(B8, B3:J3, 0))

=XLOOKUP(B8, B3:J3, B4:J4)

Handling entire columns and rows

When setting up real worksheets, it's common for users to refer to entire columns or rows in their formulas to account for potential data additions in the future. Both XLOOKUP and INDEX MATCH readily support this approach. For example:

=INDEX(A:A, MATCH(E4, B:B, 0))

=XLOOKUP(E4, B:B, A:A)

A word of warning for those who want to use this method: referencing whole columns or rows forces Excel to process very large arrays. This can affect performance and slow down your worksheets. You should always think about how this might impact calculation speed and try to limit your data ranges if performance is an issue.

Search in reverse order

In scenarios where you need to search your data from the end to the beginning (bottom-to-top for vertical lookups or right-to-left for horizontal lookups) either XLOOKUP or INDEX XMATCH can help. It's worth noting that the MATCH function lacks this feature and cannot be applied in such situations.

To search last to first with XLOOKUP, you set the optional search_mode argument to -1:

=XLOOKUP(F4, B4:B24, C4:C24, , ,-1)

Similarly, for a reverse search using INDEX XMATCH:

=INDEX(C4:C24, XMATCH(F4, B4:B24, ,-1))

Two-way lookup

A two-way lookup, also known as “matrix” or “2-dimensional lookup”, is a technique that finds a value at the intersection of a specific row and column. The INDEX MATCH combo is well suited for this kind of lookup as INDEX can target both the row and column numbers, and you can employ two different MATCH functions for this purpose.

For instance, to get the score of a certain student in a particular subject, you can use this formula:

=INDEX(B4:D25, MATCH(G4, A4:A25, 0), MATCH(G5, B3:D3, 0))

Now, when it comes to a 2D lookup with XLOOKUP, it gets a bit more complex because it's primarily designed for a single lookup array. To handle both columns and rows simultaneously, you nest one XLOOKUP function inside another. The inner function looks for its value and returns a column or row of related data. This array then becomes the return_array for the outer function. Here's how it looks:

=XLOOKUP(G4, A4:A25, XLOOKUP(G5, B3:D3, B4:D25))

For more formula examples, please see How to do two-way lookup in Excel.

Multiple criteria lookup

The structure of both formulas allows for a fairly easy application of multiple criteria. The general approach is as follows: first, you test each condition individually, generating temporary lookup arrays of TRUE and FALSE values. Next, multiply the elements of these arrays to create a unified lookup array, where 1 represents a match and 0 represents no match. And then, you set the lookup_value to 1 to retrieve the first found match.

For example, to pull the date of a given exam (criteria1 – F5) for a particular student (criteria2 – F4), the formulas would go as follows:

=INDEX(A4:A24, MATCH(1, (B4:B24=F4) * (C4:C24=F5), 0))

=XLOOKUP(1, (B4:B24 = F4) * (C4:C24 = F5), A4:A24)

In Excel 2019 and earlier versions that do not support dynamic arrays, you need to enter INDEX MATCH as an array formula by pressing the Ctrl + Shift + Enter keys together. Otherwise, it will not work correctly. The non-array INDEX MATCH with multiple conditions formula also exists but has a slightly more complex syntax.

For in-depth understanding and practical examples, please refer to the following tutorials:

• NDEX MATCH with multiple criteria
• XLOOKUP with multiple criteria

Return values from multiple columns

With XLOOKUP, getting information from multiple neighboring columns is a breeze, as long as those columns are adjacent to each other. Just choose the range that includes all the columns you need and serve it as the return array.

For instance, this is how you can retrieve the exam results from columns B, C, and D for a certain student in cell G4:

=XLOOKUP(G4, A4:A25, B4:D25)

The INDEX MATCH duo goes a bit further by pulling data from any columns, even those that aren't next to each other:

=INDEX(B4:D25, MATCH(G4, A4:A25, 0), MATCH(F8:G8, B3:D3, 0))

In simple terms, this formula utilizes a 2-dimensional INDEX MATCH approach. The first MATCH finds the right row number for the student name in G4, and the second MATCH figures out the column numbers for the subjects in F8:G8.

Note that in Excel 2019 and older, this only works as an CSE array formula (requires pressing Ctrl + Shift + Enter to calculate correctly). To return values from non-adjacent columns with XLOOKUP, you can use it together with the FILTER function. A horizontal array like {1,0,1} added to the include argument of FILTER determines which columns to return (1s) and which to filter out (0s).

=XLOOKUP(G4, A4:A25, FILTER(B4:D25, {1,0,1}))

Handling missing values

When a specified value is not found, the basic form of the INDEX and MATCH formula returns an #N/A error. However, you can mitigate this issue by wrapping the formula in the IFERROR or IFNA function and providing a custom message.

For instance, to replace an #N/A error with your own text, you can use INDEX MATCH together with IFNA:

=IFNA(INDEX(B4:B25, MATCH(E4, A4:A25, 0)), "Not found")

Alternatively, include your message in the 4th argument of XLOOKUP like this:

=XLOOKUP(E4, A4:A25, B4:B25, "Not found")

Both formulas achieve the same result, allowing you to handle missing values gracefully.