How to VLOOKUP in Google Sheets From Another Tab/Sheet

Are you working with multiple datasets across different worksheets, and you need to look up data between different worksheets? Well, doing it manually can leave you frustrated.

But what if we told you Google Sheets has a unique function that saves you all that hassle by looking up the data for you? Would you be interested? Well, we bet you would.

Google Sheets VLOOKUP function is a powerful resource that lets you look up and retrieve matching data from another table. This could be either from the same sheet or a different sheet.

In today’s Google Sheets tutorial, we will show you how to VLOOKUP in Google Sheets from another tab/sheet.

But before we jump into the practical aspect of today’s tutorial, let’s start with the basics- Understanding VLOOKUP in Google Sheets.

What is VLOOKUP in Google Sheets?

VLOOKUP function is a powerful resource in Google Sheets that allows you to do more within a short time. It stands for Vertical Lookup.

Using the VLOOKUP function in Google Sheets lets you perform a vertical lookup. Simply put, it lets you search for a particular lookup value within a range of cells.

By unlocking the full potential of VLOOKUP in Google Sheets, you’ll be able to call data across different worksheets.

Thanks to this incredibly useful function in Google Sheets, you can turn what would have been hours of work into mere seconds.

Although the VLOOKUP function is super useful for data analysis on a single sheet, you can unlock its full potential by calling data across different spreadsheets.

By executing VLOOKUP in Google Sheets, you’ll be able to look up and fetch specific values from a large volume of data effortlessly.

And as we mentioned earlier, this function can be used across multiple spreadsheets, ensuring your data is clean and tidy. What’s even more exciting is that it automatically updates the spreadsheet any time a change is effected.

While there is a general consensus among Google Sheets newbies that VLOOKUP is challenging to understand and apply, today’s guide will simplify the entire process.

After reading today’s tutorial about VLOOKUP in Google Sheets, you’ll see how easy it is to apply.

Understanding Google Sheets VLOOKUP Function

Before we delve further into today’s guide, it’s critical that you have some understanding of the VLOOKUP function. For those new to VLOOKUP, here is the syntax for VLOOKUP in Google Sheets:

VLOOKUP(search_key, range, index, [is_sorted])

Let’s break down the VLOOKUP function, so you understand it better.

search_key: This parameter within the VLOOKUP function represents the key value you want to look up in your worksheets. And just so you know, it can be a value or a reference to a cell within your worksheets.

Range: This parameter refers to the range of cells in your worksheets within which the VLOOKUP function should search. It’s important that this range has the column featuring the search key as well as the other column with the corresponding value you’re looking to retrieve. To clarify things a bit, keep in mind that the VLOOKUP function always searches the first column for a range to find the search key.

Index: This parameter, within the syntax, tells VLOOKUP which column value to return. Let us quickly add that the index is relative to the range, not the worksheet. Let’s say our search is matching in column B and then returns a value from column C; it means our index value is 2. On the flip side, if our search is matching in column D and returns a value from column B, then the index value is 3.

Is_sorted: Unlike other parameters within the VLOOKUP syntax, this one isn’t very clear and here is why. It returns exact matches when it’s set to false. When set to true, it returns the closest match. That said, remember that true is set by default, while false is recommended for most uses.

Note: A FALSE value for the is_sorted parameter indicates that the first column of the range doesn’t need to be sorted in ascending order. Consequently, the VLOOKUP function searches for an exact match of the search_key.

In a situation where there is more than one value equal to the search_key, the VLOOKUP function accesses the first occurrence of the search_key.

On the flip side, a TRUE value means that the first column needs to be sorted out in ascending order.

In this instance, the VLOOKUP function first looks for an exact match of the search_key. If it doesn’t find an exact match, the function searches for the closest match.

As we reiterated earlier, the parameter is_sorted is set to FALSE by default.

We understand that this explanation is somewhat confusing. But don’t worry; you’ll get the hang of it once we jump into the practical aspect of today’s tutorial.

Can Google Sheets VLOOKUP from Another Sheet or Tab?

Let’s assume you know how to use VLOOKUP in Google Sheets to fetch data from the same worksheet. But how do you do it when looking up data from a different sheet in the same workbook? Well, it is surprisingly easy, and we will show you how to go about that in a bit.

If you haven’t done this before, you might find it challenging at first, but with us guiding you every step of the way, you’ll see how easy it is to use VLOOKUP to fetch data from different sheets of the same workbook.

Let’s cut to the chase and bring you all the practical details, shall we?

For our tutorial, we will use the following sample data. It comprises two sheets. We will name the first sheet Employee Records. It will house information such as employee ID, employee name and hourly pay. The second sheet, which we will name Sales, will feature employee ID, hourly rate and hours worked.

Here, take a look at what our Employee Records Sheets look like:

Here is what our Sales Sheets look like:

Our objective with the above sample data is to access the Employee Records sheets to retrieve Hourly Rates corresponding to the employee IDs we have in the Sales sheet. The goal is to display the result in cells B2 to B11 of our Sales sheet.

Here are the relevant steps to follow to VLOOKUP from another worksheet in Google Sheets:

Step 1: Choose Sheets to Display results and select the appropriate cell

Since we want to display the result in the Sales sheet of our worksheet, we need to head over to that sheet and select the cell where we want the result to appear. For this example, we will select cell B2 in our sales sheet.

Step 2: Type VLOOKUP, Followed by parentheses

After choosing the cell where we want to display our result, we need to head over to the formula bar and type =VLOOKUP, followed by an opening parenthesis. This action will launch the formula in our chosen cell.

The screenshot below gives a better understanding:

Step 3: Choose the cell containing the value you want to look up

Now, choose the cell with the value you’d like to look up. For our example, we will select cell A2, followed by a comma. If you did everything right, you should have something like this.

Step 4: Select Employee Sheet

For the second parameter, we need to select our Employee Sheet. Here, we need to choose the range of cells we want VLOOKUP to search in. For this guide, we will select cells in the range of A2: C2. If you did this right, you should have something like this.

Step 5: Add a comma to the formula, followed by the index of the column

Head over to the formula bar and continue from where you stopped. This time, you want to add a comma to the formula. After doing that, you also want to add the column’s index, which has the value you’d like to retrieve. For this tutorial, we are looking to retrieve Hourly Pay, which is in the third column in the range A2:C11. So go ahead and type 3. Once you’re done, close the parentheses.

Step 6: Hit the Enter button on your keyboard

After entering your formula, you can simply hit the Enter key on your keyboard. Google Sheets will automatically generate the result in the selected cell. Here is what ours looks like:

From the image above, you can see clearly how we have used the VLOOKUP function to fetch data from a different sheet. However, we only did so for cell B2 in our Sales spreadsheet. We need to repeat the same process for the other cells.

But instead of going at it manually, which will waste your time, you can use the Google Sheets autofill option.

This video shows you exactly how to do it.

Understanding the VLOOKUP Formula We Used

If you’ve read to this point, we are sure you now know how to use VLOOKUP in Google Sheets to fetch data from a different sheet in the same workbook. However, we want to use this opportunity to explain the formula so you understand it even better.

To start with, here is the syntax for the VLOOKUP function (when referring to data in a different sheet)

=VLOOKUP(search_key,{sheet name}!{cell range},index,is_sorted)

Looking closely at the syntax above, you’ll notice an exclamation mark “!” between the sheet name and cell range. This is because we want to VLOOKUP in Google Sheets from another tab.

For our tutorial, we wanted to look up data from the range A2:C11 in the sheet “Employee Records.” At the end of the day, we used the following formula:

=VLOOKUP(A2,’Employee Records’!$A$2:$C$2, 3)

Again, if you look closely at the above formula, you’ll notice our reference sheet name is enclosed in single quotes. However, you don’t need to add single quotes if you use the default sheet names provided by Google Sheets (Sheet 2, Sheet 2, etc.)

In our example, we renamed the sheets as Employee Records and Sales, respectively. This explains why we had to enclose our reference sheet in single quotes.

Overall, the above formula fetches the value from the third column (Hourly Pay) of the sheet named “Employee Records.

How to VLOOKUP from Another Sheet in a Different Workbook

We are sure by now you know how to use VLOOKUP in Google Sheets to fetch data from a different sheet in the same workbook. Now we want to take things up a notch. This section will show you how to perform VLOOKUP from another worksheet in a different workbook.

While we will use the same function as the one we used earlier, we will slightly change the second parameter.

For the second parameter, we will include the IMPORTRANGE function. Read on as we dive deeper into how to use the IMPORTRANGE function to do VLOOKUP in Google Sheets from a different sheet.

IMPORTRANGE Function in Google Sheets

If this is your first time hearing about the IMPORTRANGE function, you’ll be happy to learn that this function is used to import values from cells in another spreadsheet into your current spreadsheet.

Here is the syntax for the IMPORTRANGE function.

IMPORTRANGE(spreadsheet_key, range_string)

The IMPORTRANGE function has two distinct parameters:

Spreadsheet_key: This parameter represents the spreadsheet URL where you want to import value from. For a smooth and seamless experience, it should be specified in double quotes.

range_string: This parameter represents the range of cells you want to import. It typically contains the sheet name and range of cells that you want. Assuming you want to import cells A2:C11 from a worksheet named ‘Employees Record,’ the range_string will look something like this:

“Employee!A2:C11”

That said, you need to specify the whole range string within the entire double quotes.

Having established this background, let’s get back to our earlier example.

Let’s say we have the employee data in a workbook called ER1, in a sheet named Employees Record, and the data for sales in a separate Workbook named SA1, in a sheet called Sales, and we want to import some details; we can use the IMPORTRANGE function.

What we are aiming to do here is access the Employee sheet from the Workbook, ER1, retrieve the Hourly Pay information for corresponding IDs and display them in cells B2 to B11 in the Sales sheet in Workbook SA1.

Here is how to achieve that:

Step 1: Select the cell where you want the result generated

The first thing you want to do is click the cell in your target column where you want the VLOOKUP between sheets to be generated. For this example, we will choose cell B2 in the Sales sheet of workbook SA1.

Step 2: Type the VLOOKUP function in the formula bar

With your target cell where you want the result generated selected, navigate to the formula bar and type =VLOOKUP.

Step 3: Choose the cell with the value you want to look up

Next, quickly choose the cell with the value you’d like to look up. For this tutorial, we will select cell A2 and add a comma afterward.

Step 4: Complete the second parameter

For the second parameter, we need to enter the IMPORTRANGE function. The image below shows you what it should look like:

Step 4: Launch the workbook (WR1)

Open the workbook WR1, as this is where we want to import the data from.

Step 5: Copy the URL

Head over to the location bar of your browser and copy the URL for the WR1 workbook.

Step 6: Return to the previous workbook

After copying the URL for the WR1 workbook, head over to the SA1 workbook and paste the URL at the end of the formula in the formula bar. Don’t forget to enclose the URL in double quotes. If you did everything correctly, you should have something like this:

Step 7: Update the formula

After adding the URL, the next thing you need to do is add a comma, followed by the sheet name of the source sheet. For our example, that’s the Employee Records Sheets. Once that’s done, you need to add an exclamation mark (!), followed by the range of cells you want to look up from the source spreadsheet.

Step 8: Add Index

Now, you need to add the index that represents the column you want to return the results from. For our example, the index is 3. So go ahead and update the formula to reflect that.

Step 9: Allow Access

After typing the formula and hitting the Enter button on your keyboard, you’ll typically get an error message. This happens because you didn’t grant access. So go ahead and grant access.

Once you get access, Google Sheets will automatically generate the result in the chosen cell. Here, take a look at what ours looks like:

Final Thoughts

Using VLOOKUP in Google Sheets to fetch data from another tab/sheet can be tricky. Because it is a bit advanced, you’ll need some guidance to pull it off, especially if this is your first time using the VLOOKUP function.

Thankfully, today’s guide has all the information you need to perform VLOOKUP in Google Sheets.

Our Google Sheets tutorial includes a step-by-step tutorial showing you how to VLOOKUP in Google Sheets from a different sheet or tab.

If you have always wanted a comprehensive tutorial with detailed steps on performing VLOOKUP in Google Sheets, we are sure you’ll find today’s tutorial helpful.

And like we always do with our Google Sheets tutorials, today’s guide featured tons of screenshots and short videos that will guide you every step of the way. Feel free to explore these resources in case you run into any issues.