Inspiration taken from this article on Medium
Let’s assume you have a Contact data file with fields like first name, last name, email, etc. In the same file, you have a company id that is a unique identifier of the company. But all your company data is in a separate Company file.
VLOOKUP to the rescue. Let us go ahead and merge company data from Company file into the Contacts file in few simple (almost) steps:
Start by copying Company sheet from a separate file into Contacts file
Make sure that the lookup field is the first column in company datasheet. This important, as otherwise VLOOKUP() will not work
Now let’s pull a company name from the company datasheet. To do so, create a new column “company name” in the Contacts sheet. And populate first cell with the VLOOKUP formula. It looks likt this:
=VLOOKUP(D2,’company data’!A2:B3,2,FALSE)
In human language, the formula above means that we use the value in cell D2 to lookup corresponding row with the same value in a lookup table A2:B3, when found we take column 2 in a lookup table and populate it into the first sheet. Read this sentence 3 times to make sure you follow :) trust me, it will save you many hours later when you try doing this at home.
We are almost there, but before copying the formula to the whole column, make sure you have locked the table array using $ sign as following
=VLOOKUP(D2,’company data’!A$2:B$3,2,FALSE)
Now as the last step copy the formula to the whole column and enjoy the result.
Using VLOOKUP() function to join data from 2 spreadsheets
Some More Tips
Make sure the ID column is first in the lookup table
Always, always, always set “FALSE” in the last parameter of the VLOOKUP field. The parameter has a very unclear name “range_lookup”. What it really means is that if you set it to FALSE, then Excel will try to find an exact match. For some strange reason, default value here is TRUE and creates unexpected results
Make sure that the cell data type is matching between the lookup field and a lookup table. For example, if the field type is number in the lookup field, but it’s a text in the lookup array, Excel might not match the fields correctly.
We hope it will save you some time.