VLOOKUP is a very useful tool in Excel but can be difficult to get right. Here’s how to use Excel VLOOKUP, including an example and what to do when it doesn’t work right.

VLOOKUP is a useful tool for data analysis in Excel, especially merging data from multiple sources. It’s a frequent question in job interviews for business analysts and other jobs that handle data.

## Excel VLOOKUP use

Using VLOOKUP in Excel can be confusing, so let’s demystify it with an example and some troubleshooting tips.

The use case for Excel VLOOKUP is when you have data in two different sources and you need to bring them together. If you’re trying to use Excel like a simple database, VLOOKUP is a must. Arguably when you start using it too much, it’s time for a real database, or maybe Python, but for prototyping a database, or for quick-and-dirty one-offs, this function allows you to do serious data analysis using a familiar tool. And let’s face it, unless you live in databases a lot, Excel is more familiar and easier to learn how to use.

I’ve used it in my job to take vulnerability scan data from Nessus or Qualys and merge threat intelligence data into it. Threat intelligence tools are useful for prioritization, but often throw away the specific data from my scanner that proves what they found was real. Merging the two gives me the best of both possible worlds.

That said, VLOOKUP can be hard to get right. Here’s how to get it right. Let’s go through a simple example followed by a complete step by step, and finish up with some tips on troubleshooting if VLOOKUP just isn’t working right.

## Excel VLOOKUP example

Here’s a VLOOKUP in its simplest form:

=VLOOKUP(A2,Sheet2!:A\$1:C\$900,2)

That looks innocent enough. The first parameter is the cell containing the value I want to look up. The second parameter is my data source. This is the data I want to merge in. The last parameter tells me which column of the table contains the cell to bring in when it finds a match.

So why’s it so hard to get right? Because an example without a thorough explanation and troubleshooting tips only gets you so far. Let’s talk about how you have to format your data and why.

### Building out an Excel VLOOKUP, step by step

Nothing lends itself to data analysis like Baseball. So let’s build out some data on three sheets, then bring it together into a single sheet.

First, open a new file, and create three sheets. Paste this into sheet 1.

 name pos gp ba salary WAR Perez Hosmer Infante Escobar Moustakas Gordon Cain Rios Morales

#### Sheet 2

Paste the following into sheet 2, starting at cell A1:

 name pos gp ba Perez c 142 0.26 Hosmer 1b 158 0.297 Infante 2b 124 0.22 Escobar ss 148 0.257 Moustakas 3b 147 0.284 Gordon lf 104 0.271 Cain cf 140 0.307 Rios rf 105 0.255 Morales dh 158 0.29

#### Sheet 3

Paste this into sheet 3, starting at cell A1:

 name salary WAR Perez 1750000 2.4 Hosmer 5650000 4.3 Infante 7500000 -0.7 Escobar 3000000 -1.2 Moustakas 2640000 4.1 Gordon 12500000 2.6 Cain 2725000 7 Rios 11000000 -1.3 Morales 6500000 2.7

#### Returning back to sheet 1, filling out the data

Now let’s go back to sheet 1 and pull in some data. Start by pasting this formula into cell B2 and fill down:

=VLOOKUP(A2,Sheet2!A\$1:D\$10,2,FALSE)

Try both TRUE and FALSE in the last argument. See how the fuzzy match can sometimes be a little too fuzzy?

Let’s bring in another column. Follow up with the following in C2, and fill down:

=VLOOKUP(A2,Sheet2!A\$1:D\$10,3,FALSE)

See the difference? The formula is subtly different.

Now let’s bring in the last column from Sheet 2 into D2, then fill down:

=VLOOKUP(A2,Sheet2!A\$1:D\$10,4,FALSE)

Of course nothing says you have to pull in all the data from all your sources.

Now let’s pull in the data from Sheet 3 into cell E2, and fill down:

=VLOOKUP(A2,Sheet3!:A\$1:C\$10,2,FALSE)

Notice the subtle difference?

Here’s the last column. Paste this into F2, and fill down:

=VLOOKUP(A2,Sheet3!A\$1:C\$10,3,FALSE)

And there you have it. You’ve populated five columns of a spreadsheet with VLOOKUPs.

## What to do when VLOOKUP doesn’t work

There’s a rule with VLOOKUP that’s really important not to forget. VLOOKUP can only look to its right. Depending on the format of your data, that may not be a problem, but if the column you want to use for lookups is in the middle of your data, this can keep VLOOKUP from working right. In my line of work, the column I want to use for lookup always seems to be in the middle.

If your VLOOKUP doesn’t work, rearrange your data. Make sure the column you want to use for lookup is always in Column A to be safe.