Tag Archives: VLOOKUP

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_1

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.

VLOOKUP_Excel_2

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

 

VLOOKUP_Excel_4

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.

VLOOKUP_Excel_5

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!

VLOOKUP_Excel_6

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