INDEX MATCH vs VLOOKUP Function (9 Examples)

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.


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.

INDEX MATCH vs VLOOKUP

And another table contains different Items records of a company.

INDEX MATCH vs VLOOKUP

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 vs VLOOKUP

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.

number of functions

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.

number of functions

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

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 vs VLOOKUP

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.

different reference system

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.

different reference system

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 vs VLOOKUP

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

inserting a new column

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

inserting a new column

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)

INDEX MATCH vs VLOOKUP

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)

inserting a new column

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 vs VLOOKUP

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

deleting a column

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

INDEX MATCH vs VLOOKUP

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)

deleting a column

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)

deleting a column

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 vs VLOOKUP

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.

direction of columns

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.

direction of columns

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.

INDEX MATCH vs VLOOKUP

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.

direction of columns

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


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 vs VLOOKUP

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.

functional differences

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.

functional differences

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 vs VLOOKUP

INDEX MATCH vs VLOOKUP

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.

looking up the value in row, column or both

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.

looking up the value in row, column or both

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 vs VLOOKUP

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.

adaptability of the formula

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.

adaptability of the formula

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.

adaptability of the formula

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.

INDEX MATCH vs VLOOKUP

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 vs VLOOKUP

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.

error

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

INDEX MATCH vs VLOOKUP

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.

error

This will give us the #N/A error.

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.

error

This will give us the #N/A error.

INDEX MATCH vs VLOOKUP

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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo