How To Use Vlookup in Excel - And Have Fun Doing It

Since I receive questions on how to use Vlookup in Excel on a continuous basis, I decided to write a fairly informative, yet concisely entertaining article on the subject (in any case, I hope that it's informative and entertaining).

Part one reviews the process of using the VLookup function within a simple Excel table. In part two you'll view an awesome video on how to use the VLookup Function. The lesson is taught by Richard Rost of Excel Learning Zone.com; he does a great job of explaining the process. I thought about creating my own video but it makes more sense to utilize a video that doesn't stink (I’m awful when it comes to creating videos). Part three walks through the process of using Named Ranges with the VLookup function.

By the time you complete this article (I'll try to keep you awake), you should have a fairly good understanding of how the Vlookup function works. This tutorial is direct toward those who use Excel 2003 or an earlier version. Let's get started.

1. The VLookup function is used to find specific data within an Excel Table of a spreadsheet.
2. VLookup can be used to find data on a separate worksheet (tab) beside the current worksheet (however, that capability is not reviewed in this tutorial ).
3. The first column of data should be sorted in ascending order.
4. VLookup has four arguments...

The Arguments Are: VLookup(lookup_value, table_array, col_index_num, [range_lookup])

Important Side Note:  The VLookup example that used in this tutorial only works when all of the data is contained on the same worksheet.  In other words, you can’t use this particular example to lookup data that’s contained on two different worksheets (looking up data on two different worksheets with VLookup will be covered in part two of this tutorial).

The VLookup function searches for a value in the left-most column of a table (the value in the left-most column is the Look-up value). Upon locating that value, it identifies the value to the right within the same row according to the column number that you specify (the column number is also known as the index number). In order for the VLookup function to work correctly, your data should be sorted in alphabetical order by the Lookup value (the value in left-most column of the table). Ok, now that I've given you the pompous explanation, I'll explain in English.

For example, based on the Excel Table Below:

The following Vlookup functions should be typed in any empty cell. For instance, if you entered the following vlookup function in cell C2:

=VLOOKUP(21001,A2:B11,2,FALSE)

The product name "Video Cards" would display in cell C2.

if you entered the following vlookup function in cell C3:

=VLOOKUP(21022,A2:B11,2,FALSE)

The product name "Keyboards" would display in cell C3.

Examples

=VLOOKUP(21001,A2:B11,2,FALSE) would return "Video Cards"

=VLOOKUP(21022,A2:B11,2,FALSE) would return "Keyboards"

=VLOOKUP(21096,A2:B11,2,FALSE) would return "Sound Cards"

=VLOOKUP(21199,A2:B11,2,FALSE) would return "Wireless Mouse"

In order to understand how the VLookup function works, let's examine the first example from the list above:

1). 21001 is the Product Code in the left column of the first row; this is the actual Lookup Value.

2). A2:B11 represents the range of data from cells A2 to B11.

3). 2 represents the column number to the right of the Lookup value; this is also known as the index number. If you wanted to identify a value in the third column, you would need to enter 3 as the index number.

4). Entering the word "FALSE" at the end of the function assures that an exact match will be found. If you were interested in finding an approximate match, you would enter the word "TRUE".

Ok, this may be a bit redundant but a little overkill never hurt anybody. As I mentioned in the introduction, Richard Rost does an excellent job of explaining the VLookup function visually. This is a great video.

At this point you're probably saying to yourself, 'so when does the fun start'. When I actually worked for a living, I read a lot of computer books; especially titles related to Microsoft Excel. If you're interested in becoming a VLookup expert, or an Excel aficionado, I highly recommend that you learn some basic VBA (Visual Basic for Applications). The following publications incorporate a number of simple, fun projects.  

Mr. Excel is actually two things; a community of Excel power users and an actual person. The Mr. Excel community exist to help end-users resolve any number of complex Excel issues. Mr. Excel himself, Bill Jelen, has been providing spreadsheet support for over 18 years. He's been an Excel consultant since 1998. I've been using Excel for many years and can say without trepidation that these guys are good! But don't just take my word for it, see for yourself.

In part one of the lesson "How To Use VLookup in Excel"; we used VLookup to return the value of a product code within an Excel table. In this lesson, we'll examine how to use Named Ranges in conjunction with the VLookup function. Named Ranges allow you to assign a name to a cell or a range of cells. For example, instead of referencing a cell range as displayed in the following function "=VLookup(21001,A2:B11,2,False)"; we could assign a name to the cell reference "A2:B11". In order to name a cell, or a range of cells complete the following steps:

Using your mouse, highlight the cell or range of cells that you want to name. In the lesson example, cells A2 through B11 are selected.

Click on the Insert menu option.Scroll down to Name; click on Define...

At this point the Define Name window will display. If you look toward the bottom of the Define Name window, you'll notice that your cell reference is displayed in the 'Refers to' input box.

Click on the input box under Names in workbook:' enter a name, and click on the OK button. In the lesson example, the name "Products" is entered.

Because you've applied a name to the cell reference A2:B11, you can easily access those cells by selecting the name from the "Name Box" located directly above column "A" to the left. Try it for yourself.

This is what the "Name Box" looks like:

In order to apply the Named Range that we just created, we'll utilize the example from the previous lesson. Based on the Excel table below, entering the VLookup function in conjunction with a Named Range would result in the following (as before, you'll need to enter the function in an empty cell):

• =VLOOKUP(21001,Products,2,FALSE) would return "Video Cards"
• =VLOOKUP(21022,Products,2,FALSE) would return "Keyboards"
• =VLOOKUP(21096,Products,2,FALSE) would return "Sound Cards"
• =VLOOKUP(21199,Products,2,FALSE) would return "Wireless"

As you can see, applying Named Ranges can simplify the process of accessing cell references.

That's it; you've come to the end of the tutorial and you didn't fall asleep. If you have any questions, fill free to post them in the comment section. Thanks!