INDEX MATCH vs VLOOKUP Function (9 Practical Examples)

In this article, you will get 9 practical examples of the INDEX MATCH vs VLOOKUP function. Excel provides various functions to look up a value in a large dataset. Nevertheless, INDEX-MATCH allows us to look up values more effectively than the VLOOKUP function. Therefore, 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.

Don’t forget to check out the overview of this article.

INDEX MATCH vs VLOOKUP Function in Excel: 9 Examples

To show the differences between the INDEX-MATCH formula and the VLOOKUP function, we are using two tables here.
One is the Student records of a college.

Another table contains different Item records of a company.

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

1. Number of Functions for INDEX MATCH VS VLOOKUP Function

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 Mark 80 for the student Sara

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

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.

Read More: How to Use VLOOKUP Function with Exact Match in Excel

2. Different Reference Systems of 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 look up the Marks for the Student Sara.

INDEX-MATCH Function:

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

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 Mark 80 for the student Sara

Here, 2 is the column index number which is the static reference in the VLOOKUP function 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.

3. Inserting a New Column for INDEX MATCH VS VLOOKUP Function

Here, we will demonstrate the changes in 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 Mark 80 for the student Sara

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.

VLOOKUP Function:

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

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.

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 for INDEX MATCH VS VLOOKUP Function

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 Mark 80 for the student Sara

But then we 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.

VLOOKUP Function:

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

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.

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. Direction of Columns in 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.

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.

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.

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.

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.

6. Functional Differences for INDEX MATCH VS VLOOKUP Function

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.

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

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

7. Looking up the Value in Row or Column or Both

The INDEX-MATCH function can look for the value in both the row-wise and column-wise direction but the VLOOKUP function can look 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

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

It will only look 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 the column-wise direction.

8. Adaptability of Formula for INDEX MATCH VS VLOOKUP Function

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

Now, drag the formula to the right side.

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

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

Now, drag the formula to the right side.

But we are not getting the correct value here.

The formula doesn’t change here due to the static referencing 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

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.

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 look for a value that is not in the range then you will get the #N/A error.

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.

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.

Things to Remember:

⦿ 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: Use VLOOKUP to Sum Multiple Rows in Excel

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.

Download Workbook

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

• Combining SUMPRODUCT and VLOOKUP Functions in Excel
• How to Use VLOOKUP with COUNTIF
• How to Combine SUMIF and VLOOKUP in Excel 
• Excel LOOKUP vs VLOOKUP
• XLOOKUP vs VLOOKUP in Excel
• How to Use Nested VLOOKUP in Excel
• IF and VLOOKUP Nested Function in Excel 
• How to Use IF ISNA Function with VLOOKUP in Excel
• How to VLOOKUP and SUM Across Multiple Sheets in Excel
• How to Use VLOOKUP Function with INDIRECT Function in Excel
• How to Use IFERROR with VLOOKUP in Excel
• VLOOKUP with IF Condition in Excel

<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel