How to cross reference spreadsheet data using VLOOKUP

As we plough on towards Christmas, the end of year figures start to loom ahead. Often our data needs collating at year end for the accountant and the exchequer. When you have information spread across several different spreadsheets, it can seem a daunting task to bring all these different sets of data together into one meaningful list or table. This is where the Vlookup function comes into its own.

It is used a lot in business analysis to compare and contrast figures from different sources. For instance, if you have two worksheets, one showing the address details for each customer, and the other showing the number of orders for each customer, Vlookup will combine these two pieces of information quickly. Thus allowing you to see such information as which geographical areas are generating the most orders, who are my key customers, whose order volume has changed significantly, etc..

Let's look at a very simple example of cross-referencing two spreadsheets. Each spreadsheet contains information about the same group of people. The first spreadsheet has their dates of birth, and the second shows their favourite flower. How do we build a list showing the person's name, their date of birth and their favourite flower? We use Vlookup.

In Spreadsheet Data 1. we see the first spreadsheet which contains the dates of birth. In this example we are going to cross-reference data on two different worksheets within the same workbook.

The first worksheet is called "DOB".

Spreadsheet Data 1.

In Spreadsheet Data. 2 we see that the second worksheet, "flowers", has the same names and lists each person's favourite flower.

Spreadsheet Data. 2

So, we have two different lists on two separate worksheets which have something in common, the people's names. We can use that as the point of reference to tie the two pieces of information together, as we would when doing the same task manually.

We return to the initial worksheet "DOB" in Spreadsheet Data 3. to perform the first step of the cross-reference, typing the Vlookup function.

Row 1 contains the titles for the columns, i.e. "Name" and "Date of Birth" The first line of data is the entry for Alice, in cells A2 and B2. We go to the first empty cell in that row, C2. To inform Excel that we are entering a function, and not a line of text, we use the prefix "=" (the equal-to sign) as in Spreadsheet Data 3.

Spreadsheet Data 3.

Continuing on, we then type the name of the function we wish to utilise. In this case, Vlookup. So, in the cell C2, after the "=" (equal-to) sign, we type "Vlookup", as in Spreadsheet Data 4.

Spreadsheet Data 4.

Next, still in cell C2, we type a left bracket "(" (using SHIFT + 9). Excel brings up a line of text to guide us. See Spreadsheet Data 5. This shows the four parameters required for the function to work.

Spreadsheet Data 5.

To get help filling out the four parameters required, go to the INSERT menu and select Function,
see Spreadsheet Data 6.

Spreadsheet Data 6.

This will start a dialog box, see Spreadsheet Data 7., which lists each of the four values required for the Vlookup function to work. The values required to satisfy a function are called its 'arguments'. These are:

1. Lookup_value
2. Table_array
3. Col_index_num
4. Range_lookup

Spreadsheet Data 7.

Let's start with No.1, the Lookup_Value. This is the value we want to look up against in the other table. In this instance, the value is "Alice". We select the cell that the name "Alice" is in, in this case, A2. We can either type A2, or use the cell selection tool (the box at the right edge of the cell which has a small red arrow at its centre). To use the cell selection tool, click on the button as indicated by the black arrow in Spreadsheet Data 8.

Spreadsheet Data 8.

This will hide the Vlookup dialog box temporarily and bring up a single row box, with the heading of "Function Arguments". Now click (and simultaneously) select the relevant cell, in this case A2 as it contains the name "Alice". See Spreadsheet Data 9.

Spreadsheet Data 9.

Now, to return to the Vlookup dialog box, click on the button as indicated by the black arrow. This will re-open the dialog box, and take the value of "A2" through and put it in the Lookup_value field. See Spreadsheet Data 10.

Spreadsheet Data 10.

Reading the Lookup_value row in the dialog box, we see that the cell value of A2 has been pulled through, and to the right of this, we see that Excel recognises that the value of cell A2 is "Alice".

Next we move on to Table_array. This is the table that we will lookup against. We select the table in the same way as we selected the Lookup_value, by clicking on the small button to the right of the field, indicated by the black arrow, see Spreadsheet Data 11.

Spreadsheet Data 11.

This will bring up the same single row box as before. Next, we select the table of data that we want to cross-reference with the names in the 'DOB' worksheet. This is on a different worksheet, so the first thing to do is to click on the worksheet tab, as indicated by the black arrow. See Spreadsheet Data 12.

Spreadsheet Data 12.

In the dialog box, you will now see that the worksheet name "flowers!" has been put into the box for you, as in Spreadsheet Data 13.

Spreadsheet Data 13.

Next we select the cells in the "flowers" worksheet which contain the data we want to cross-reference with the names in the 'DOB' worksheet. Then we click on the button as indicated with a black arrow. See Spreadsheet Data 14.

Spreadsheet Data 14.

The worksheet name and the cell references are pulled through into the dialog box. We need to modify the cell references a little. We have to put a dollar sign, '$' in front of each individual part of the address, as in Spreadsheet Data 15.

Spreadsheet Data 15.

Now we move on to Col_index_num. This tells Excel which column contains the answer. We have two columns in the Table_array, A and B. The first column, A, has the name which we are using for the cross-reference, and the second column, B, contains the favourite flower, so we simply type a "2" into the main dialog box, to select the second column, B. See Spreadsheet Data 16.

Spreadsheet Data 16.

The last value to enter is the Range_lookup. We type "false" into the dialog box as we want Excel to find an exact match. The dialog box should look like Spreadsheet Data 17.

Spreadsheet Data 17.

Now we click OK, closing the dialog box. The function has pulled through the answer of "Rose" for Alice. The last step is to drag the formula down for each person, see Spreadsheet Data 18.

Spreadsheet Data 18.

Type "Favourite Flowers" into cell C1, and now you have a table which shows the person's Date of Birth alongside their favourite flower. So, if you wanted to give a client a nice surprise you know what to send and when.

As you can see, if you have a large amount of data which requires cross-referencing, Vlookup can save you large amounts of time. If you have any suggestions for future Spreadsheet Tips or would like some assistance with those areas we have already covered, please contact us.

..back to top