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 you knew your nieces and nephews favourite football teams and your spouse knew their dates of birth.
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:
- 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).
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 surprisingly inexpensive 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 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:
Sign up for Excel Help, Data Tips and Business Admin Advice
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.
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.
Hi Arjun,
Thanks for your question.
If you have the date of birth in one column and the name in another you could use a filter on the date of birth column and this will only show the people with that date of birth.
You may find this post about filtering on the MS Office site useful.
https://support.office.com/en-au/article/Filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e
Best of luck!
What if you need to compute age in Sheet “Flowers” basing from the date of birth at sheet “DOB”?
Hi Sarah, the formula to calculate current age from date of birth is:
=ROUNDDOWN(DAYS(NOW(),A2)/365,0)
with the date of birth in cell A2.
Hope that helps!