Have you wanted to find out how to use Excel's vlookup function? This function is used to extract data from a range of cells and display the extracted data in a different cell. This article describes the elements which make up vlookup and then explains what vlookup does and describes how to use this function in clear easy to follow steps.

What is vlookup ?

vlookup is an Excel function used to extract data from a cell within an Excel table and copy it to a different cell in the same worksheet, or in a different worksheet or workbook. The data in the table where the data is extracted from has as its first column an index column, containing one unique value for every item in the list. For example the index column could be a list of part codes in a parts list, or a list of staff IDs in a staff list.

The index can be numerical or text based, so long as every item has its own index value. The vlookup formula uses a value typed into a chosen cell which it uses to look for a match in the index column in the data table for a match in this value to establish which row of the table the match occurs in. vlookup is then told which column to move to in this row, to find the data to extract.

The data table vlookup checks in can be defined by the range of cells the data occupies, or alternatively given a range name. So for example we could call the data in the staff table the range name Staff1 rather than specifying the cells P20:T30. It's much easier to then refer to the cell range as Staff1 in the formula rather than to the actual cells range.

How to create a vlookup function

The vlookup function contains three parts, separated by commas, and looks something like this "=VLOOKUP(INDEX,RANGE,COLUMN)". The index is the cell reference where we have typed the value we want to lookup, for example in cell A2. The user then types the vlookup function into a different cell, for example in cell B2. Vlookup then checks in the cells defined by range and looks for a match in the first column for the value typed in A2 to find which row the match is in. Then it locates the column number specified by column in the function to find the cell with the data to extract, and displays this data in cell B2. Lets now look at an example.

Let's say you want to use vlookup to create a monthly payroll calculation. Suppose you have a list of all the staff in an Excel table located elsewhere in the worksheet, and you've already named the data in the table Staff1. This named range covers all the data in the table but not the headings. The first column in this Staff1 range is a list of each employee's staff ID number, for example ST1, ST2, ST3 and ST4. The second column is a list of the staff surnames, for example Smith, Jones, Green and Brown. The third column contains each member of staff's salary, for example £20,000, £22,000, £15,000 and £12,000.

You then want to create a payslip in the current worksheet, starting at cell A1. So in A1 you type the heading StaffID, in cell B1 you type Surname and in cell C1 you type Salary. You type in an employee ID into cell A2, for example ST1, then type a vlookup calculation into cell B2 to extract the actual Surname from Staff1 table. So in cell B2 you type =VLOOKUP(A2,Staff1,2) and press the enter key. Cell B2 then shows the employee surname Smith. If you try different values in cell A2, such as ST2 or ST3 you should find that the correct surname is extracted and displayed in cell B2.

You then add another vlookup formula in cell C2 to extract the employees monthly salary from the Staff1 table. The vlookup formula would be exactly the same as before but the column number would be 3 rather than 2. So in cell C2 you type =VLOOKUP(A2,Staff1,3) and press the enter key. You should now see the extracted salary. Try changing the lookup value in cell A2 to any of the staff IDs in the Staff1 table, and you should see the extracted surname and salary for that member of staff.

We've only really touched the surface here in exploring vlookup but hopefully this article has given you a brief insight into how this function can be used. If you're interested in learning more about Excel's vlookup and other functions, consider attending a training course. There are lots to choose from and the best ones can really boost your Excel skills.