Although vLookups might initially scare some Excel users off, they’re probably more straightforward than you think. Plus, once you’ve got an understanding of them down, you can save yourself a lot of time and effort while impressing your colleagues – what’s not to love!?

Obviously, the “understanding” part can initially be a bit difficult – especially if your experience with formulas isn’t particularly extensive. That’s why I’m going to break down the steps of a vLookup today, so you can see why we’re typing each individual part of the VLOOKUP formula.

Note: If any of the images on this page are hard to read, try right-clicking them and selecting 'Open in a new tab'

When Should a VLOOKUP be Used?

A VLOOKUP should be used in any situation where it would be helpful to automate retrieval of information. We can do this by using a table where we’ll (hopefully!) have a match for the information we’re looking up, as well as additional data which we’ll pull out with our formula.

Here’s an example:

John works in an office, and has been asked to retrospectively trawl through some monthly logs (which are in a consistent spreadsheet format, with filenames like “Jan-2024.xls”, “Feb-2024.xls”, and so on). These logs contain a listing of every item sold by his employer for that particular month.

The incomplete sales spreadsheets which require completion (“jan-2024.xls”)

Management have told John that he needs to go through the sales and, depending on which item was sold, mark whether or not a VAT calculation needs to be applied to the total cost.

Management have also provided the below spreadsheet to John (called “lookup-values.xls”), which shows which items are subject to VAT, and how much VAT is applicable if so:

The information we’ll be referring to. (“lookup-values.xls”)

As you can see, it’s a pretty straightforward job; the VAT amounts are provided as decimal values (i.e. “.20” means 20%), so what we really need to do is pull those through onto our sales lists so that we can factor them into our calculations.

We could look them up and type them one-by-one, but it’s going to be much easier to just automate the process; so let’s do that using the VLOOKUP function.

Outline of a vLookup

Let’s look at the basic structure of a VLOOKUP. In its most basic form, a VLOOKUP always contains two core pieces of information:

• The Lookup Value This is the value you’re starting off with (from the sales lists), and which you’ll be looking up in your reference table (or table range) to find the appropriate return value (which in this case is the VAT amount).
• The Table Range This is the area which holds all of the information we’ll be referring to; essentially, we’ll be trying to match our lookup value to the first column of our table range, and then selecting the desired data from an adjacent cell.

Preparing our Spreadsheet

Before we can actually start implementing our vlookup, we’ll need to prepare the spreadsheet first. There are more efficient ways to do this once we become a little more comfortable but, for the time being, I’m going to just copy and paste the information from “lookup-values.xls” into “Jan-2024.xls”.

These pasted values will serve as our table range, which exists between columns J and L. Each row in our original purchase list (between columns A and H) will serve as our lookup value.

Writing our Formula

Let’s go through a few rows of the sales list. The first row, as you can see, is a sale of tuna which occurred on 30th January, 2024. We want to grab the applicable VAT value for “tuna” from our imported table range (in columns J to L), and we want to insert that value into cell F2.

We’ll do this by clicking on cell F2, and starting to type our formula:

=vlookup(B2,J:L,3,FALSE)

Does that make any sense? Don’t worry if not! Here’s a breakdown of our vlookup:

Note: in the below screenshot, I’ve alphabetically sorted the imported table range. This was just for my own neurotic satisfaction, and will make no difference to how the formula works!

1. B2 this is our lookup value. In other words, this is the information we already have, and what we’ll be using to look up the corresponding VAT amount in our table range. As you can see from the above screenshot, we’re looking for the corresponding VAT amount for tuna (the value in B2).
2. J:L this refers to our table range, which holds all of the information we need for our lookup (in other words, the answers to our VAT queries). The table range begins at H and ends at J, and so we enter this information as “H:J” (without quotation marks).
3. 3 refers to the third column within our table range, and it’s the column we want to retrieve information from. We’re looking for the VAT amounts stored in our table range, and so we’re looking to grab the information from the third column.
4. FALSE we enter “FALSE” here, because we do not want to accept approximate matches – we only desire exact matches. Generally, you’re going to want to use “FALSE” with vlookups (I don’t think I’ve ever used approximate matches in over two decades of working with Excel!).

In short, the above formula looks for the value in cell B2 (“tuna”), then refers to the table range (between – and including – columns J and L). It searches the table range row-by-row, until it finds the row with “tuna”, and then grabs the data from the third column (which will be “.20”).

And this is the result:

Ok, so hopefully that makes things a little clearer; but how about if we wanted to look up the information in cell B3? This time, we’re looking for the VAT amount for cardboard boxes. As always, we could manually enter the formula as:

=vlookup(B3,J:L,3,FALSE)

The only thing we’d be changing here is the lookup value (from B2 to B3), as we’re still referring to the same table range, the same column number, and we still need an exact match.

However, there’s no need to enter anything in cell B3. We can simply double-click on the tiny box at the bottom-right of cell B2:

That should auto-fill the formula down onto every row of our spreadsheet!

If that doesn’t work, you can always just click on the tiny box and drag it down manually.

Note: For more on using Autofills, please do see our recent article about them!

I won’t bother going through the rest of the sample task step-by-step, as we won’t be using VLOOKUPs any further in this particular spreadsheet.

For your own reference though, I’ll provide the following formulas which I used to complete the data:

• G2 used the formula “=E2*F2” (calculating the applicable VAT amount by multiplying the ‘Before VAT’ value by the VAT rate)
• H2 used the formula “=E2+G2” (adding the ‘Before VAT’ value and calculated applicable VAT amount together)

And, of course, I double-clicked the tiny green square on both cells to autofill the columns as required!

By copying and pasting the table range from “lookup-values.xls” into our other purchase sheets, we can repeat these steps and still save ourselves time. On the other hand, if you’re keen to learn how to lookup values in a completely separate Excel file – and avoid the need to copy and paste table ranges - this tutorial at ExcelJet should be a great starting point.

Summary

Although this is quite a brief introduction to vlookups, it can be a bit much to get used to at first, so it’s best to start by only concerning yourself with the fundamentals. My advice is to play around with them using dummy data, until you feel confident enough to really have a go at using one in the office.

For more articles about how to use Microsoft Office, remember to keep checking back at XL-How.com :)