Three (3) Reasons why INDEX/Match is better than VLOOKUP
VLOOKUP is used around the globe and has become one of Excel’s most useful and known formulas. It allows a user to quickly and efficiently retrieve data from a different source using a common identifier.
However, just like most things in life, there is something better. There is a clear argument why INDEX/MATCH is more reliable, effective, and downright better. We have written/video tutorials for both formulas, which can be found at the bottom of this page. Lets start…
INDEX/Match can lookup from any range
What would you do if you wanted to use a VLOOKUP formula to bring over the country into the blue shaded cells in Data Set #1? You can’t, because VLOOKUP can only bring over data that is to the RIGHT of the common identifier. In this case, ‘Country’ is to the LEFT of the common identifier in Data Set #2. INDEX/MATCH allows you to select any column, regardless if it is to the left or right of the common identifier.
VLOOKUP is unreliable when inserting columns
VLOOKUP requires you to enter a number that signifies which column to the right of the lookup value you want to bring over. In this case, if we are using Data Set #2 to bring over data into the blue shaded cells, we are entering the number ‘2’ (see highlight in picture). However, what happens when we enter a column between Column D and Column E? The formula will no longer work. INDEX/MATCH does not face this issue.
VLOOKUP is less efficient
If we want to bring over the Department information from Data Set #2 into Data Set #1, it requires us to select the WHOLE table. That requires Excel to work harder and will make your spreadsheet slower. If you use INDEX/Match you only have to select the single column that you want to bring over… not the whole table.
