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.

Now this is the reason why we use the INDEX with MATCH, MATCH is to grab both the horizontal and the vertical matches. – (INDEX wants an array, a row number, and a column number )

If you want to be lay with your INDEX match then you will only index one column, and that way you only need the row number to be found with the MATCH.

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.

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]