If you only have a few records, you can just do them quickly by hand. What if you had hundreds or thousands of records? Well, the VLookup function can save you hours there. Here’s how….
You can either build your own vLookup example or purchase one we’ve prepared, there’s one for Access 2007 or late (xlsx) or Access 2003 and earlier (xls):
Purchase VLookup Spreadsheet Examples
To purchase the XLSX VLookup Spreadsheet Example (For Excel 2007 and later), click the button below:
To purchase the XLS VLookup Spreadsheet Example (For Excel 2003 and earlier), click the button below:
Excel 2013 VLookup Tutorial
So, in our example we have two worksheets, the first one is called ‘birthdays’ and has a list of names and dates of birth…
The second sheet, ‘Flowers’, has a list of the same names but with their favourite flower instead…
We want to cross-reference them to get this…
How do we do it? VLOOKUP! It is a really useful function found in Excel and Google Docs. Here’s how you do it…
Go back to the ‘birthdays’ sheet and add a new column header called Favourite Flowers in C1. We put the VLookup formula into C2. Go ahead and type =vlookup(
The VLookup formula has four elements:
- Lookup Value – The value you are cross-referencing
- Table Array – The list you are cross-referencing against
- Col Index Num – Which column in the Table Array you want to return given that the matching value is in the first column (more on this later!)
- Range Lookup – How good a match you want
In our example our Lookup Value ‘Alice’ is in cell A2, so we click on A2 or type in A2
Next we enter the Table Array which is the list we want to search ‘Alice’ for. In our example the Table Array is the list of favourite flowers in the Flowers worksheet. Click on the Flowers tab and then click and drag to select the list of names and flowers as below.
We then do one small extra step which is to add dollar signs ($) to the table array address, so that A2:B9 becomes $A$2:$B$9 as below…
Putting the dollar signs in the table array makes the cell references absolute. When you copy the formula down the list in the final step, the VLookup function will still be looking at the same table. You can find out more about relative and absolute cell references on the Microsoft website here, but it’s not necessary for now. Just remember to always put dollar signs in the cell references of the Table Array part of Vlookup.
Next we enter the Col Index Num (Column Index Number). This is the number of the column whose value we want to show in the formula’s result, in this case, the actual favourite flower. For Alice we can see that this is a rose (cell B8). If we take the first column which contains the names as column 1, then the flowers are in column 2, so we type 2.
Almost done! All that’s left is to tell VLookup how close a match we want. For an exact match (which is what you will almost always want) we type FALSE.
Now we type in a closing bracket, ) and hit RETURN and this gives us the completed formula. We can now see that Alice’s birthday is the 14th of Feb and her favourite flowers are roses.
To get everyone else’s favourite flower we use the drag-down tool in the bottom right corner of cell C2, as below.
And there you have it, you now know everyone’s birthday and their favourite flowers.
If you had hundreds or thousands of people in your list you would have just saved yourself a day or two’s pretty tedious work.
Our Take on VLookup
VLookup is a really useful tool. Cross referencing multiple sources of information is very useful. It is the mainstay of ‘Business Intelligence’. Powerful as it is, VLookup should be used with caution. The entry of spreadsheet data is often not controlled. This means that you may not be cross referencing like sets of data. Simple lists of data are not always what they seem. In the example above, imagine there were two clients called Ben. One who liked Chrysanthemums and the other who liked Roses. VLookup would have stopped at the first mention of Ben and said that they both had the same favourite flower. You would have sent the wrong flowers to the second Ben.
Sending someone flowers that aren’t their absolute favourites isn’t a major disaster. But what if it were something more important like a patient’s medical history, or a customer’s purchase history or a car’s service history. With these examples we can see how much more important it is to be precise with data.
If your business needs full and accurate reporting we would recommend that you leave spreadsheets behind and instead invest in a custom Microsoft Access database. Microsoft Access is a high-value, low-cost Rapid Development environment. Powerful reporting and bespoke business software is the perfect replacement for spreadsheets. Task automation saves you and your team time and frustration. Your team can concentrate on value-adding tasks rather than learning Excel.
Find out more about Upgrading from spreadsheets to a Custom Access Database.
Click here to find out more about how we help clients with their Access Database development.
Here are some other blog posts you may find interesting:
- Want to Delegate to your team? Give them the right tools.
- Hell is ‘Sharing a Spreadsheet’
- Spreadsheets vs Databases: Round 2 – Task Automation
Excel 2003 VLookup Tutorial
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:
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.
Save even more time and purchase our completed Spreadsheet Examples: