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**

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.

- When it is
**0**,**XLOOKUP**will search for an exact match (Default) - 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. - 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. - 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:

- If it is
**1**,**XLOOKUP**will search from top to bottom in the**lookup_array**(Default). - When it is –
**1**,**XLOOKUP**will search from bottom to top in the - If it is
**2, XLOOKUP**will conduct a**binary search**in ascending order. - 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**

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

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)`

And the **INDEX-MATCH** formula will be:

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

**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)`

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)`

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")`

**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)`

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.

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.

**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)`

**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)`

**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)`

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

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

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

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

**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)`

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

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

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