In this article, you will get the differences between the **INDEX MATCH** vs **VLOOKUP** function.

To look up a value in a large dataset you can use both the **INDEX-MATCH** function and the **VLOOKUP **function. In fact, **INDEX-MATCH** allows us to look up values more effectively than the **VLOOKUP **function.

So, by going through this article you will get an overall comparison study of the **INDEX-MATCH **function and the **VLOOKUP **function and be able to decide which formula you should use for looking up a value in your dataset.

**Table of Contents**hide

## Download Workbook

## 9 Examples of INDEX MATCH vs VLOOKUP Function

For showing the differences between the **INDEX-MATCH **function and the **VLOOKUP **function, we are using two tables here.

One is the *Students* records of a college.

And another table contains different *Items *records of a company.

For creating the article, we have used *Microsoft Excel 365* version, you can use any other versions according to your convenience.

__1. Number of Functions__

Here, we will look up the *Marks* of a *Student ***Sara** by using both the **INDEX-MATCH** function and the **VLOOKUP **function.

** INDEX-MATCH Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=INDEX($C$5:$C$10,MATCH(B13,$B$5:$B$10,0))`

So, here we can see this formula consists of two functions one is the **INDEX function **and the other is the **MATCH function**. Inside the **MATCH function **here **B13 **is the **lookup value**, **$B$5:$B$10 **is the **lookup array** and **0 **is for an **exact match**. Finally, **MATCH **will return the row or column index number of the lookup value in the data range.

**MATCH** will return here **4**.

Then, it will pass this information to the **INDEX function **which returns the information we actually want by using the return range **$C$5:$C$10**.

** VLOOKUP Function**:

By using the following formula, we can get the *Marks* for the student **Sara**

`=VLOOKUP(B13,$B$5:$D$10,2, FALSE)`

This formula consists of only one function which is the **VLOOKUP function**.

Here, **B13 **is the **lookup value**, **$B$5:$D$10 **is the **table array**, **2 **is the **column index number **and **FALSE **is for an **exact match**.

** Quick Note**:

Considering the above two formulas, we can say that it is easier to use the

**VLOOKUP function**than the

**INDEX-MATCH function**.

__2. Different Reference Systems of __**INDEX-MATCH vs VLOOKUP function**

**INDEX-MATCH vs VLOOKUP function**

We will here demonstrate the two different referencing systems of the **INDEX-MATCH function **and the **VLOOKUP function **by using the following table to lookup the *Marks *for the *Student Sara*.

** INDEX-MATCH Function**:

After using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=INDEX(C5:C10,MATCH($B13,$B$5:$B$10,0))`

Here, the return range **C5:C10 **within the **INDEX function **uses dynamic references so it can change with the change of the data range and give us the right output.

** VLOOKUP Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=VLOOKUP(B13,$B$5:$D$10,2, FALSE)`

Here, **2 **is the **column index number **which is the static reference in the **VLOOKUP function **and so it cannot give us the correct value with the change of the dataset.

** Quick Note**:

Considering the above two formulas, we can say that it is more advantageous to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the dynamic referencing.

**Read More:** **[Fixed!] INDEX MATCH Not Returning Correct Value in Excel (5 Reasons)**

__3. Inserting a New Column__

Here, we will demonstrate the changes of results after inserting a new column named **Subject **before the column **Marks** in the case of the **INDEX-MATCH function **and the **VLOOKUP function**.

** INDEX-MATCH Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=INDEX(C5:C10,MATCH($B13,$B$5:$B$10,0))`

But then we have inserted a new column before the column **Marks**.

In Spite of inserting this column, this formula is giving us the right output due to the dynamic references inside the **INDEX function**. It automatically changes the return range here from **C5:C10 **to **D5:D10**.

`=INDEX(D5:D10,MATCH($B13,$B$5:$B$10,0))`

** VLOOKUP Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=VLOOKUP(B13,$B$5:$D$10,2, FALSE)`

But after inserting the new column we are getting **Math **instead of **80**. This change occurs because static reference **2 **doesn’t change and here the **Subject **column has the column index number **2**.

`=VLOOKUP(B13,$B$5:$E$10,2,FALSE)`

** Quick Note**:

Considering the above two formulas, we can say that it is more convenient to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the advantages of having the correct output in spite of inserting a new column.

__4. Deleting a Column__

Here, we will show the changes of results after deleting a column named **Subject **in the case of the **INDEX-MATCH function **and the **VLOOKUP function**.

** INDEX-MATCH Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=INDEX(D5:D10,MATCH($B13,$B$5:$B$10,0))`

But then we have deleted the column **Subject**.

In Spite of deleting this column, this formula is giving us the right output due to the dynamic references inside the **INDEX function**. It automatically changes the return range here from **D5:D10 **to **C5:C10**.

`=INDEX(C5:C10,MATCH($B13,$B$5:$B$10,0))`

** VLOOKUP Function**:

By using the following formula, we have got the *Marks ***80 **for the student **Sara**

`=VLOOKUP(B13,$B$5:$E$10,3, FALSE)`

But after deleting the column **Subject **we are getting **B **instead of **80**. This change occurs because static reference **3 **doesn’t change and here the **Grade **column has the column index number **3 **now.

`=VLOOKUP(B13,$B$5:$D$10,3, FALSE)`

** Quick Note**:

Considering the above two cases, we can say that it is more convenient to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the advantages of having the correct output in spite of deleting a column.

__5. The direction of Columns in the Range__

We will show the difference in the direction of columns used in the **INDEX-MATCH function **and the **VLOOKUP function **by using the following table.

** INDEX-MATCH Function**:

By going from left to right from the lookup value to the return value, we have got a *Sales *value of $1000.00 for **Banana**.

`=INDEX(E5:E7,MATCH($B10,$B$5:$B$7,0))`

Here, the position of the lookup range **$B$5:$B$7 **is before the return range **E5:E7**.

To go from the right column to the left side like here having *Banana *for the lookup value *East*.

`=INDEX(B5:B7,MATCH($B10,$D$5:$D$7,0))`

Here, the lookup range **$D$5:$D$7 **is after the return range **B5:B7**.

** VLOOKUP Function**:

By going from left to right from the lookup value to the return value, we have got a *Sales *value of $1000.00 for **Banana**.

`=VLOOKUP(B10,B5:E7,4,FALSE)`

Here, the position of the lookup value is in the range **B5:E7 **and the column index number is **4** which is after the lookup range.

To go from the right column to the left column like here we want to have the corresponding item for the lookup value *East*.

`=VLOOKUP(B10,B5:E7,1,FALSE)`

So, going from the right side to the left side or having a lookup value after the return range will give us an error in this case.

** Quick Note**:

So, we can say that it is more convenient to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the advantages of having the correct output regardless of the direction of the lookup range and return range.

**Similar Readings**

**How to Use INDEX and Match for Partial Match (2 Ways)****SUMIF with INDEX and MATCH Functions in Excel****Index Match Sum Multiple Rows in Excel (3 Ways)****Index Match Multiple Criteria in Rows and Columns in Excel****How to Match Multiple Criteria from Different Arrays in Excel**

__6. Functional Differences__

The** INDEX-MATCH function **can give an output for multiple conditions whereas the **VLOOKUP function **can give multiple values for a lookup value.

To show these differences we are using the following table.

** INDEX-MATCH Function**:

Here, we are having the *Sales *value of **$6000.00** for different conditions like **Orange**, **2020**, and **North **as **Items**, **Year**, and **Location **respectively.

`=INDEX(E5:E7,MATCH(1,(B10=B5:B7)*(C10=C5:C7)*(D10=D5:D7),0))`

**B10, C10, **and **D10 **are the lookup values that will be looked up in **B5:B7, C5:C7, **and **D5:D7 **ranges respectively and **0 **is for an **exact match.**

**MATCH **will return here the row-index number **3**.

Then **the INDEX function** will return the corresponding value.

** VLOOKUP Function**:

For the item **Orange, **we are getting the **Year 2020**, **Location North, **and **Sales $6000.00** respectively by using the following formula

`=VLOOKUP(B10,B5:E7,{2,3,4},FALSE)`

Here, **B10 **is the** lookup value**, **B5:E7 **is the **table array**, **{2,3,4} **is the array of the **column index number **and **FALSE **is for **Exact match**.

** Quick Note**:

So, you can use any of these formulas according to your purposes. These two are convenient for these separate two cases.

**Read More:** **Excel INDEX MATCH to Return Multiple Values in One Cell**

__7. Looking up the Value in Row or Column or Both__

The **INDEX-MATCH **function can lookup for the value in both the row-wise and column-wise direction but the **VLOOKUP **function can lookup for the value only in the column-wise direction. So, the **INDEX-MATCH **function fulfills both the purposes of the **HLOOKUP **function and the **VLOOKUP **function.

The first table here, we are using for the **INDEX-MATCH **function and the second table is for the **VLOOKUP **function.

** INDEX-MATCH Function**:

By using the following formula we will get the *Sales of ***$1000.00**

` =INDEX(C6:E8, MATCH(H7,B6:B8,0), MATCH(H5&H6,C4:E4&C5:E5,0))`

**MATCH(H7, B6:B8,0)** is used for row-wise matching, and **MATCH(H5&H6, C4:E4&C5:E5,0) **is used for column-wise matching.

** VLOOKUP Function**:

After using the following formula we will get the *Sales of ***$1000.00**

`=VLOOKUP(B10,B5:E7,4,FALSE)`

It will only lookup for the value in the column-wise direction.

** Quick Note**:

So, we can say that it is more convenient to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the advantages of looking up the value in both the row-wise and column-wise direction rather than only column-wise direction.

__8. The adaptability of the Formula__

You can easily copy or drag the formula to get the correct result in the case of the **INDEX-MATCH function **but the **VLOOKUP function **will not give the correct result.

** INDEX-MATCH Function**:

Firstly, we have got the **Year 2019 **by using the following formula

`=INDEX(C5:C7,MATCH($B10,$B$5:$B$7,0))`

Now, drag the formula to the right side.

After that, you will get the correct output **East **for the lookup value **Banana**.

`=INDEX(D5:D7,MATCH($B10,$B$5:$B$7,0))`

Due to the dynamic references inside the **INDEX function**, it automatically changes the return range here from **C5:C7 **to **D5:D7**.

** VLOOKUP Function**:

Here, we have got the **Year 2019 **by using the following formula

`=VLOOKUP($B10,$B$5:$E$7,2,FALSE)`

Now, drag the formula to the right side.

But we are not getting the correct value here.

`=VLOOKUP($B10,$B$5:$E$7,2,FALSE)`

The formula doesn’t change here due to the static referencing and so it gives the same value as the previous one.

** Quick Note**:

Considering the above two formulas, we can say that it is more convenient to use the

**INDEX-MATCH function**than the

**VLOOKUP function**due to the advantages of having the correct output in spite of dragging or copying the formula.

__9. Error in Formula __**INDEX-MATCH vs VLOOKUP function**

**INDEX-MATCH vs VLOOKUP function**

In the case of the **INDEX-MATCH function, **you can get two errors; **#REF! **and **#N/A** but for the **VLOOKUP function, **you can get only the **#N/A **error.

** INDEX-MATCH Function**:

Here, we have mistaken the range within the **MATCH **function and within the **INDEX function**.

`=INDEX(E5:E6,MATCH(B10,B5:B7,0))`

Here, the selected range **E5:E6 **is less than the range **B5:B7**.

For this blunder in range selection, we are getting the **#REF! **error here.

Moreover, if you try to lookup for a value that is not in the range then you will get the **#N/A **error.

`=INDEX(E5:E7,MATCH(B10,B5:B7,0))`

Here, **Blueberry **is not in the range.

This will give us the **#N/A **error.

** VLOOKUP Function**:

For looking up a value that is not in the range then you will get the **#N/A **error.

`=VLOOKUP(B10,B5:E7,4,FALSE)`

Here, **Blueberry **is not in the range.

This will give us the **#N/A **error.

** Quick Note**:

Considering the number of errors, we can say that it is more convenient to use the

**VLOOKUP function**than the

**INDEX-MATCH function**.

__Other Things to Notice__

⦿ For a **look-up **value exceeding 255 characters, **VLOOKUP** will produce an error but in this case, the **INDEX-MATCH** function can work properly.

⦿ In the case of a large dataset, the **INDEX-MATCH** function is way faster than the **VLOOKUP **function.

⦿For returning multiple values **INDEX MATCH function** is used instead of the **VLOOKUP function**.

**Read More:** **How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)**

## Practice Section

For doing practice by yourself I have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, I tried to show the differences between the **INDEX-MATCH **function and the **VLOOKUP function**. Considering these differences it can be said that it is more convenient to use the **INDEX-MATCH **function instead of the **VLOOKUP function**.

If you have any suggestions or questions, feel free to share them in the comment section.

## Related Articles

**Excel INDEX-MATCH Formula to Return Multiple Values Horizontally****INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)****INDEX+MATCH with Duplicate Values in Excel (3 Quick Methods)****How to Use INDEX-MATCH Formula in Excel to Generate Multiple Results****Index Function to Match and Return Multiple Values Vertically in Excel****Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)****XLOOKUP vs INDEX-MATCH in Excel (All Possible Comparisons)**