How to use Excel VLOOKUP

Last Updated on August 11, 2022 by Dave Farquhar

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

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.

Sheet 2

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

Sheet 3

Paste this into sheet 3, starting at cell A1:

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.

Check your data sources

The second thing that’s easy to get wrong is the data source. If you use the usual Excel formatting of A1:C990 to define the two corners of your lookup table, it might work. Most likely, it will work part of the time, which is just enough to confuse you. The reason it won’t always work is because when you fill down with that formula, it will adjust the table as you move down. Inserting dollar signs to define the lookup table as A$1:C$900 is called locking the cells. When you lock the cells, it will use the whole table unchanged when you fill down. This allows VLOOKUP to look up and down, and not just down. So try to remember to use the dollar sign in your formula to lock the cells to keep VLOOKUP working right.

Fuzzy lookups can help or hurt you

There’s one more thing you can try when VLOOKUP doesn’t work right. If VLOOKUP isn’t bringing in data when you think it should, try adding a fourth parameter of TRUE to the formula, so it looks like this:

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

This parameter tells Excel to use what it calls a Fuzzy lookup, or approximate match. This lets VLOOKUP work when part of the string matches. This helps when you may have leading or trailing spaces, partial names, or comma separated lists.

If you want an exact match, specify FALSE instead of TRUE. This helps when you’re pulling in data, but some of the data looks wrong.

And if this is too limited for you, here are some more data merging tricks.