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!

  

and

combined and cross-referenced to give

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 in Excel 365 (compatible with Excel 2007 and later):

Purchase VLookup Spreadsheet Examples

To purchase the XLSX VLookup Spreadsheet Example (For Excel 2007 and later), click the button below:

Follow the example below.

Excel 365 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:

  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

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:

  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