This financial model illustrates the potential problems or pitfalls with the Excel VLOOKUP function in financial analysis. These five potential issues surrounding the Excel VLOOKUP, can be a source of error and frustration in financial or other spreadsheets.
In this example, we are using a VLOOKUP to reference the Last price for the following Dow Jones Industrial Average stocks.
Strategize FM – 5 Potential Problems using Excel VLOOKUP.xlsx
1st Potential Problem – First Value
In some instances, Excel VLOOKUP can return the wrong value.
Our simple table of DJ30 stocks worked fine for the previous 4 days, however today’s extraction of stock data contains some lines of stock prices from the previous day.
Hence the Excel VLOOKUP is erroneously referencing the previous day’s Last price for: Boeing, Cisco Systems, General Electric, Microsoft and Verizon.
This highlights one major pitfall, which is often overlooked, by financial analysts with the Excel VLOOKUP function – it will always reference the 1st value.
2nd Potential Problem – Exact Value
A powerful feature of the Excel VLOOKUP function, is its capacity to identify only an exact lookup value, which can be also problematic when referencing values from a large data array – as in the following.
This can be true if we are sourcing data, like these closing Dow Jones stock prices, from an external database or information source, which we are dumping into our financial model periodically.
Although the previous 4 days were fine, today’s example illustrates how the bulk of our Excel VLOOKUP formulas are returning an error – #N/A. DuPont and Intel are the exceptions.
On the surface, it appears strange why our Excel VLOOKUP formulas are not working for these other stocks. But it is when we examine these referenced cells in the table array, that we realise these cells contain spaces after the text. Hence Excel VLOOKUP will treat them as not an exact match, and therefore not return the corresponding “Last” price.
To correct this in a simple and seamless way, we apply Excel TRIM function in column K and reference the corresponding cell in column B. Then copy and paste values, and paste these values over this range in column B.
3rd Potential Problem – Absolute Table Array Range
The Table Array parameter in the Excel VLOOKUP function can sometimes pose problems. The previous 4 days of days were all uniform, however in the following data dump stock prices; there are both DJ30 and NASDAQ 100 stocks listed.
When we copy over the legacy Excel VLOOKUP formula from the previous day, the absolute reference range doesn’t facilitate all the data in this extended range. The Table Array parameter should be able to facilitate variable data arrays, either via the creation of a defined name range or an expanded range i.e. $B$2:$G$500 or $B:$G.
Please remember a large Table Array of referenced cells will increase the file size of the financial model.
4th Potential Problem – Absolute Column Number Referencing
This can be a major cause of erroneous Excel VLOOKUP referencing in financial modelling. Under this scenario the data extract appears uniform, however there is an additional column called “Executing Broker”, which our data source is now presenting in the data array.
It may seem innocuous, but our existing Excel VLOOKUP formula is now incorrectly referencing the “Low” price; instead of the required “Last” price for each of the stocks. Even if we cut out the “Executing Broker” column, paste over the right-hand columns; the Column Index Number will still wrongly reference the 6th column – column G.
Instead of using the hard-coded 6 integer, replace it with the Excel COLUMNS function; which will reference the entire data array. Therefore, when we insert the “Executing Broker” column into the data extract of stock prices, our Excel VLOOKUP formula will update and flex to facilitate the additional column.
We will continue to reference the “Last” stock price for our Excel VLOOKUP formula.
5th Potential Problem – Range Lookup
This is probably the most obscure pitfall of the Excel VLOOKUP in financial analysis. It is advisable in best practice financial modelling to default to the “FALSE” option – which references an exact match. In the following example, the data extract of the Dow Jones stock prices is not clean.
Hence if we are certain there is only one reference for each stock, then it is still advisable to maintain the “FALSE” option in our Excel VLOOKUP formula and not change to the “TRUE” option – closest match.
As this example illustrates, applying the closest match Range_Lookup parameter, will incorrectly reference adjacent values. The exact “FALSE” option would prevent these errors occurring; after we have cleaned up the source data array.