Comparison Between Index Match and Vlookup Formulas in Excel

Comparison Between Index Match and Vlookup

There two most powerful lookup formulas in excel. First one is Vlookup and second one is Index Match. Vlookup is most common formula nowdays and it is very much popular in Excel. On the other hand Index Match is not so popular but it works same as Vlookup. In fact Index Match is two different formulas Index and Match which combine to get result of lookup. In this post we compare both formulas with different criteria to understand which one is need to be used.

Lets Start -

1. Easy to Understand - 

Most of the people understand Vlookup is very easy as compare to Index Match. Index Match is more large formula compare to Vlookup and it has include two ranges so user may confuse which range to use.

Syntax of Vlookup - 

=VLOOKUP(lookup value, range containing the lookup value, the column 
number in the range containing the return value, 0) 

 Syntax of Index Match -  

=INDEX (column to return a value from, MATCH (lookup value, column to lookup against, 0)) 

Thus most of the people use Vlookup formula as they may be think Index Match is difficult to remember or they don't aware about index match function.


2. Delete / Insert Column - 

There may be chances that you want to delete or insert extra column in sheet after completing sheet.
If in that sheet vlookup formula is included then after deleting or inserting column that formula has #REF reference error.
But it is not the case in Index Match Formula. You can delete or insert column which is not used in formula iteself but formula works fine.
So Index Match is better than vlookup to avoid errors in future. 

3. Right to Left - 

Vlookup only works in one direction i.e. left to Right. If suppose column to return value from is at left side from column to lookup against then vlookup not works. We have to copy paste that column at right side then we can use it.
But in Index Match it dosen't matter which column is on which side. It works perfectly without any condition. We don't want to adjust data to use Index Match formula.


4. Array

Vlookup is not consist array range. We have to adjust data by adding extra column and by using other formula to get result.
Index Match can be use with array. It works perfectly when we have multiple criteria.

 Syntax of Index Match with multiple criteria -  

{=INDEX(range1,MATCH(1,(Lookup Value1=Lookup range1)*(Lookup Value2=Lookup range2),0))}

 Ctrl + Shift + Enter to complete Array Formula

Conclusion - 

Though Vlookup is more popular than Index Match formula but the fact is Index Match is better and more powerful lookup formula than vlookup. Thus forget Vlookup and start to use
Index Match. 

Please watch video tutorial to understand in better way

Thank you..!! Enjoy Excelling !!

Have a great day !!