INDEX MATCH versus VLOOKUP functions - Microsoft Excel

Most of the folks who use Excel do not know that a alternate combination of functions is available for Vlookup. In this post I would explain:

1. Drawbacks of Vlookup function
2. Best alternative for Vlookup function.
3. Usage of index and match function.

Vlookup is a function which allows users to lookup for a particular value in a column and provide the related data of that value from the subsequent columns towards right side.

1. Drawbacks of Vlookup function:

• You cannot lookup for values towards the left of the table from where the lookup value column resides.
• Generally Vlookup is not recommended if you are performing the lookup on a huge dataset. As this uses a lot of system's resources rendering the Excel application useless until the lookup calculation is completed.

2. Best alternative for Vlookup function.

INDEX and MATCH functions are a good alternative for Vlookup as they overcome the drawbacks mentioned earlier.

3. Usage of index and match function.

Index & Match example:

Example 1: Right hand side lookup from a table

Example 2: Left hand side lookup from a table

Hopefully you have enjoyed the tip. Let me know if you want to know something more or have any specific question (questions.aweexcel@gmail.com)