Merge Data From Multiple Spreadsheets with vLookup Function

vLookup: Quickly Find & Return Data

When I find myself trying to pull together data from multiple spreadsheets, I frequently turn to using the vLookup function. VLookup can be used provided there is a unique value (search key) in both data sets. For example, a student identification number or email address. The vLookup function uses this search key to “lookup” information from one data source and pull it into another.

Example of vLookup Use

Some, but not all, of the data needed is in the “Source Spreadsheet”. Another sheet contains additional fields I would like to merge into the Source Spreadsheet. By using vLookup, the formula will search the Lookup Range for a matching Student ID and return the student’s grade and contact email. The far left column should contain the Search key (Student ID) so that vLookup will function correctly.

Anatomy of vLookup Function

vLookup Formula Breakdown:

1. Search Key or Lookup Value which is the common but unique value that exists in BOTH data sets. For example a student ID number.
2. Range or Table Array which is the “other” data range that contains additional values you want to merge into an existing data set. For example, parent / guardian email addresses or student grade level information. Notice that the Student ID exists in BOTH data sets.
3. Index or Col_Index_Number is the number of the column, with the Search Key or Lookup Value equal to 1 and each column to the right increases by 1. For example if your Search Key is in column A and you want to retrieve data in column C this value would be 3.
4. Is Sorted or Lookup Range, is technically optional, but should be set to False to ensure only an exact match is returned.

Tutorial Video & Example

Practice using the vLookup function on your own using THIS sample data spreadsheet which matches the video tutorial below.