How to use VLOOKUP in Google Sheets from Another Tab and Spreadsheet

Did you know, you could easily match up vertical arrays of large data sets with just a simple formula? 

VLookup in Google Sheets is a function that enables us to search for a certain value in the table array with just a simple line of code. 

In this guide, we’ll understand the methods of performing Google Sheets VLookup functions on a tab and on multiple spreadsheets.

An overview of what we’ll cover: 

So let’s start! 

How to use Google Sheets VLookup from Another Tab

Let’s understand how to use Google Sheets VLookup to get data from another tab. 

For our reference, we’ll consider a VLookup Google Sheets example, where we have a random list of Employee IDs in the sub sheet named Sheet1

We want to find the names of the employees corresponding to the data we have in column 1 of our sheet. 

Verifying data on a spreadsheet to do a VLookup function

In another sub sheet named Employee Data 1, we have complete data about 15 employees including their Name, Address, and Employee ID. 

Verifying data from the sub sheet 1 on a spreadsheet to do a VLookup function

Similarly, we also have an Employee Data 2 sub sheet that contains the data corresponding to 15 other employees from our organization. 

Verifying data from the sub sheet 2 on a spreadsheet to do a VLookup function

And similarly, we also have an Employee Data 3 sub sheet that contains the data regarding 15 other employees from our organization. 

Verifying data from the sub sheet 3 on a spreadsheet to do a VLookup function

We want to do a lookup from all the three Employee Data sub sheets to pull the data in our Sheet1. 

Let’s call the lookup function by inputting =VLOOKUP. Next, we’ll need to configure the search_key, range, index, and whether our function is sorted or not. 

Suppose we want the function for our first Employee ID. This ID will be our search_key. As this ID is in the A2 cell, we’ll enter the same location. 

Add a comma after the location. Next, we need to input the range

Our first data set is from the sheet named Employee Data 1. Navigate to the sheet, and select the whole range of data. Again add a comma. 

Our next parameter is the index. This parameter signifies the column from which we want to extract the information corresponding to our search_key. 

As our information is the name of the employee from column 2, our index will be 2

Finally, we need to mention whether our data is sorted or not. As this data is not sorted, let’s add a 0 after a comma. 

Close the parentheses once the input is configured. 

Configuring the VLookup function to access the data from a sub sheet of a Google Spreadsheet

One important consideration while calling the VLookup function is the range that we configured. 

We need to drag this formula to all the other cells in Sheet1 so it can be a generalized formula. Make the range an absolute reference by selecting the range value and clicking on F4. 

Setting an absolute range for the VLookup function to access the data from a sub sheet of a Google Spreadsheet

Once done, drag this formula to cover all the employee IDs. You’ll notice that a lot of the names will be added in column B. 

Implementing the VLookup function across the data set to access the data from a sub sheet of a Google Spreadsheet

However, we still need to configure the Employee Data 2 and Employee Data 3 sheets in order to obtain all the names. 

Using VLookup in Google Sheets from Multiple Tabs

Let’s modify our range into an array by using curly brackets {} to denote the values. 

Modifying the range of a VLookup function into an array to access the data from a sub sheet of a Google Spreadsheet

We can add multiple sheets to this array now. We’ll add a semicolon after the range of the first sub-sheet ends within the curly brackets. 

Following that, we’ll again add the range for the second and the third sub-sheet. The process will be exactly the same as we added for the first sheet. 

Make the range an absolute reference by selecting the range value and clicking on F4. 

Configuring the range into a VLookup function to access the data from multiple sub sheets of a Google Spreadsheet

Drag this formula to cover all the data sets. If the formula is configured correctly, you’ll see that all the IDs have been added correctly! 

Implementing the VLookup function across the data set to access the data from multiple sub sheets of a Google Spreadsheet

Note that if the data isn’t represented correctly, it could be due to an error. 

Make sure that your search key is in the same column in each and every sub sheet. This is because we have added a single value of the search key in our formula. 

So, the order of your columns should be the same in all the sub sheets that contain the data. 

However, there might be some entries that don’t show any Employee Name corresponding to the Employee ID. 

This is simply because the data of those IDs don’t exist in any of our Employee Data records. 

How to use Google Sheets VLookup from Another Sheet

Let’s say you have the data in different spreadsheets instead of sub sheets. The method is slightly different if you want to integrate the data for Google Sheets VLookup from another sheet. 

Our current formula won’t work because our range has changed. But the essence of our new formula is similar to the one we already created. 

This means we’ll still check the second column of our data to match the first column for VLookup. 

Suppose we want to add Employee Data 1, Employee Data 2, and Employee Data 3 spreadsheet information into Sheet1. 

For making it easier to understand, let’s start with adding the range values from the Employee Data 1 spreadsheet. 

We’ll need to import the range from various tabs into the one in which we’re collaborating the data. 

The import function should always be in a new cell. Call the import range function by typing =IMPORTRANGE

There will be two parts for this function – adding the sheet URL, and adding the range string. 

The first one is to add the sheet URL. 

Copy the Employee Data 1 spreadsheet URL. As our URL is in a text format, we’ll add it in double-quotes “URL”. 

Add a comma after configuring the first parameter. Next, we’ll add the range in the form of the string, so again in double-quotes.

As we want the data from a sub sheet under Employee Data 1 named Data 1, we’ll keep the range as Data1!A2:C

Implementing an import range function to access the data from various spreadsheets

Click on Enter after filling up all the information. If the configuration is correct, you’ll be prompted to request access to Employee Data 1 sheet. 

Allowing access for implementing an import range function to access the data from various spreadsheets

Once you allow access, it will establish a connection with the sheet. 

Accessing the data from various spreadsheets by calling the import range function

If the configuration is successful, it will show a list of the whole data set that originally appeared on the Employee Data 1 sheet.

The range of our VLookup function will be the entire import function. Copy the whole function after the equal to sign. 

Paste the entire import function as the range of the VLookup function. 

Configuring the import range function as the range for a VLookup function to access data from different spreadsheet

Click Enter to save the changes to the formula. 

Once done, you can also remove all the data from the Employee Data. 

Drag the formula to all the Employee IDs so that your data is visible. 

Implementing the VLookup function across the data set to access the data from a different spreadsheet

This way, we have connected the data from another sheet into our VLookup function. 

Let’s see how to connect the data from multiple sheets to do a VLookup. 

Using VLookup in Google Sheets from Multiple Sheets

We’ll follow the same process for adding all three Employee Data sheets to our Sheet1. 

One most important thing to remember is that we need to add each sheet individually and allow them access to the data. 

The request access option is essential for configuring the data from multiple sheets to do a VLookup. 

Just like adding the ranges of multiple tabs, we’ll add the ranges of various Employee Data sheets separated by a semi-colon.

Configuring the import range function as the range for a VLookup function to access data from multiple spreadsheets

Once you’ve added the ranges of all the sheets, your VLookup function is ready. 

Drag your code to the entire data set for doing a VLookup. 

Implementing the VLookup function across the data set to access the data from multiple spreadsheets

Your configuration is successful if your data comes up correctly. 

There might be some entries that don’t show any Employee Name corresponding to their Employee ID. 

This is simply because the data of those IDs don’t exist in any of our Employee Data records. 

Summary

So that’s all you need to know about performing Google Sheets VLookup functions on a sub-sheet as well as multiple spreadsheets. 

These functions are especially useful when you want to match up vertical arrays of data from large volumes of data sets. 

If you’re new to Google Sheets, a good idea is to start with our Google Sheets Basics guide.

Were you able to establish VLookup functions across your sheets? Which method did you use – VLookup for sub-sheets or VLookup for spreadsheets? Let us know in the comments below! 

JOIN US!

Master Data & Analytics with Measuremasters

Exclusive Courses & Workshops | Ongoing Troubleshooting | Support Resources, Tools & much more
guest
0 Comments
Inline Feedbacks
View all comments

Blog Categories

Join 30,000+ marketers getting exclusive resources and our latest content!

now it's time to

Start measuring like a master

Itching to jump into the world of MeasureMasters? This is what you have to look forward to.