Stop using VLOOKUPs

Many of the finance teams that we talk to are using Excel to manage their corporate budget and their headcount plan. After all, it's a really robust tool that can get your team pretty far. But as teams start to collaborate on a budget plan, some of the Excel functions that you're using end up being a recipe for disaster. The number one culprit we see causing problems for teams: VLOOKUPs.

The issue is so bad, that a couple of my finance friends will automatically fail an FP&A candidate if they suggest using VLOOKUPs during an interview.

For anyone living in the dark, here's what they are and why you should avoid them.

What is a VLOOKUP?

A VLOOKUP (short for vertical lookup) is an Excel function that helps you search a table for a specific value in one column to return related data from a separate column.

Let's take an FP&A example. Imagine that I was updating our headcount model. My headcount model has a list of all current employees with their employee IDs. I need to make sure that the salary I have in my model is correct, so I download a report from the payroll system, look up each employee using their employee ID, and write their current salary on my spreadsheet.

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:

• They're easy to break.
  
  

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.