How to use a Vlookup in Excel

The Vlookup function is part of the Lookup & Reference category. The v stands for vertical. Vlookup has a sister, it is called Hlookup. The h is for Horizontal, not Hookup. But when you need a hookup with your data, the v or the h will do a better job than you can with your eyes.

So where do you start? You have to be in a position where you need to look up information within a spreadsheet. For example, let’s say that you want to look up a product price if you know the product number.  If you had a data table that listed all the product numbers in the first column, then you could retrieve data associated with the product number such as Product Name, Product Type, and Price.

Excel Data

Before you start with the Vlookup, I suggest you create a table with the data. Start with selecting a single cell that is within your data range. Click the Insert tab, then click Table.

Create Table in Excel

Verify that your table has headers and the correct range is selected. Click OK.

Name the table. It will make things a lot easier when referencing the table from the Vlookup arguments. Name a table by using the Table Tools Design tab then clicking in the properties group to name the table.

Table Name Excel

Once you have named the table, navigate to a cell the you want to use the vlookup function. Usually it will be adjacent to a cell containing data with what you want to base your look up information on.


From the Formulas tab, click the Lookup & Reference category button, then choose Vlookup.

And the arguments:

Lookup_value: this answers what are you looking up? Nobody goes to google with out a search term already in mind. So it is with the vlookup, you need to know what you are looking up. This probably is a cell next door to where you are entering the formula.

Table_array: this answers where are you going to find what you are looking up? Write the name of your table. See instruction above to create and name a table.

Col_index_num: this anwers how you would like to reference the return value.  Enter a number here that is the column number from the leftmost table column. Using my example screen shots, to get the price of a product I would enter 4 because it is the fourth column within the table.

Range_lookup: this answers if you want to find an exact or an approximate match. Type false if you would like to literally search for an exact match. If Excel can’t find what you are looking for, it will return #N/A. Type true if you are searching for numbers and you want to find an approximate match. Note: if looking up numbers, the first column of the table needs to have the numbers sorted in ascending order. Also you shouldn’t have any duplicates in your first column of your lookup table.


When counting columns across, include the first column of your table.


After searching for product TE-59483 vertically withinn the first column of the product table, the Vlookup returns the value of $259. Thata boy Vlookup, your my data hookup!


Want to practice on your own with my Excel Workbook. Download my Vlookup-Practice-Training-File.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged , , . Bookmark the permalink.

One Response to How to use a Vlookup in Excel

  1. Pingback: How to Use the Index and Match functions instead of a Vlookup in Excel | Steve Chase Docs

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s