👇 Grab our practice workbook and follow along:

What is VLOOKLUP

Imagine you have a big book of names and phone numbers. If you want to find a person’s phone number, you first look for their name, then see the number next to it. VLOOKUP does something similar in Excel but with data.

VLOOKUP means Vertical Lookup. It is one of the most popular functions in Excel.

Understanding the Syntax of VLOOKUP

Before we go further, let’s simplify what VLOOKUP is made of (its syntax) and explain its parts.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value“: What you want to find.
  • table_array“: Where to look for it. The lookup_value should be in the first column of this range.
  • col_index_num“: This is the number of the column that has the information you want to get.
  • [range_lookup]: This tells Excel how you want to search. Use FALSE to find an exact match. Use TRUE to find something close if an exact match isn’t there (approximate match). If you don’t choose, Excel will look for an approximate match by default.

Step-by-Step Guide to Using VLOOKUP

  • What You’re Looking For (lookup_value): First, decide what information you need. It might be a product’s ID, someone’s name, or another unique detail.
  • Where to Look (table_array): Choose the part of your Excel sheet that has the information. Make sure it includes both what you’re looking for and what you want to find out.
  • Column Number for Your Answer (col_index_num): In the area you selected, count the columns from where your search starts to where your answer is. Start counting from 1. This number tells Excel in which column your answer will be.
  • Type of Search (range_lookup): Decide if you need the exact information you asked for (use FALSE) or if something close enough is okay (use TRUE). If you don’t choose, Excel will look for something close.

Basic Example: Finding a Company’s Name with VLOOKUP

Let’s say you have a list of customer IDs in Excel and you need to find the corresponding customer names.

But the customer names are located in a master data list, stored elsewhere in your workbook.

This is where you can use VLOOKUP. Click on the cell where you want the name to appear. Let’s say you choose cell D6.

Step 1: Start the VLOOKUP Formula

Let’s say you select cell D6. Begin by typing =VLOOKUP. After you type just a few letters, a list will appear and Excel will offer VLOOKUP as a suggestion. To use this suggestion, press “Tab” on your keyboard.

Enter VLOOKUP function

But don’t press Enter yet! You’ll see Excel is waiting for more information.

Step 2: Provide the Lookup Value

Now, Excel wants to know what you’re looking for.

Since we’re looking for the Customer ID in cell C6, you can use the arrow keys on your keyboard to highlight it. Or, you can use the mouse and click on that cell instead.

Next, type a comma,” (or a semicolon “;” if that’s what your regional settings require) to proceed to the formula’s next argument. So, you’ll get:

=VLOOKUP(C6, 

Step 3: Define the Table Array

Next, you need to tell Excel where to look for the customer ID and its name.

Find the master data list in your workbook, which, in our example, is between cells I3 and J14. Click and drag from I3 to J14 to select the list of company IDs and names. Then, type a comma.

Now, your formula should look like this:

=VLOOKUP(C6, I3:J14, 

VLOOKUP always searches vertically: It goes down the first column of the table you specify (in our case the customer ID), searching for the lookup value you’ve given in the formula.

Step 4: Enter the Column Number

Next, we need to show Excel where to find the customer name.

When using VLOOKUP, think of each column in the table array as having a number, starting from 1.

VLOOKUP only looks right: The value it finds and returns (in our case the customer name) must always be in a column to the right of the first column it searches through.

In our example, customer name is in the second column of our table. So, after the last comma, type the number 2, then add another comma. Now, your formula will look like this:

=VLOOKUP(C6, I3:J14, 2, 

Step 5: Choose Exact Match

VLOOKUP provides two ways to search: exact match and approximate match. Most often, you’ll prefer the exact match, especially for finding unique identifiers such as customer IDs or product numbers.

In our example, we choose “False” for an exact match. Because this is the final part of the formula, we close it with parentheses. Your formula should now look like this:

=VLOOKUP(C6, I3:J14, 2, FALSE)

💡 You can use zero (0) for an exact match and one (1) for an approximate match. Excel interprets 0 as FALSE and 1 as TRUE, making 0 and 1 simpler alternatives.

Once you press Enter, VLOOKUP will find the correct customer name (in our case, “Voltage”).

In short, VLOOKUP first looks down the first column. When it finds the customer ID “4706” (lookup_value), it goes to the second column (col_index_num) to get the customer name.

How VLOOKUP works

Step 6: Adjust Cell Referencing to Copy VLOOKUP Formula

This step is optional, but in our case, we plan to copy the VLOOKUP formula into the cells below. But if we just copy the formula from cell D6 and paste it into cells D7 and D8, we’ll end up with a “#N/A” error in D8:

Select cell D8 and presst F2 to edit the formula. You’ll notice that when the formula was copied and pasted, the table array moved down too. Now, the range has changed from I3:J14 to I5:J16.

This means that the customer number 4731 is no longer within the search range of the formula, resulting in the error.

To avoid this, we need to lock the cell referencing in D6 before we copy it to the cells below.

To fix this, go to cell D6 and hit F2 to edit. Inside the formula, move your cursor to the start of the table array (here, it’s I3) and press F4. This action adds “$” signs to the range, making it absolute.

Absolute cell referencing in Excel

Then, move your cursor to the end of the table array (here, it’s J14) and press F4 again. This converts the cell references to absolute, ensuring they stay the same when copied.

Your formula should look like this:

=VLOOKUP(C6, $I$3:$J$14, 2, FALSE)

With these changes, you can now copy the formula from D6 and paste it into D7 and D8 without issues. VLOOKUP will then give you the accurate results.

To understand better how cell referencing works correctly in Excel, including the meaning and use of the Dollar Sign, you can read more in this article here.

Common Pitfalls and How to Avoid Them

⚠️ #N/A errors

These errors happen if VLOOKUP can’t find the lookup_value within your specified range.

Check that the value is in your table_array and you’ve selected the right match type. Also, correct the cell referencing before duplicating the VLOOKUP formula to additional cells.

⚠️ Failing to Specify the Match Mode

Keep in mind, VLOOKUP defaults to an approximate search if not specified differently. Skipping the optional “range_lookup” argument means VLOOKUP assumes it’s TRUE, for an approximate match.

For precise searches, this default behavior might cause unforeseen errors and outcomes. Therefore, it’s wise to always define a value for range_lookup, usually “False” or “0”, to ensure accurate results.

⚠️ First match only

VLOOKUP has a limitation when dealing with tables that have duplicate entries. It can only return the first record that matches your search. This means if your table has several entries for the same lookup value, VLOOKUP will only show you the first one it finds, not any of the others.

To overcome the limitation of VLOOKUP with duplicates, XLOOKUP is a great alternative for newer Excel versions, offering more powerful features. It can handle duplicates more effectively by allowing searches in any direction and returning all matches.

For those without access to XLOOKUP, combining INDEX and MATCH functions is another strong solution, providing flexibility in searching and retrieving data from tables with duplicates. Both methods are explored in depth in dedicated articles for those seeking to master these techniques.

Unlocking VLOOKUP’s Full Potential

Learn more about VLOOKUP, including using wildcards and combining formulas, to make the most of it. Discover how to multiply or divide results for better insights by reading our detailed guide.

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.