Other Lookup & Reference Functions

Excel > Excel 2007 Intermediate > More Advanced Functions

Vlookup Function

The Excel Vlookup function is a lookup and reference function. It searches for a record of data in a list and returns information from that record. For example, it could search a list of orders and return the value of an order that you ask for.

Vlookup stands for Vertical Lookup. It gets this name because it looks down columns. You would be right to assume that there is also an Hlookup that looks along rows.

Vlookup requires 4 items of information (arguments) in order to work.


Lookup value

The value to search for

Table array

The list of data to search for the value in. It looks for the value in the leftmost column

Col index num

The column number from the left of the data to be returned

Range lookup

Logical value that could be true or false.

This is an optional argument that if untouched is set to true. This requires the data list to be sorted by the leftmost column in ascending order. If the value is not found in the column then the closest match is returned.

If set to false, the data list can remain unsorted and an exact match is returned. If the value is not found a #N/A error message is returned.

Let's run a Vlookup on the stock sheet below in cell H7. When the code is entered in cell H4 the stock level will automatically appear in cell H7 as a result of the Vlookup function.

Download the Stock Sheet workbook to follow along.

Prefer a video? Watch the Vlookup function video tutorial.

An example of the Vlookup function

  1. Select cell H7
  2. Click the Formulas tab, and then Lookup and Reference from the Function Library. Select Vlookup from the list.

Once you get used to writing functions you may find it quicker and easier to type them directly into the cell

Inserting the Vlookup function into a spreadsheet
  1. The Arguments dialogue box appears asking for the information the Vlookup needs.
    1. The Lookup value is H4 as this is the cell that holds the value to search for.
    2. The Table array is $A$3:$F$9 as this cell range holds the data list that Vlookup will search in for the value. Vlookup searches in the leftmost column for the value, which in this case is column A. This range is made absolute.
    3. The Col index num is 5 as the Stock Level column is the 5th column along.
    4. Range lookup is false as we want an exact match.

Entering the arguments

  1. Click Ok
  2. Type a value in cell H4 to see the Vlookup update the cell containing its stock value

More examples of Vlookup being used on the Stock Sheet workbook:









Product E







Show Me

Follow us on

Facebook  Twitter  You Tube