Today I want to discuss the question above.
Which formula is better and why.
We all know and love VLOOKUP
Probably one of the first more advanced formulas that we all learned at some point.
You can take a look at VLOOKUP here and find out how it works.
Excel reads like humans do.
From left to right, then down a row.
This makes the story interesting because this means that if your dataset is wider than taller, excel have to check/read a lot more columns, before it can start to check the next row.
While INDEX MATCH does not have to read like humans
The reason why INDEX and MATCH is quicker than VLOOKUP is because INDEX only records the boundaries of your dataset. Ie.. It will say O.K. I have a dataset 100 x 100. (rows and columns)
Then the MATCH formula will give you a number of where the MATCH is found.
Any other reason for INDEX MATCH?
Perhaps the biggest pro for INDEX in the VLOOKUP vs INDEX MATCH is that the indexing column can be anywhere. So basically you can do your lookup to the left if you have to. Lock that column and you have a bulletproof sheet.
ExcelAngel has a facebook page. Come and visit us.