Hired with Excel: Why Vlookup?

We are finally covering Excel content. I’ve discussed everything from what an analyst does to the good and the bad in Excel.

Why the slow windup? It’s because being a good analyst is more about having the right mindset than memorizing every formula.

Excel use follows the Pareto Rule — that is, 80% of your work will use about 20% of your domain knowledge. So rather than detail every Excel formula out there, get a strong grip on the most common functions.

Introducing the Vlookup

This is the most common formula you will use as an analyst.

Let’s say you have a list of sales and profits by region and store number.

For a few stores, your boss would like to compare this sales information with the inventory levels of each store, which is located in another spreadsheet.

Above you have data in two places which you would like to join into one table for analysis… a very common analyst problem.

You could manually join these two sets of data…. But here’s why you shouldn’t.

Human error.

When manually searching, a lot can go wrong. You might make a typo. Or your eyes might skip and you read the wrong line. Conclusions based on incorrect data are themselves incorrect.

Difficulty of searching big data sets.

If the company has 100 stores, manually searching for a couple isn’t too bad. But what if there are thousands of stores, and you need to look up hundreds? That would take too long. Efficiency is a trait of a good analyst. You’re on company time!

Difficulty of repeated multiple searches.

The problem with searching for results manually is that it is a hand-to-mouth approach. What if your boss comes back and asks for a few more stores? You must look them all up, one at a time.

The vlookup avoids these problems. As long as you code it correctly, there cannot be errors. Once it is set up, you can “plug and chug,” easily repeating the process across many data points.

Remember our duct tape analogy — the vlookup takes two sets of data, and pulls them together. Now that you understand the concept of the vlookup, let’s go into the mechanics.