Welcome to the top online resource for help with vlookup on excel. It’s one of the most popular functions in the famous spreadsheet application – simple yet powerful.

Master Excel to get a higher-paying job – Click here for an exclusive course!

Help with vlookup for excelDid you know the vlookup function in Excel stands for “vertical lookup”?

It can be used to find specific information that is stored in a spreadsheet table. But for beginners to Excel, functions and vlookup in particular can leave you confused. Help is at hand! Take a look around our website and the mystery will be solved.

To begin with, check you are using the right function. If there is specific information you want to look up and display in another part of the spreadsheet. In fact, you can take information from another worksheet and display it on another one entirely. This is useful when, for example, you want to keep reference information “hidden” from view on a second worksheet. Here’s some great examples of practical uses of vlookup in Excel:

  • A student gradebook to automatically give a letter grade based on the percentage gained in a test
  • Translating a product number into a product name on a sales pipeline
  • Looking up the part name based on the part number at an auto garage
  • Find a person’s phone number from a membership list

So, let’s get started with a straightforward example.

Improve your Excel skills at home & get that promotion! Click here for an exclusive offer.

Airport Schedule

Let’s say you come across a list of scheduled flights from your local airport. Around the globe airport names are shortened to three letter codes to aid universal understanding across languages and IT systems. That may work a treat, but it doesn’t help us when planning our trips! Take the example below:

Airport Times example

Not very useful as it is! But if you also had the corresponding names to go with the codes, life would be much easier. And, oh look, here it is:

Airport Codes

What we need to do now, is get the two tables talking to one another. The best way to do that? Why what else, it’s the vlookup excel function of course! Let’s get cracking. The following is the syntax for the function, in other words, the template you need to use. You type this into a cell, replacing the parameters (value, table_array etc) with the relevant values.

=VLOOKUP(value, table_array, index_number, not_exact_match)

Let’s keep things simple and say both tables are on one spreadsheet, as follows:

Enter the function in this cell

Now let’s enter the following into cell D3: =VLOOKUP(B3, $F$3:$G$7, 2, False)

Examing this formula one section at a time:

B3 = the cell containing the value we want to lookup (in this case the airport code)
$F$3:$G$7 = the reference table without the column headings, i.e. a square from cells F3 to G7
2 = the number of the column within the above range where the value to be delivered (the airport name) is kept
False = this means an exact match is required with the three-letter code

When you press enter/return after typing the vlookup excel formula, you should see the following:

Result of the vlookup function displayed

Success! Now simply hover the cursor over the bottom-right of the highlighted cell until you see the crosshair, click and hold, and drag down to copy the function into the other cells. You should now see something like this:

Completed vlookup function, congratulations

By now you should understand the basic functionality of vlookup in excel. So let’s now look at a slightly more complicated example, which introduces the concept of closest-match. In the airport code example above, if you enter a code that is not in the lookup table, the function will return an error. For some types of data, closest-match gets rid of that problem. It is appropriate when you have a range of data all delivering the same result, for example days 1 to 31 in a year would deliver the month January, days 32 to 59 would return the month February (yes, assuming no leap year) and so on. Here’s an example.

Financial Quarter-End Accounts

Imagine you are a busy accountant coming to the end of the financial quarter. You have a list of transactions and need to extract only the ones from the first quarter of 2010. Can vlookup in excel help you here? It sure can, with a little help from the closest-match trick. Here’s the data and the reference table:

The finance problem

You can see the reference table just holds the value for the first day in the quarter. Using the closest-match variable within the vlookup excel function will deliver the correct value for the date within the range specified in the table. Let’s use cell D2 to type in the following function:

=VLOOKUP(A2, $F$2:$G$5, 2, True)

Examing this formula one section at a time:

A2 = the cell containing the value we want to lookup (in this case the transaction date)
$F$2:$G$5 = the reference table without the column headings, i.e. a square from cells F3 to G7
2 = the number of the column within the above range where the value to be delivered (the quarter) is kept
True = this means an exact match is not required and excel will use the closest-match functionality

When you press enter/return after typing the vlookup excel formula, you should see the following:

The solution to the excel problem using vlookup and closest-match

Success! Now as in the airport code example, simply hover the cursor over the bottom-right of the highlighted cell until you see the crosshair, click and hold, and drag down to copy the function into the other cells. You should now see the following:

The completed financial quarter problem