I recommend Index and Match instead of Vlookup due to its ability to column insertion within the range. While Vlookup requires the user to input a number to identify the column within the range to perform the lookup, when using Index-Match the user specifies both the range of potential output values and the range of input values. If the user inserts or deletes a column between the input values column and the output values column, the formula automatically updates the input and output ranges to follow the data. However, with Vlookup the column number in the formula must be manually updated by the user when columns are added or deleted within the Vlookup range.
Below is an excellent tutorial article about Index and Match.
It mentions other benefits of Index and Match over VLookup:
- It can be used two dimensionally.
- It can be used on horizontal data, not just vertical.
- It can handle (well) ascending or descending data.
- It can be faster, especially for large data sets
Advantages of Vlookup:
- It is easier to use and for others to understand the formula.
- It doesn’t require two formulas, nested, like Index and Match.