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

Approximate Match in INDEX-MATCH Formula

Today I will make a comparative analysis on XLOOKUP vs INDEX-MATCH in Excel.

In the earlier versions of Excel, we used to use the HLOOKUP, the VLOOKUP, and the INDEX-MATCH functions of Excel 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 to conduct a similar operation more sophisticatedly.

Each of them has some positive sides, as well as some limitations too. In this article, I will try to make a comparative analysis of the widely used functions, the XLOOKUP and the INDEX-MATCH.


Download Practice Workbook


XLOOKUP vs INDEX-MATCH in Excel

First, we will break down the two functions, then we will make a comparative analysis.


Breaking down the XLOOKUP Function

Excel XLOOKUP Function

The Syntax of the XLOOKUP functions is:

=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
  • Lookup_value: It is the value that we are searching for in a specific column of the range. Here it is F4 (Jennifer Marlo).
  • Lookup_array: It is the array in which we are searching the lookup_value. Can be both row and column. Here it is C4:C15 (Student Name).
  • Return_array: It is the column from which the corresponding value of the lookup_value will be returned. Here it’s Marks in Physics (D4:D21).
  • If_not_found: It is the value that will be returned in case the lookup_array doesn’t have the lookup_value. Here it is “Not Found”.
  • 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.

Here we were looking for the exact marks in Physics of the student name of cell F4 (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.

So we used the formula:

=XLOOKUP(F4,C4:C15,D4:D15,”Not Found”,0,1)

Breaking down the INDEX-MATCH Function

Excel INDEX-MATCH Function

The Syntax of the INDEX-MATCH formula is:

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

For the MATCH function:

  • Lookup_value: It is the value that we are searching for. Here it is F4 (Jennifer Marlo).
  • Lookup_array: It is the array in which we are searching for the lookup_value. Can be both row and column. Here it is C4:C15.
  • Match_type: It is an integer denoting the type of match we are looking for. This is optional.
  • When it -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.

  • 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.

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

For the INDEX Function:

  • Array: It is a range of cells from which we want to extract out a value. Here it is B4:D15.
  • 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. Here it is 3 (Marks in Physics).

Therefore, the complete INDEX-MATCH formula to return the marks of the student in cell F4 (Jennifer Marlo) was:

=INDEX(B4:D15,MATCH(F4,C4:C15,0),3)

Comparison between the XLOOKUP function and the INDEX-MATCH Function

Now we have broken down the formula, let’s discuss some similarities and dissimilarities of the two functions.

Before going to the main discussions, I am showing the major points in a table for your convenience.

Point of Discussion Similarity/Dissimilarity Explanation
Column lookup_array Similarity Both support a column as the lookup_array.
Row lookup_array Similarity Both support a row as the lookup_array.
No Matching of lookup_value Dissimilarity XLOOKUP has the default set up option for no matching of the lookup_value. But the INDEX-MATCH does not have.
Approximate match Partial Similarity XLOOKUP can find out the next smaller or the next larger value when there is no exact match. INDEX-MATCH can also do so, but the lookup_array needs to be sorted in ascending or descending order.
Matching Wildcards Similarity Both support matching Wildcards.
Multiple Values Matching Partial Similarity XLOOKUP can find out either the first or the last value when multiple values match. But INDEX-MATCH can only return the first value that matches.
Array Formula Similarity Both support the array formula.

1. In Case of Lookup Array being a 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.

We have seen it earlier. In the above example, for finding out the number of the student named Jennifer Marlo, the lookup_array was a column (Student Name).

We executed the task using both the XLOOKUP and the INDEX-MATCH functions.


2. In Case of Lookup Array being a 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.

Look at this new data set. This time we have the IDs, Names, and Marks of the students, along with a new column called Grade.

Data Set for XLOOKUP vs INDEX-MATCH

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 (B3:E3) as the lookup_array and the word “Grade” as the lookup_value.

We can accomplish it using both the XLOOKUP and the INDEX-MATCH.

For example, to find out the grade of the 3rd student, the XLOOKUP formula will be:

=XLOOKUP("Grade",B3:E3,B6:E6,"Not Found",0,1)

XLOOKUP Function with Row Lookup Array

And the INDEX-MATCH formula will be:

=INDEX(B3:E22,4,MATCH("Grade",B3:E3,0))

INDEX-MATCH Function with Row Lookup Array


3. In Case of 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 an IFERROR function outside to handle the error.

In the given data set, to find out the Student Name with the ID 100, you can use this XLOOKUP formula:

=XLOOKUP(100,B4:B22,C4:C22,"Not Found",0,1)

XLOOKUP Function with No Match

See, it returns “Not Found”. Cause there is no student with ID 100.

On the other hand, to do a similar task, the INDEX-MATCH formula will be:

=INDEX(B4:E22,MATCH(100,B4:B22,0),2)

INDEX-MATCH Function with No Match

See, it returns #N/A error.

You have to use an IFERROR function outside to handle this error.

=IFERROR(INDEX(B4:E22,MATCH(100,B4:B22,0),2),"Not Found")

IFERROR with INDEX-MATCH Function


4. In Case of Approximate Matches

There is a partial similarity of 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, to find out the student with marks 50 or the next larger mark, you can use this XLOOKUP formula:

=XLOOKUP(50,D4:D22,C4:C22,"Not Found",1,1)

XLOOKUP with Approximate Match

See, there is no student with the mark 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.

See the formula:

=INDEX(B4:E22,MATCH(50,D4:D22,-1),2)

is returning an #N/A error.

INDEX-MATCH with Approximate Match

Cause the lookup_array D4:D22 is not sorted in descending order.

But if you sort it in descending order, it will return Desmond Hayes.

Approximate Match in INDEX-MATCH Formula

Special Note:

In the XLOOKUP function, -1 works for the next smaller value, but in INDEX-MATCH, -1 works for the next larger value.

Similarly in the XLOOKUP function, 1 works for the next larger value, but in INDEX-MATCH, 1 works for the next smaller value.


5. 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.

To find out any student with “Marlo” as the second name, you can use this XLOOKUP formula:

=XLOOKUP("*Marlo*",C4:C22,C4:C22,"Not Found",2,1)

XLOOKUP with Wildcards

Note: To use wildcards in XLOOKUP, you have to set the match_type argument to 2. Otherwise, it will not work.

And the INDEX-MATCH formula to accomplish the same task will be:

=INDEX(B4:E22,MATCH("*Marlo*",C4:C22,0),2)

INDEX-MATCH with Wildcrads


6. In Case of Multiple Values Matching 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:

=XLOOKUP(100,D4:D22,C4:C22,"Not Found",0,1)

XLOOKUP When Multiple Values Match

And to get the last student with 100, use this XLOOKUP formula:

=XLOOKUP(100,D4:D22,C4:C22,"Not Found",0,-1)

XLOOKUP When Multiple Values Match

But in INDEX-MATCH, you will get only the first value that matches.

=INDEX(B4:E22,MATCH(100,D4:D22,0),2)

INDEX-MATCH When Multiple Values Match


7. In Case of Multiple Lookup Values (Array Formula)

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:

=XLOOKUP(G4:G8,B4:B22,E4:E22,"Not Found",0,1)

XLOOKUP Array Formula

And for INDEX-MATCH, the following function will also work:

=INDEX(B4:E22,MATCH(G4:G8,B4:B22,0),4)

INDEX-MATCH Array Formula


Summary

Therefore, to summarize, let’s look at the comparative advantages and disadvantages of both functions.


XLOOKUP

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.
  • Is available in Office 365 only.

INDEX-MATCH

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.

So you have to decide, depending on the circumstances, which one to choose. Do you have any other questions? Feel free to ask us.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo