How to cross reference spreadsheet data using VLOOKUP

You need to combine two lists into one cross-referenced list. Save yourself loads of time with the quick, simple and powerful VLookup function!

  excel spreadsheet with a list of names and dates of birth and excel spreadsheet with a list of names and favourite flowerscombined and cross-referenced to give

excel spreadsheet showing data cross referenced using the VLookup function

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:

Follow the examples below.  We have a tutorial in Excel 2013 or 2003, which would you like to see?  2013 or 2003

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…

excel spreadsheet with 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…

excel spreadsheet with a list of names and favourite flowers

We want to cross-reference them to get this…

excel spreadsheet showing data cross referenced using the VLookup function

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 formula structure of VLookup demonstrated in an Excel 2013 tutorial

The VLookup formula has four elements:

  1. Lookup Value – The value you are cross-referencing
  2. Table Array – The list you are cross-referencing against
  3. 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!)
  4. 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

The Lookup Value element in VLookup shown in the 2013 version of Excel

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.

The table array element of the VLookup function demonstrated in an Excel 2013 VlookupTutorial

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…

The Table Array in Excel 2013 VLookup tutorial

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.

Excel 2013 demonstration of The Col Index Num element of the VLookup function

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.

The VLookup function's Range Lookup element

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.

A complete VLookup function formula in Excel 2013

To get everyone else’s favourite flower we use the drag-down tool in the bottom right corner of cell C2, as below.

How to autofill all a column of VLookup functions using the drag-down tool

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:

  1. Want to Delegate to your team? Give them the right tools.
  2. Hell is ‘Sharing a Spreadsheet’
  3. Spreadsheets vs Databases: Round 2 – Task Automation

..back to top

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. Screenshot of spreadsheet list of dates of birth

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 Screenshot of spreadsheet list of favourite flowers

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. Screenshot of spreadsheet with equals sign typed into an empty cell

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. Screenshot of spreadsheet with start of VLookup function typed into empty cell

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. Screenshot of guide text in spreadsheet of VLookup function

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

Spreadsheet Data 6. Screenshot of Excel Insert Function menu command

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. Screenshot of Excel VLookup function dialog box

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. Screenshot of where to enter the first value in the VLookup dialog box

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. Screenshot of how to select the lookup value in VLookup

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. Screenshot showing VLookup lookup value pulled through into dialog box

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. Screenshot showing where to input the table array in the Excel VLookup function

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. Screenshot showing how to select a value table in a different worksheet in VLookup

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. Screenshot showing the notation for using a value table in a different worksheet in VLookup

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. Screenshot showing how to return the table array value to the VLookup dialog box

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. Screenshot showing the Table Array values pulled through into the VLookup dialog box

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. Screenshot showing the col index num in the VLookup function dialog box

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. Screenshot explaining the range lookup in the VLookup dialog box

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. Screenshot showing how to drag down a VLookup function.

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:


..back to top

4 thoughts on “How to cross reference spreadsheet data using VLOOKUP

  1. Sir.

    I have one question if their is same date of birth two different people how can we find out person name when we lookup the date of birth, please help me it’ll very useful for me.

Leave a Reply

Your email address will not be published. Required fields are marked *