The VLOOKUP function is one of the two most popular lookup functions in Microsoft Excel. Almost all serious spreadsheets can barely survive without this function in action.
You should already know by now, how important this function is in the life of a spreadsheet user.
As important as it is, most beginner Excel users seldom understand how VLOOKUP works and thus misunderstood it as something complex. This confusing stem from the fact that this function seems not so useful in the immediate sense.
Don’t you worry! With real-world examples, I’ll help you learn how to use VLOOKUP in Excel.
In a very plain and simple language, you’ll understand how VLOOKUP function works and in what situation it becomes indispensable.
You can use the table of content below to navigate your way through this VLOOKUP tutorials.
Table of Contents
Introduction: What is VLOOKUP?
Obviously, VLOOKUP is an Excel function.
But how does VLOOKUP work or what does it do?
With regards to the work it does is also very simple: to find and retrieve peace of information in a data set. It does that by looking up a value in the first column of the data set to return a value in another column from the same row.
In the merest terms, the VLOOKUP function command Excel to do something like this: “Look for this piece of information (e.g. Employee Name), in this data set (Employee table), and tell me something related to it (Employee Salary).
Is this explanation still vague?
Never mind! It’ll make more sense after studying the Examples later in this tutorials.
The VLOOKUP syntax
Of course, The VLOOKUP also has its syntax just like any other Excel Function. Below is the VLOOKUP syntax followed by a detailed explanation.
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP arguments are as follows:
- lookup_value: This is the value Excel will search for in the first column of the data set (lookup table).
- table_array: This refers to the rectangular grid of cells (Lookup Table) that contains all the data you’re searching through.
- col_index_num: This specifies the column in the table from which the looked up value will be retrieved.
- range_lookup: This argument is OPTIONAL. If TRUE, an approximate match is returned. If omitted, also an approximate match is returned. But if false, an exact match is returned. And with the Exact match, if an exact value is not found, the function returns an error.
If you are not so familiar with Excel functions you probably won’t understand this concept. But I promise everything will come to light with the examples below.
Excel VLOOKUP examples
It’s now time to look upstream – bite the bullet – and jump into action. Most beginner Excel users benefit a lot from hands-on examples.
Usually, a well-thought example explains a concept much better than a description of the underlying theory.
Therefore, instead of walking you through a painful review of every facet of the VLOOKUP function, I try to use below examples to explain how VLOOKUP works and how it can be used in a variety of situations.
This is a “Learn as you go” approach. So make sure to go through all the examples presented here.
VLOOKUP Example 1: Finding Employee Salary
The table below has the employee names in the first column of the data followed by other corresponding records to the right.
In this example, we’ll use the VLOOKUP function to retrieve the salaries of employees in the data using the employee names.
Follow the steps carefully.
Pick the formula cell
Specify the cell that will contain the VLOOKUP formula. This should be the same cell that will display the salary.
In this example worksheet, Cell B3 is used to enter the formula.
NOW Insert the VLOOKUP formula
The formula to lookup for the name “Noah Ava” is stated below:
Press the Enter or Tab key after typing the formula.
See the results below:
VLOOKUP EXAMPLE ONE EXPLAINED
Let’s consider how the VLOOKUP was able to retrieve the employee name “Noah Ava”.
The formula says:
According to this example, the “Noah Ava” part of the formula represents the lookup_value argument. This is the Value Excel will search for in the first column of the dataset.
The A5:E18 part of the formula represents the table_array argument. This second argument is the most important. It is the range of cells that contains all the data you’re searching through. It represents the table of data that has the employee details.
This example has 13 rows (from row 6 to 18) and 5 columns of data (from column A to column E). The first column in this range must be the column with the data you want to locate (in this case, the Employee Name column). The remaining columns have other related columns from which Excel is going to retrieve data once it finds a match.
The third argument, 5, represents the col_index_num argument. This number specifies the column from which to retrieve the lookup value. Thus, 5 represents the Annual Salary column.
Finally, the FALSE part of the formula represents the range_lookup argument. This specifies what match type to use: an exact match or an approximate match. False means you’re insisting on an Exact Match to ensure your formula retrieves the correct salary.
The approximate match of the VLOOKUP function is explained in example two.
Now let’s have a bird’s-eye view of what really happened.
First, the function looked through the first column of the table in search of an employee named Noah Ava. Once Noah Ava was found, it quickly spot the fifth column of the data and retrieved the value in it.
Very simple huh!
Truly, you can also adjust the formula a little more to fetch any employee salary as well as the address, occupation or hire date.
For example, to find the salary of a different employee, just replace Noah Ava with the employee name, and his or her salary will be more than glad to present itself to you.
Thus, below is the formula to retrieve Daniel Chloe’s salary:
Now adjust the formula to display this employee’s position or occupation by changing the 5 to 3. This means the VLOOKUP function is no more looking and fetching data from the fifth column but from the third column of the dataset which represent the occupation column.
Now here’s the adjusted formula that retrieves “Daniel Chloe”‘s occupation.
Using Cell Referencing in VLOOKUP
Just take a close look at the formula in example one and you’ll notice that the value we are looking for is in double quotes.
It is also possible to use cell referencing to specify a lookup value. For instance, instead of typing the lookup value in double quotes into the formula, you can refer to another cell whose value will be used as the lookup value.
In the formula below, cell B2 is used as the lookup value. Meaning any name that is typed into cell B2 will be used for the search.
See the formula below:
When the Value in cell B2 is changed, the lookup value in the formula also changes as shown below:
Using cell referencing in formulas like the VLOOKUP is very handy and flexible as it let you easily change the lookup value in the referenced cell without touching the formula.
That’s why in the remaining examples, we’ll be using cell references as the lookup values.
VLOOKUP Example 2: Finding tax rate for various earnings.
Did you know that employees pay a certain percentage of their income to the government as income tax?
Did you also know that the amount paid as income tax differ from one employee to another depending on the employee’s earnings?
The higher the earnings, the higher the percentage of income withheld for taxes.
Well, that’s how it works. I just thought you should know.
The VLOOKUP function is commonly used by accountants to prepare income tax rate schedule. This schedule shows the income tax rate for various income levels.
(See screenshot below)
Now let the VLOOKUP do what it does best! Below is the formula for this example:
(See screenshot below)
What this formula does is very simple. It returns the tax rate for the income in cell C4.
The range of the lookup table has three columns with the third column being the Tax Rate column. Since the goal of the formula is to return the tax rate for various earnings, the third column is specified by the number three.
Experiment with a different Income in cell C3 to get a feel of how the tax rate for that income level will pull out in cell C4.
Approximate Match explained
If the match type value is set to TRUE, or if this argument is omitted altogether, it means you are willing to accept an approximate match for your lookup.
Unlike the exact match which calls for an error when a value is nowhere to be found, the approximate match tries to guess a matching value base on certain criteria.
And the criteria goes like this:
Excel attempts to find a salary that matches exactly to the values in the salary column (being the first column of the lookup table). If found, then BINGO. But if it can’t, it uses the next largest value that is less than the missing lookup value.
For instance, in this example, when you enter an income of $3,399, Excel will search for this value in the salary column (first column). But since there’s no such salary amount, Excel will then said, which among the salaries is next to $3,399 in rank? In other words, which among the salaries is the largest value that is less than $3,399?
When you take a careful look at the data, you’ll find out that the $3,000 is the answer as it is the largest value that is less than $3,399 in the first column of the lookup table.
But note that the middle or second column in this example is there as a helper to make the salary groups. Therefore, the formula does not in any way get the tax rate base on the values in this column.
This is exactly how the VLOOKUP work for the approximate match type.
VLOOKUP Example 3: Using VLOOKUP to find product Name and Price
If you have some experience working with any good accounting software, then you already know how effortless producing invoices for customers can be.
The products alongside with prices are tied together in an interesting way. So when you select or type a product ID, the name, as well as the price of the product, automatically show up without you having to type them yourself.
Such a result can be achieved in Excel using the VLOOKUP function.
Consider this Example:
The first table is a catalog of products with three columns: ID column, name column, and Price column.
The second table is a simple invoice with five columns including the product ID column.
Our goal here is to use VLOOKUP function to retrieve an item from the product catalog base on the product ID. This way, users need not type out full product names or prices.
Here’s the VLOOKUP for the product column that retrieves the product name base on the product ID:
The formula for the price column is the same except that the column offset is 3 instead of 2:
Below is a screenshot of the above formulas in the product and price column of the invoice.
Now once these formulas are entered in their respective cells, you’ll need to copy them down to remaining cells to complete the invoice template. Therefore, let’s do so and see what happens.
Looking at the last two entries of the invoice, both the product and price formulas has a #N/A error message.
But why did some of the copied formulas encounter those errors?
This brings us to the next topic.
Using Absolute and Relative Cell referencing in VLOOKUP
Those errors occurred because of relative cell referencing.
When copying formulas from one cell into another using relative cell referencing, Excel updates the formulas automatically.
It is a true convenience using relative references since they make it handy to copy formulas that don’t need the slightest bit of editing.
However, example three already made it clear to us that relative references don’t always work. That’s why you need to introduce the $ sign into your formula.
Now back to our little invoice.
Since the formula in the product, as well as the price column, was relative references, as the formula was being copied down the cells, Excel was busy updating the formula as well which leads to the #NA error in the last two entries.
In the formula, the first and second arguments are pointing to a cell reference.
Looking at the formula in the products column, for instance, the first argument, which represents the lookup value, point to a cell reference (G5). The second argument also points to a cell reference (B5:D14).
Here, a relative reference is needed for the first argument (G5) to update itself as the formula is being copied down the cells. Because as you copy the formula to the rest of the cells, the G5 reference in the first row of the invoice needed to change to G6 reference in the second row, and in that order to the final row.
The second argument, however, doesn’t need any relative cell reference because it doesn’t need to change as you copy the formula to the remaining cells. The price list is a table stationed to some fixed range of cells that doesn’t change whatsoever.
Now, what does it need?
It needs a relative cell reference which will stop it from updating when the formula is being copied.
To make this cell reference absolute, highlight it (the second argument. i.e. B4:D14) and press the F4 key on your keyboard. This should insert a $ sign before each row number and column letter.
Now these are the updated formulas for both columns:
Formula for the product column:
The formula for price column:
Now that the formulas are corrected, try copying them down the remaining cells and see whether or not you’ll get the annoying #N/A error again. Now everything should work just fine.
Pro tip: Using Named ranges in formulas
Whenever there’s a need for you to use an absolute reference like this one in this example, the easiest way is to name the source cells (that is, the cells being referred to). This way, you can use the name instead of the cell reference.
Yes, it is possible to build formulas using descriptive names or named ranges. All you have to do is define the ranges; then you can use the names in your formulas instead of typing the difficult to read cell reference.
To demonstrate that, let’s name our products list table and use the name in our VLOOKUP function.
To name a cell or group of cells, just follow these steps:
- Select the cell or cells you want to name. It is possible to name a single cell or an entire range of cells.
- Just below the ribbon, at the left end of the formula bar, there’s a box which indicates the address of the active cell. This is called the Named Box. Delete the text inside (active cell address) and type the name you want to use for your selected cells.
- Hit the enter key to insert the new name.
Remember, when naming cells in Excel, never use space between words. This results in an error. For instance, don’t type Product List, type ProductList or Product_List instead.
Now that we have a name to the product list table, let’s make good use of it in our VLOOKUP.
This is very simple. Since the product table is the source cells for the second argument (that is, the cells being referred to), instead of using the cell reference (, use the name you just assign to the table (ProductList).
Use =VLOOKUP(G5,ProductList,2,FALSE) instead of =VLOOKUP(G5,$B$5:$D$14,2,FALSE)
Named ranges can be particularly handy when using cell referencing in different worksheets (or even different workbooks)
How to do VLOOKUP from another sheet
In most cases, VLOOKUP is not used to find data in the same worksheet. The function is always used to look at and retrieve data from another worksheet.
Therefore, in this example, I’ll copy the product catalog table to a different worksheet so that we can create a formula that will look up and retrieve the product names and prices from another sheet.
The goal here is to create a VLOOKUP formula that will scan through some data in one sheet (product list) and retrieve product names and prices to another sheet (Invoice sheet).
To enter such formula very easily, please follow these steps:
- Type the formula and specify the cell whose value you want to look up.
- Now switch to the product worksheet and select the range of cells as your lookup table (table_array)
Remember to make the second argument an absolute reference. This will help reduce errors.
- Now the complete formula should look like this:
- Copy the formula to the rest of the cells.
Remember to use named ranges whenever possible.
Thus, to use a name in your formula, switch to the product sheet and assign a reasonable name to the product table (e.g. ProductList). Then use the name in the formula. This way, your formula will be easy to understand.
Also, you don’t need to append the cell reference with the sheet name. All you need for the formula to work is the table name (ProductList).
Example Four: Do it yourself
This example is more or less a test for you. As I mentioned before, learning by example has been proven as the best way to learn.
In this example, I’m presenting you the data. It is left for you to give life to it by inserting the formulas.
It goes like this:
This final Example is a worksheet that needs the VLOOKUP function to enable the end user to easily explore more information about employees contained in a long list of employee information.
The idea here is simple. On top of the spreadsheet, we want to offer some kind of interactive information dashboard so that anyone can enter an employee’s number or ID number, and then be able to view some (or all) information about the employee (like the hourly rate of the employee).
The aim of this example is to teach you how to get a customizable view for a large amount of data.
Now let me give you a blow-by-blow breakdown of what just happened here.
The focused cells here are cell D2, cells F4 through F8 and Cell F10. D2 is where whoever is viewing the data types in the product ID.
F4 through F8 and F10 all contain a different version of the VLOOKUP function, which retrieve the employee name, address, hire date, hourly wage, hours worked, and taxes and deductions, respectively.
The calculations, which are pretty straightforward, actually take place in two cells only: Cells F9 and F11. These two cells don’t need any version of the VLOOKUP – they simply use the values from the lookup cells to perform some simple calculations.
Now, if the user types the employee ID 50 into cell B2, for example, each of the VLOOKUP function cells finds the requested row and then retrieve the data they’re designed to retrieve. In other words, the information dashboard updates and recalculates whenever there is a change in employee ID.
True, it’s possible and fairly easy to use the retrieved information in other ordinary calculations (as seen in cells F9 and F11).
Why VLOOKUP not working!
Considering how powerful it is, the VLOOKUP () function isn’t flawless. It still suffers from a problem. This problem already had come to light in the process of creating our simple invoice in example three.
#N/A and #REF!, mostly, are the errors you see when VLOOKUP is not working.
This error is normally caused by a mismatch of data. Though, it’s not only one reason that could trigger the “VLOOKUP not working” puzzle. Wrongfully writing the formula can also cause it.
I’ll, therefore, highlight some of the known causes of these errors using the above examples we’ve discussed so far.
Causes of #N/A Error in VLOOKUP
As written, the VLOOKUP function isn’t perfect.
Let’s take a close look at the simple invoice for instance. Delete some of the content in the products ID column, and you’ll notice this unexpected #N/A error message in each row where there isn’t a product ID.
This error message is as a result of the VLOOKUP attempts to match a product with a product ID of 0, which doesn’t exist. This is just one of the causes of the “VLOOKUP not working” problem.
Dealing with Blank Values in VLOOKUP
If for some reason, the lookup value, like the products ID in this example must be blank, then you’ll need a way to make this error disappear.
To handle this error, using a conditional logic to see whether a product ID has been entered can do the trick. If it hasn’t, you can put a blank value or a dash sign in the product name or price column respectively. But if it has, the VLOOKUP function is executed to get the product name and price respectively.
Here’s the formula to perform this logic:
Product column: =IF(ISBLANK(G5),””,VLOOKUP(G5,ProductList,2,FALSE))
For the price column too, you can display a dash (-) or a zero value as an empty cell as seen in the formula below.
Price Column: =IF(ISBLANK(G5),”-“,VLOOKUP(G5,ProductList,3,FALSE))
Now the invoice should work just fine without product IDs.
Dealing with incorrect values in VLOOKUP
The ISBLANK() function helps when there are empty rows to show the right information. What if the spreadsheet user enters a product ID that doesn’t correspond to any product in the list?
You know it already – the familiar #N/A error is the result.
Of course, the error disappears as soon as the spreadsheet user fixes the problem by typing a valid product ID. Sometimes it is not that easy.
Take a look the below example.
This kind of error takes more than just the eyes to see. Even though the product name can be spelled correctly, but the additional space before or after the product can result in an error.
If you have any more issues on how to use VLOOKUP, please make it known in the comments or shoot me an email addressing the problem.