It never ceases to amaze me when I open an Excel worksheet and see VLOOKUP or HLOOKUP functions. These functions should basically never be used! In fact, in simple models (e.g. those containing two columns of data with the lookup values placed in the left column) the use of V- or HLOOKUP would be marginally quicker to implement than the INDEX-MATCH alternative. However, it so often arises that initial models are subsequently developed or added to, so that these inappropriate function choices become embedded in larger models that are then hard to rectify without significant rework. My rule of thumb would be to only use a V- or HLOOKUP for some quick-and-dirty analysis that is so raw that the workbook file would never be saved. One should never find these functions in a saved workbook! The benefits of their use are outweighed by other factors in all other cases.
The VLOOKUP (and HLOOKUP) functions have several problems associated with them:
- A lack of flexibility (e.g. column or row numbers needs to be hard-coded). The only way to avoid hard coding of a column or row number for the data to be looked up is by use of a MATCH function. Doing so means that the function is then used in conjunction with MATCH, so can be no better than INDEX-MATCH, even ignoring the other issues.
- Their error-prone nature (e.g. if a column or row is added before a row which is referred to with a hard-coded value in the formulae, then the formulae will give the values from an incorrect row or column).
- They are difficult to audit, since the dependency and precedent paths are two-dimensional ranges, not individual cells.
- File size is typically larger (Excel’s behind-the-scenes formula-paths are longer, creating a larger electronic file.
- They are computationally inefficient when a reference item needs to be looked up several times (e.g. look up a reference value in the first column in order to find its row number, and then return the corresponding value from both column 4 and column 6. In this case, if two VLOOKUPs are used, the row number for the reference figure is looked up twice). By using MATCH (in a separate cell that then drives and index function), this first lookup step need only be conducted once.
- They are less flexible, since the data set referred to cannot be split into separate components so that some parts could be moved to a more appropriate part of the model e.g. when a model needs to be adapted or restructured for another purpose.
In almost all cases, an INDEX-MATCH combination will be far superior. A discussion of this with practical example models is contained in Chapter 25 of my recent book Principles of Financial Modelling.