How to cross reference spreadsheet files using VLOOKUP

In this month's tips we return to the Vlookup function which cross-references data. We look at combining data from different files in the context of a management task, preparing data for presentation.

Clients contacting Working Data to help them compile and compare data, often have their data spread over different workbooks, or files. This can occur when members of staff keep separate spreadsheets. The data in these spreadsheets can be cross-referenced, however, with the Vlookup function, as we will see in the context of a management task; Preparing data for a presentation.

Preparing data for a presentation

At Blooming Data, our flower shop, the accountant has returned our year-end figures. One of the spreadsheets shows the quantity of each flower we have sold in the last year. The manager is preparing a presentation showing how sales of each product have changed since the previous year. However, last year's figures are in a different spreadsheet. We can use the Vlookup function to bring all this data together into one manageable table.

Spreadsheet Data 1

Spreadsheet Data 1 shows the data from 2007. We want to pull through the figures for 2006, but in the 2006 spreadsheet (Spreadsheet Data 2) the products are in a different order, so we can not simply copy and paste. We could manually go through the list and type the figures in one at a time. Alternatively, we can let the Vlookup function do all that work for us and give us our presentation figures instantly.

Spreadsheet Data 2

In the 2007 spreadsheet, we write a heading into the column to the left of the 2007 figures, called 2006. See Spreadsheet Data 3.

Spreadsheet Data 3

We then enter the Vlookup formula into cell B7 which we will copy down the rest of the column. Remember from our December '07 newsletter that the Vlookup function has four elements, or "arguments".

• The value to be looked up. In this example it is A7 (Rose).
• The table we want to look it up in, here it is cells A7 to B13 in the "sales_product" tab of the spreadsheet called "2006.xls" in "C:" (see Spreadsheet Data 2). We put dollar signs ($) in front of each part of the cell address to make sure that the cells we are looking up don't change as we copy the formula down the column in a later stage. e.g. $A$7:$B$13.
• The column number of the value that we want the formula to return. In this instance it is 2. Looking at Spreadsheet Data 2, the 2006 spreadsheet, we see that column 1 is the name of the flowers, and column 2 is the quantity sold.
• This is a TRUE or FALSE option. We select FALSE as this forces Excel to find an exact match for the flower name.

In cell B7, we enter =VLOOKUP(A7,'C:\[2006.xls]sales_product'!$A$7:$B$13,2,FALSE)

This will pull through the number sold in 2006 for the flower in cell A7, Rose. See Spreadsheet Data 4.

Spreadsheet Data 4

510 Roses were sold in 2006. You can check this by looking at the data in Spreadsheet Data 2. We now copy the formula down the rest of the column. The figures for 2006 are pulled through and allow easy comparison with the 2007 figures. You can now add a formula which will show percentage change between 2006 and 2007 in cell D7 and copy this down the column to show the difference, as in Spreadsheet Data 5. These figures are now ready for presentation and will guide buying patterns for the year ahead.

Spreadsheet Data 5

You could combine this with a spreadsheet of costs and prices to show which flowers are giving you the greatest margins and bringing you the most profit.

If you have any questions about the Vlookup function and when to use it, or if you would like to suggest a topic for us to cover in a future newsletter, please contact us.

..back to top