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

**Read More:** **How to Select Specific Data in Excel (6 Methods)**

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

**Read More:** **How to Match Multiple Criteria from Different Arrays in Excel**

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

**Similar Readings**

**Excel INDEX MATCH to Return Multiple Values in One Cell****Excel INDEX-MATCH Formula to Return Multiple Values Horizontally****How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results****[Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)****How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)**

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

**Read More:** **How to Use INDEX and Match for Partial Match (2 Ways)**

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

**Read More:** **INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)**

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

## Further Readings

**Index Function to Match and Return Multiple Values Vertically in Excel****IF with INDEX-MATCH in Excel (3 Suitable Approaches)****INDEX MATCH with Multiple Criteria in a Different Sheet (2 Ways)****Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)****Index Match with Multiple Matches in Excel (5 Methods)**

RIP Index-Match. Having begun to use XLOOKUP, I don’t see where INDEX-MATCH is to be preferred. XLOOKUP is much easier to us. The only wonder is why it took Microsoft so long to update their archaic formulas.

Hi,

I’m struggling to find the right formula to multiply units by rates.

I have different materials with different units and rates are depend on quantities.

I have a more than 2000 row spreadsheet and units also varies that means that the formula also need to find the unit on sheet 1. Rate criteria can also change on sheet 1.

Many thanks for your help!

Details on Sheet 1

Unit Rate1(not exceeding) Rate2(not exceeding) Rate3(not exceeding) Rate4(exceeding)

day

h

m (QB) 10 50 200 200

m

m2 (QB) 10 50 150 200

m2

Details on Sheet 2

Description Unit Quantity Rate 1 Rate 2 Rate 3 Rate 4 Price

path m (QB) 9 11 13 13.5 14

road m (QB) 51 5 10 15 20

wall m2 (QB) 35 10 15 20 25

wood m 20 11

paint m2 150 12

And I’m looking for the price.

Hi,

I’m struggling to find the right formula to multiply units by rates.

I have different materials with different units and rates are depend on quantities.

I have a more than 2000 row spreadsheet and units also varies that means that the formula also need to find the unit on sheet 1. Rate criteria can also change on sheet 1.

Many thanks for your help!

Details on Sheet 1

Unit Rate1(not exceeding) Rate2(not exceeding) Rate3(not exceeding) Rate4(exceeding)

day

h

m (QB) 10 50 200 200

m

m2 (QB) 10 50 150 200

m2

Details on Sheet 2

Description Unit Quantity Rate 1 Rate 2 Rate 3 Rate 4 Price

path m (QB) 9 11 13 13.5 14

road m (QB) 51 5 10 15 20

wall m2 (QB) 35 10 15 20 25

wood m 20 11

paint m2 150 12

And I’m looking for the price.

Many thanks for your help!

Niki

Hi Niki,

To find out the price, you need to calculate the product of unit and unit quantity. Your sheet 1 denotes the unit of rate 1, rate 2, rate 3, and rate 4. The unit quantity will be found in sheet 2. So, in that case, you can apply the VLOOKUP formula to get the specific unit and unit quantity. You can use the following formula to calculate.

=VLOOKUP(J8,$B$7:$C$12,2,FALSE)*VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE)

=VLOOKUP(J8,$B$7:$C$12,2,FALSE):

First, you need to define the value you are looking for. Here, J8 refers to the lookup value. After that, define the table array where you think the required answer can remain. Then, define the column number of the expected result. Finally, use false to get the exact match. Then, we have to multiply the result of the first VLOOKUP by the result of the second VLOOKUP.

VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE):

As we put the unit in sheet 1, we have to look up the rate 1 unit by utilizing the VLOOKUP function. First, define rate 1 as the lookup value. Then, set the table array in sheet 1. After that, define the column number of the expected result. Finally, use false to get the exact match.

The product of these two values will give the price of rate 1. Do the same procedure for other cases.

Do you get your preferred answer? check on it. Otherwise, give us a more accurate question. We are always ready to help you.

Thank you.