Now we know which employees had a salary increase, so I can update my headcount plan. What could go wrong? Where VLOOKUPS fail
It turns out, a lot can go wrong. VLOOKUPS are a fragile formula to build into a model, especially when you build them into a document that you're using to collaborate with others. Here are the top ways that they fall apart:
If someone accidentally changes the export from your HR system and the column structure changes, your whole model will break. If a column is added or deleted from your spreadsheet, then you need to manually go in and change the lookup column. This happens more often than people expect, especially if you're working on the document with other people. Best case scenario, you end up with broken formulas, worst-case scenario, you don't catch it and your model continues calculating, but using incorrect data.
- Using them defines the structure of your spreadsheet.
The placement of the data is really specific. The value that you're looking up, must always be to the right of the search column. Your lookup column must be in the first column for a VLOOKUP to work. If either of these changes, then the VLOOKUP breaks. There are so many moving parts, that over time, it's easy to get them wrong.
- They will make your file crash.
VLOOKUPS are really resource-intensive. Microsoft has worked to improve the performance of common calculations
in the past few years, but VLOOKUPs work by checking the entire array in a formula. My Stark Industries headcount example above is pretty straightforward, but VLOOKUPs start to take up a fair amount of processing power when you plug them into large complicated spreadsheets that are executing many background calculations. All this work can result in a high CPU and memory usage that will crash your file. What do you instead?
If you're set on using Excel, a less fragile formula to try is an Index match. We'll go into more details on why Index matches are better than VLOOKUPs in another post. But even when using more performant formulas, broken formulas are incredibly common when you're collaborating on an Excel file with other people. Cobbler
can help by automating the manual work that you're doing today to maintain your headcount plan. We connect directly into the HR system, pulling the most up-to-date data and eliminating the need to email Excel files back and forth. If you want to free up your team from spending hours maintaining BVA reports and headcount reporting, reach out to us to schedule a demo.