I know Excel gurus might laugh at me for writing this blog post. But whenever I am opening Excel, and need to join data from 2 spreadsheets, my stomach hurts. Excel can surprise you with“n/a” result for your formula without giving any explanation. Or it can return random values leaving you for hours to figure out why.
So I am writing this post partially for myself as a reminder and a reference on how to use VLOOKUP function for such a common task as joining data from 2 tables.
Let’s assume you have Contact data file with fields like first name, last name, email, etc. In the same file, you have 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 lookup field is the first column in company data sheet. This important, as otherwise VLOOKUP() will not work
- Now let’s pull a company name from company data sheet. To do so, create a new column “company name” in Contacts sheet. And populate first cell with the VLOOKUP formula. Mine looks like this:
- In human language, the formula above means that we use 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 lookup table and populate it into first sheet. Read this sentance 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:
- Now as the last step copy the formula to the whole column and enjoy the result.
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 cell data type is matching between lookup field and a lookup table. For example, if field type is number in the lookup field, but it’s a text in the lookup array, Excel might not match the fields correctly.
Hope it will save you some time.