LOOKUP functions, especially VLOOKUPs are very commonly used in financial modelling – sometimes a little too commonly used! Knowing when and how to use them – and use them well is a critical skill for any financial modeller. Let’s explore some of the problems with LOOKUP functions and how to make their more robust.
If you have created a VLOOKUP in a model such as the one here, this should work well….
Until someone enters or deletes a column in your source data!
With a formula such as =VLOOKUP(A19,A2:B15,2,0), it specifically asks for the second column, so it will not work if someone inserts a column within the range. This is because your required column becomes the third column, but the VLOOKUP is asking for the second.
VLOOKUPs and HLOOKUPs are not particularly robust formulas – you can see how easy they are to break! For this reason, try some of the alternatives below to make your LOOKUP more robust, use another formula, or else protect your model so that users cannot insert or delete rows or columns.
The best way around this is to make the “2” in your VLOOKUP a formula instead of a hard-coded number. A way to do this is to nest the VLOOKUP with a MATCH, COLUMN or ROW function.
What difference does a V make?
HLOOKUPs work in exactly the same way, except that they are orientated horizontally instead of vertically, and are subject to the same problems.
The LOOKUP function is much simpler than either the VLOOKUP or the HLOOKUP, and it has the added advantage of being able to have the results column or row either to the left or the right of the criteria column or row – a huge advantage. However, LOOOKUPs will only return a close match and so the data MUST be sorted in alphabetical order or it won’t work. This does limit it usage significantly, and it is for this reason that it is far less popular than the VLOOKUP or HLOOKUP formulas.
So, which LOOKUP function should I use?
Here’s a quick overview of the different LOOKUP functions and where and when to use them!
What are the alternatives to a VLOOKUP?
If a VLOOKUP simply isn’t doing it for you, you might consider a combination of an INDEX and MATCH functions which take a little longer to build, but do not have the limitations of LOOKUP formulas.
