Newsletter, Dec. '07

Spreadsheet Tips		Meet the Client

The Vlookup function is a fast and powerful time-saving tool for cross-referencing tables and data from different sources.		Meet Working Data client Starlight Enterprise, the Oxford based organisation who support and help young people to embrace a positive outlook to learning, work and life in general.


Meet the Client - Starlight Enterprise

		Starlight is the brainchild of Sue Funge and Glen Berley. Their core belief, and the foundation on which Starlight rests is that "We are all �stars' and that, if we remain true to our inner self, we will radiate this light and be who we are capable of being."

It is usually the case that most of us need some friendly guidance along the way to help us realise our potential to enjoy life and become a productive member of our community. This is especially difficult for those members of our community against whom there is a legacy of prejudice, particularly, children of mixed race. Sue and Glen have developed a series of seminars and workbooks which are used to guide youngsters towards valuing themselves, their communities and their contribution. "We have a lot of client and prospect data, including attendance records and documentation for our workshops and seminars. Working Data helped us take control of our data right from the start, asking lots of questions and helping us decide what we really wanted. Our historic data was spread across several spreadsheets and in emails. Working Data took it all away and brought it back to us in a neat and simple system on which we now rely. It has saved us so much time and helped us progress our business forward."

Find out more about the workbooks and courses offered by Starlight, and for those of you in the Oxford area, give your support to Starlight BCMHG.

back to top


Spreadsheet Tips - Using Vlookup to cross-reference tables.

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 Fig 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".

Fig 1.


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

Fig. 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 Fig 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 Fig 3.

Fig 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 Fig 4.

Fig 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 Fig 5. This shows the four parameters required for the function to work.

Fig 5.



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

Fig 6.



This will start a dialog box, see Fig 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: Lookup_value Table_array Col_index_num Range_lookup

Fig 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 Fig 8.

Fig 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 Fig 9.

Fig 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 Fig 10.

Fig 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 Fig 11.
Fig 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 Fig 12.

Fig 12.



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

Fig 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 Fig 14.

Fig 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 Fig 15.

Fig 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 Fig 16.

Fig 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 Fig 17.

Fig 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 Fig 18.

Fig 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