XLOOKUP vs INDEX-MATCH in Excel (All Possible Comparisons)

Today I will make a comparative analysis of the XLOOKUP vs INDEX-MATCH functions in Excel. In the earlier versions of Excel, we used to use the HLOOKUP, the VLOOKUP, and the INDEX-MATCH functions to look for a specific value in a range of cells. However, with the emergence of Office 365, Excel has provided us with a new and dynamic function called the XLOOKUP function to conduct a similar operation more sophisticatedly. In this article, I will try to compare the widely used functions, the XLOOKUP and the INDEX-MATCH.

Introduction to XLOOKUP Function

The XLOOKUP function is used to find a specific value within a range of cells or an array. After that, it returns the corresponding first match. It also shows the closest or approximate match when there is no exact match.

Syntax:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])

Arguments:

• Lookup_value: It is the value that we are searching for in a specific column of the range.
• Lookup_array: It is the array in which we are searching the lookup_value. Can be both row and column.
• Return_array: It is the column from which the corresponding value of the lookup_value will be returned.

Optional Arguments:

• If_not_found: It is the value that will be returned in case the lookup_array doesn’t have the lookup_value.
• Match_mode: It is a number denoting the type of match of the lookup_value you want. This is an optional argument. It can contain four values.

1. When it is 0, XLOOKUP will search for an exact match (Default).
2. When it is 1, XLOOKUP will first search for an exact match. If an exact match is not found, it will match the next smaller value.
3. When it is -1, XLOOKUP will first search for an exact match. If an exact match is not found, it will match the next larger value.
4. When it is 2, XLOOKUP will first search for an approximate match using Wildcards (Applicable for string lookup values only).

• Search_mode: It is a number denoting the type of search operation conducted on the lookup_array. This is also optional.  It can also have four values:

1. If it is 1, XLOOKUP will search from top to bottom in the lookup_array (Default).
2. When it is -1, XLOOKUP will search from bottom to top in the
3. If it is 2, XLOOKUP will conduct a binary search in ascending order.
4. When it is -2, XLOOKUP will conduct a binary search in descending order.

Introduction to INDEX-MATCH Functions

The combination of the INDEX and MATCH functions is used to fetch a value from a given location and match it with the source range.

Syntax:

=INDEX(array,MATCH(lookup_value,lookup_array,match_type),no_of_column)

Arguments:

For the INDEX Function:

• Array: It is a range of cells from which we want to extract a value.
• MATCH(lookup_value,lookup_array,match_type): It is the row number of the range where the lookup_value matches a specific value in the lookup_array.
• No_of_column: It is the number of the column of the array from which we want to return a value corresponding to the lookup_value.

For the MATCH function:

• Lookup_value: It is the value that we are searching for.
• Lookup_array: It is the array in which we are searching for the lookup_value. It can be both a row and a column.
• Match_type: It is an integer denoting the type of match we are looking for. This is optional.

1. When it is -1, MATCH will first look for an exact match. In case an exact match is not found, it will look for the next larger value (Default) (opposite to XLOOKUP).

But the condition is that the lookup_array must be sorted in ascending order. Otherwise, it will show an error.

2. When it is 1, MATCH will also first look for an exact match. In case an exact match is not found, it will look for the next smaller value (opposite to XLOOKUP).

But the condition is that the lookup_array must be sorted in descending order this time. Otherwise, it will show an error.

3. When it is 0, MATCH will search for an exact match.

XLOOKUP vs INDEX-MATCH in Excel: 7 Comparisons

Now we have broken down the formula, let’s discuss some similarities and dissimilarities between the two functions. Before going to the main discussions, I am showing the major points in a table for your convenience.

1. XLOOKUP and INDEX-MATCH to Lookup Value in Column

There is a similarity between the two functions in this aspect. For the XLOOKUP and the INDEX-MATCH, the lookup_array can be a column for both functions. Here we are looking for the exact Marks in Physics of the student name in Cell F5, Jennifer Marlo. We wanted to search from top to bottom in the Student Name column and return “Not Found” in case no match was found.

• For XLOOKUP, apply the formula in Cell G5.

• For INDEX-MATCH, use this formula in Cell G5.

2. XLOOKUP and INDEX-MATCH to Lookup Value in Row

There is also a similarity between the two functions in this aspect. For the XLOOKUP and the INDEX-MATCH, the lookup_array can also be a row for both functions. For illustration, we have a new dataset with the IDs, Names, and Marks in Physics and Grades.

Let’s consider for a moment that this is a very wide data set, and we don’t know what the number of the Grade column is. Then, to find out a particular student’s grade, we have to use the Heading row (B4:E4) as the lookup_array and the word “Grade” as the lookup_value. We can accomplish it using both the XLOOKUP and the INDEX-MATCH.

• To find out the grade of the 3rd student, the XLOOKUP formula will be like this in Cell G5.

• For this case, the INDEX-MATCH formula will be:

3. XLOOKUP and INDEX-MATCH When No Match Being Found

The two functions are dissimilar in this aspect. If the lookup_value does not match any value in the lookup_array, you can set a fixed value to be returned in XLOOKUP. To do that, you have to set that value in the if_not_found argument. On the other hand, there is no such option in INDEX-MATCH. It will return an error. You have to use the IFERROR function outside to handle the error. In the given data set, we will find out the Student Name with the ID 100.

• For this, use the following XLOOKUP formula in Cell G5.

• On the other hand, apply this INDEX-MATCH formula.

• As it returns an error, you have to use an IFERROR function outside to handle this error.

4. XLOOKUP and INDEX-MATCH in Case of Approximate Matches

There is a partial similarity between the two functions in this aspect. In the XLOOKUP function, if the lookup_value does not match any value in the lookup_array, you can modify the formula to return the next smaller or the next larger value. Set the argument match_type to -1 if you want the next smaller value and set it to 1 if you want the next larger value.

For example, we will find out the student with a mark of 50 or the next larger mark.

• To find the value, apply this XLOOKUP formula.

• As you can see, there is no student with a mark of 50. That’s why it is showing the one immediately after 50, 51 by Desmond Hayes.

There is the same option in the INDEX-MATCH formula. But the shortcoming is that you have to sort the lookup_array in descending order if you want the next larger value. Otherwise, it will return an error. And to get the next smaller value, you have to sort in ascending order.

• First of all, insert this formula in Cell G5.

• As a result, you will see that the result is showing an #N/A error.
• Therefore, sort the Cell range D5:D16 in ascending order and you will get the correct value.

5. XLOOKUP and INDEX-MATCH in Case of Matching Wildcards

There is a similarity between the two functions in this aspect. The XLOOKUP and the INDEX-MATCH, both support Wildcards. Here, we will find out any student with “Marlo” as the second name. Let’s follow the steps below to see XLOOKUP vs INDEX MATCH multiple criteria with wildcard comparison.

• Firstly, apply this XLOOKUP formula in Cell G5 to get the output.

• On the other hand, the INDEX-MATCH formula to accomplish the same task will be like this.

6. XLOOKUP and INDEX-MATCH When Multiple Values Match Lookup Value

This example shows XLOOKUP vs INDEX-MATCH when multiple values match the lookup value. There is also a partial similarity between the two functions in this regard. The XLOOKUP and the INDEX-MATCH both return only one value in case multiple values in the lookup_array match the lookup_value. But in the XLOOKUP function, you can modify the search to get either the first or the last match. To get the first value that matches, set the search_type argument to 1. And to get the last value that matches, set the search_type argument to -1. But in INDEX-MATCH you have no choice. You will get only the first value that matches.

• To get the first student who got 100, you can use this XLOOKUP formula in Cell G5.

• Along with it, you will get the last student with 100 using this XLOOKUP formula.

• On the contrary, you will get only the first value that matches with this INDEX-MATCH formula.

7. XLOOKUP and INDEX-MATCH in Case of Multiple Lookup Values

In this example, we will demonstrate XLOOKUP vs INDEX-MATCH in case of multiple lookup values. There is a similarity between the two functions in this regard. Both allow multiple lookup_values (Array Formula).

• For the XLOOKUP function, the following formula will work.

• Then, for INDEX-MATCH, the following function will also work.

Advantages & Disadvantages of XLOOKUP Function

There are certain advantages and disadvantages of using the XLOOKUP function. Let’s see them in brief.

Advantages

• Set up a default value for no matching cases.
• Can search for approximate matches without sorting the lookup_array.
• Have access to search from both the first cell and the last cell of the lookup_array.

Disadvantages

• Works slower than the INDEX-MATCH function.
• Available in Microsoft 365 and 2021 only.

Advantages & Disadvantages of INDEX-MATCH Functions

The INDEX-MATCH functions also got some of the following pros and cons.

Advantages

• Works faster than the XLOOKUP function.
• Available in the old Excel versions.

Disadvantages

• Can’t handle errors when no match is found.
• Needs the lookup_array to be sorted for approximate matches.
• Returns only the first value when multiple values match the lookup_value.

Download Practice Workbook

Get this sample file for a better understanding.

Conclusion

Finally, we are at the end of our long article. Here we tried to create a comparative analysis of the XLOOKUP vs the INDEX-MATCH functions in Excel. Let us know your insightful suggestions on this in the comment box below.

<< Go Back to INDEX MATCH | Formula List | Learn Excel