How to cross reference spreadsheet data using VLOOKUP in Excel

Need to cross-reference two lists but there’s too many to do manually? Excel can help with the vLookup function

Want to add a cross-reference to your own spreadsheet with vlookup?

We’ll walk you through an example!

Imagine you have two lists.  They have one column in common.  That means you can cross-reference them.

Imagine your brother knew your nieces and nephews favourite football teams and your sister knew their dates of birth. 

Screenshot of two Excel lists to be cross-referenced

How to combine them?  vLookup!

Step 1: Add a new column for the cross-referenced data

In the first new row, enter the vLookup function.  It has 4 elements:

  1. Lookup Value (the value in this list that you want to use to cross-reference to the other list, i.e. K5).
  2. Table Array (the table you want to look up, in this example it is the list with the favourite teams, i.e. H5:I12 (add in the dollar signs to help with copying down the column later)).
  3. Col Index Num (The number of the column in the array you want as the answer.  The Favourite Team is in the 2nd column, so enter 2).
  4. Range Lookup (Almost always just enter ‘FALSE’ here).

Step 2: Copy down the column (or click'n'drag)

Because we put the dollar signs in the Table Array in the first row, we can copy down the column or click-n-drag using the handle.

Now all the values are cross-referenced and you know what to get everyone on their birthday!

Download A Prepared Example

If you would like to see a completed and formatted example of using vLookup to cross-reference data in Excel spreadsheets, just download our complementary spreadsheet.

Screenshot of spreadsheet with vLookup example for cross referencing data

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 you had two nephews called Ben. One who liked Liverpool and the other who liked Man Utd. VLookup would have stopped at the first mention of Ben and said that they both had the same favourite team. You would have sent the wrong present to the second Ben.

Sending someone the wrong calendar 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 Business Software Solution.

Find out more about how we assist clients with their MS Access Support requirements.

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

Sign up for Excel Help, Data Tips and Business Admin Advice

Subscribe

* indicates required

Working Data will use the information you provide on this form to be in touch with you and to provide alerts to new blog posts, updates and marketing. Please let us know all the ways you would like to hear from us:

You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at info@workingdata.co.uk. We will treat your information with respect. For more information about our privacy practices please visit our website. By clicking below, you agree that we may process your information in accordance with these terms.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.

How We Work

From a free consultation to ongoing support, find out the simple stages we take you through when you engage us to build your bespoke business solution. 

Automate Business Processes

Software can automate many manual and time-consuming processes.  Discover how processes in your business can be automated by software.

Who We Work With

Working Data have designed, built and supported bespoke business software for companies from all sectors and of all sizes.

4 Responses

Add a Comment

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