Join Multiple Data Sheets in Excel Using VLOOKUP Function

Mark Kofman
Apr 14, 2018 · 3 min read

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

=VLOOKUP(D2,’company data’!A2:B3,2,FALSE)

  • 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

=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 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.

Import2

Import2 blog can now be found at: https://blog.import2.com We hope to see you there!

Mark Kofman

Written by

Proving that data is more important than software

Import2

Import2

Import2 blog can now be found at: https://blog.import2.com We hope to see you there!

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade