Related Tutorials
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.
=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.
Prefer a video? Watch the Vlookup function video tutorial.
 Select cell H7
 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

 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 
Show Me