Other Lookup & Reference Functions

Excel > Excel 2003 Intermediate > More Advanced Functions


Vlookup 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.

Vlookup example

  1. Select cell H7
  2. Click the Insert Function button on the Formula Bar. Type Vlookup in the Search for a Function field and press Enter. Select Vlookup from the list and click Ok.

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

Find Vlookup function
  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.

Vlookup function 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







Follow us on

Facebook  Twitter  You Tube