Connecting Data Sets using VLOOKUP in Excel

In my dream world, the data management systems I use would allow me to extract data in the exact sequence and combinations I need. More often, I am able to extract only certain segments of data and then have to build bridges to connect those data sets outside of the original source.

Within the same or multiple workbooks, different data sets may be brought together using the VLOOKUP function. In this example, we reminisce to the lazy, hazy days of attending summer camp as a kid. Campers Becky, Joel, Vicky, Julia, and Anthony have signed up for various activities each day of the week. Their choices have been entered in the data management system as Activity Codes and are listed on the spreadsheet named Campers.


VLOOKUP_Excel_3In order to create a schedule for each camper for the week, the translation of the codes to the Activity name and Location will be accomplished using VLOOKUP. In a second sheet named Activities, the data is setup such that the Activity Code is in the first column of the data range. The Activity Code fulfills the first requirement in using VLOOKUP in that this is the common ID or key that exists in the each dataset. The second requirement is that the Lookup table is sorted by this ID or key.


To connect these two sets of data, the VLOOKUP formula will be entered in the Campers spreadsheet cell D2.



The first parameter needed to identify Becky’s Monday Activity is the Activity Code found in cell C2.

The next parameter is the address of the data containing the name and location of the date — the absolute reference Activities!$A$1:$D$6.

The next value is the column number of our target data, Activity in the 2nd column.

The last value in the VLOOKUP syntax will be FALSE. FALSE is used when an exact match is required. The alternative TRUE would be used if an approximate match was desired. I use FALSE almost 100% of the time. In fact, I’m not sure if I’ve ever wanted an approximate match and chose TRUE.

Using almost all of the same values, the Location may be filled by changing only the third parameter in the VLOOKUP formula. In cell E2 on the Campers sheet, we’ll type =VLOOKUP(C2, Activities!$A$1:$D$6,4,False). This will lookup on the same row of code AL001 in the Activities spreadsheet but will return the value in column 4 — Pool 1.


Copying the formulas to connect the data sets for all remaining rows will enable schedules to be prepared for all campers. Let the fun begin!


If you found this post helpful, you may subscribe to receive more great tips, tricks, and templates by email.


2 thoughts on “Connecting Data Sets using VLOOKUP in Excel

  1. Good post. I have used VLOOKUP in past Excel versions and found it worked great on small datasets. Have you ever run into a max record or row count where it gets too cumbersome? I’m sure it has improved in the later versions of Excel.

    1. Bob, thanks for stopping by. I haven’t personally run into a max record error in recent versions of Excel. I wonder if INDEX MATCH might work better in those larger spreadsheets. You have me wondering…might just have to do some experimenting.

Leave a Reply

Your email address will not be published. Required fields are marked *