Many articles by different experts laud the superiority of INDEX/MATCH over VLOOKUP. I decided to do a shootout myself and see whether it really makes sense to use INDEX/MATCH in place of VLOOKUP purely from the perspective of speed / time taken. I am not considering any other aspect but speed / time.
Following is the methodology to test -
1. The worksheet 100000 contains 100001 records. Column A is Numbers and column B is Text. Column C is alpha-numeric which is the result field.
2. Column E is the sort of column A and column F is sort of column B (both sort on ascending order). Column G is alphanumeric which is the result field.
3. Column I and J contain 50000 numeric and text entries respectively as keys to generate 50000 VLOOKUPs and INDEX/MATCH which can be timed.
4. For both Numeric and Text fields, following combinations have been timed
5. Calculations have been run 5 times for each scenario and average has been taken to come to a final figure for a scenario.
6. Timer used is Charles William's MicroTimer. The code is contained within the workbook itself.
The Excel workbook related to this article can be downloaded from (Note - File size - 20 MB) Vlookup_Index_Match_Shootout
Below are the results (Time is in seconds)
1. INDEX/MATCH is not faster than VLOOKUP. Both take almost same time. On the basis of time taken only (not considering other advantages / disadvantages), replacement of VLOOKUP by INDEX/MATCH will not yield any benefit.
2. VLOOKUP and INDEX/MATCH on Text are slower than on Number field.(In my data, it is slower by around 60%)
3. VLOOKUP and INDEX/MATCH with error handling take the same time as without error handling. Hence, we should use VLOOKUP and INDEX/MATCH with error handling without any performance consideration.
4. If Match Type is 0, sorting doesn't have much benefit. Benefit is to the order of around 20% only. Hence, if you can not sort data, not much concern.
5. Sorting on Text doesn't improve the performance is Match Type is 0. Rather, it is deteriorating the performance by approximately by 100%.
6. It is sorting with Match Type = 1 which gives the huge boost. It is faster by 30000%. A very very huge improvement.