When performing a financial model review on VLOOKUP, HLOOKUP, and LOOKUP, you can prioritise which formulas to look at first based on the common errors made when building financial models.
Below, in order of probability of the action being a real financial model error, are the high risk actions that we have discovered from years of debugging complex (and easy) financial models.
It is useful to have a financial model auditing tool to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
Table of contents
YouTube video for VLOOKUP, HLOOKUP, and LOOKUP errors
You can see the what is meant in our video clips. Have a look at Finding the vlookup, hlookup and lookup errors in our free Auditing Financial Models online course.
TRUE or blank as the Range Lookup
The very last part of a VLOOKUP and HLOOKUP is the most important. The majority of users need to type FALSE to make it an exact lookup rather than an approximate lookup. As a result any VLOOKUP or HLOOKUP that has a TRUE or is left blank should be investigated first.
As shown below, using the Excel Formula Report, it has identified all the VLOOKUP’s in the financial model. By looking at the end of the VLOOKUP statement you can see the ones that have TRUE or are left blank.
In the image below, you can see that the last part as been left blank which by default means TRUE to Excel. You would need to investigate these first and decide whether they should be using a TRUE.
In this case you will see that the TRUE is bringing up a financial modelling error. Student 100129 does not exist in the other list, yet it is bringing through a result and student 100135 should owe R1 518 but the VLOOKUP is bring through R2 669. This is because TRUE to Excel’s VLOOKUP means find the best place this data fits into the range assuming the range is sorted in ascending order.
If the VLOOKUP/ HLOOKUP should be a TRUE
Even if the VLOOKUP or HLOOKUP should be using a TRUE, there is another risk.
Excel assumes that when you use a TRUE you are allowing it to estimate into which band the item should fall, but it assumes that THE TABLE ARRAY IS IN ASCENDING ORDER, IN THE WAY EXCEL WOULD SORT ASCENDING ORDER.
You need to check that the table is sorted correctly. As shown below in cell E4, the VLOOKUP should result in A201, but it gives A2. Note that the VLOOKUP is blank at the end and in the grey section you will see how it should have been sorted in order for it to work. As a result you need to be careful with sort orders especially alphanumeric sorting otherwise you could have a costly spreadsheet error. If you are unaware of this risk have a look at our How Errors Happen in VLOOKUP true video clip.
No $ signs on the Table Array
It is very rare that the table array (the area you want the VLOOKUP to search through) changes depending on which cell you are in. So as a rule, the Table Array should always be in absolute terms ($ signs in front of the column and row e.g. $A$1:$G$10).
Any variation of this is a risk and should be investigated. When you do investigate it, don’t look at the first VLOOKUP in the range. Look at one of them below or to the right of the initial VLOOKUP identified by the financial modelling auditing tool.
As shown below, there is a VLOOKUP where there are no $ signs on the table array. Also note that the same VLOOKUP is used many times (implying it has been copied and pasted hence the $ risk)
Looking at the actual formula (not the first one (G8) but one lower down (G10)) you can see that the copy of the VLOOKUP is looking at the wrong place. It is missing out the first 2 rows which happen to contain the number we are looking for (100123). This is a very common financial modelling error.
The same applies to HLOOKUP.
Column Number Hardcoded
The 3rd part of a VLOOKUP or HLOOKUP specifies which column/ row to bring back. The risk here is that someone inserted a column or moved something around and the column referred to is no longer what should be returned. You also need to watch out for hidden columns/ rows. They are included in any column counting that VLOOKUP does.
Financial Model Review Impact
When performing a financial model review on VLOOKUP, HLOOKUP and LOOKUP you will in all likelihood need to look at all cells that contain these functions. Identifying the most likely Excel cells with errors in, means you find them faster.
Related
Spreadsheet Auditing Software Add In
Vlookup not finding a value that is there (when using Find)