Excel VLookup

The VLOOKUP (vertical lookup) function in Excel looks for a value in the leftmost column of a table, and then returns a value in the same row from another column of the table. Confused? The following example clearly illustrates the VLOOKUP function.

VLookup Example

The Excel file consists of 3 columns. One for the Name, one for the Product ID and one for the Product Name.

Question: how do we find the corresponding product names? Answer: We use the VLOOKUP function.

VLookup Table

The Codes sheet below shows the corresponding product names of each product ID. This is called the VLookup table.

VLookup Function

Ready to use the VLOOKUP function?

1. Select cell C3 on the Sales sheet.

2. Type in =VLOOKUP(

The VLOOKUP function has 4 arguments.

3. Excel first asks us for the lookup value. Select cell B3.

4. Second, Excel asks us to define the VLookup table (or table_array). Select range("A1:B6") on the Codes sheet (Don't forget to enter a comma first to let Excel know you are about to enter the second argument).

5. Third, Excel asks us to enter the column number of the VLookup table from which the value should be returned. We want to return the product name (which is stored in column 2), so we enter 2.

6. Enter FALSE for the fourth argument. Setting the range_lookup argument to FALSE requires the VLOOKUP function to find an exact match to the value entered in cell B3 or return an #N/A error.

Result:

Excel has looked up the product name of product ID 1. You might wonder, why we have done all this effort for this simple result. That's because we are now going to copy this function to the other cells. Read on.

Copy VLookup To Other Cells

Before we drag the function down to the other cells, we need to create an absolute reference to the VLookup table. Confused? You can go through our Cell References chapter to learn more about cell references. If you are in a hurry, just execute the following steps, and you will be fine for now.

1. Select cell C3, and place a $ symbol in front of the row numbers and column letters of the VLookup table address.

2. Click on the lower right of cell C3 and drag it down to cell C20 to copy the VLOOKUP function to the other cells.

Result:

Not bad huh!? That saves quite a lot of time!

Did you like this free Excel tutorial? Show your appreciation, vote for us.

Thank you very much! We've got many more free Excel tutorials for you. Read on, learn, and enjoy!

Go to Top: Excel VLookup    |    Go to Next Tutorial: Excel Lookup