Need to cross-reference two lists but there’s too many to do manually? Excel can help with the vLookup function
We have a pre-prepared and complementary spreadsheet for you to download if you want to see it straight away
Want to add a cross-reference to your own spreadsheet?
We’ll walk you through an example!
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. How to combine them? vLookup!
Add a new column for the cross-referenced data
In the first new row, enter the vLookup function. It has 4 elements:
- Lookup Value (the value in this list that you want to use to cross-reference to the other list, i.e. K5).
- 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)).
- 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).
- Range Lookup (Almost always just enter ‘FALSE’ here).
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!
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.
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 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
Want some more tips and examples of how to use Excel to make your work easier?
Subscribe to our Newsletter for examples of Excel Spreadsheets, Business Admin Tips and Databases.