## Related Tutorials

# Vlookup

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.

*=vlookup(lookup_value,table_array,col_index_num,[range_lookup])*

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.

- Select cell H7
- 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

- The Arguments dialogue box appears asking for the information the Vlookup needs.
- The Lookup value is H4 as this is the cell that holds the value to search for.
- 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.
- The Col index num is 5 as the Stock Level column is the 5th column along.
- Range lookup is false as we want an exact match.

- Click Ok
- 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:

Code |
Example |
Result |
---|---|---|

C132 |
=Vlookup(H4,$A$3:$F$9,6,false) |
£349.30 |

C134 |
=Vlookup(H4,$A$3:$F$9,2) |
Product E |

C138 |
=vlookup(H4,$A$3:$F$9,5) |
18 |

C134 |
=Vlookup(H4,$B$3:$F$9,5,false) |
#N/A |