How to do Vlookups in Excel 2010

As well as Vlookups being incredibly useful, at this present time this seems to be a benchmark of your Excel knowledge if you are looking for a new job – so good to know how to do it, even if you don’t have to use it.

A Vlookup stands for Vertical Look up and is used to find information from a list by basing it on the information you have already entered. For example, you enter a product code number into one cell, it can find the item description from a huge list of products and pop it into the next cell as in an invoice. It can do the same for the price, this means you only have to enter the product code once and everything else can be automatically populated.

Here’s how: For this I am going to use the example of an invoice. I only want to enter the product code and I want the corresponding information – price and description to be filled in from Vlookups.

  • Click on the cell where you want your Vlookup to go- for me ‘Product description’ (See 1st picture below).
  • Click on the formulas tab
  • Click on the fx – insert function button this will open up the insert function box.

Capture 1

 

  • You can type Vlookup in the search or select the category ‘Lookup and Reference’, select it and press ok.
  • The Vlookup box will open up and give you 4 boxes to complete:

capture3

 

  • Lookup_value – this is the data that you want to find the corresponding data about, e.g. the ‘Item’ on your invoice. Click on it to highlight it, this will put the cell number into the Vlookup box
  • Table_array – this is the name for the data you want to find the information in, the Vlookup searches the 1st column in this to find your search term in this instance the ‘Product code’ . This information can be on a separate sheet, in my example it is on the sheet called ‘Items list’. To use this data highlight it, this info will be put into the table_array box.

NB – Very important the data you want to reference, in my example the ‘Product code’,

  • Needs to be in the first column in your ‘Table_array’
  • Needs to be in order
  • Remove any gaps/blanks before starting
  • Col_index_num – this is to get the matching result you want from the ‘Table_array’ data you have selected. It is the number of the column, from left to right. It will never be column 1. e.g. I want the Description in my cell so I will choose column 2.

Capture 2

When you have entered all this info in you can see the result of the formula in the box. If it is not correct you can go back and change the formula, if it is click Ok.

If you wish to drag the formula down into the other boxes on your invoice you will need to make it an ‘Absolute cell reference’. After you have highlighted your ‘Table_array’ hit the F4 key, this will automatically enter the $ in the correct places.

This is covered in our Excel Advanced training course.

Share on LinkedInShare on FacebookTweet about this on TwitterShare on Google+Email this to someone

Related courses