Google Sheets VLOOKUP from Another Sheet

I’m here to discuss how to use VLOOKUP from another sheet in Google Sheets. My goal: Help you increase your productivity with some simple automation. After all, that’s why I love the VLOOKUP function.

Below, I’ll break down the syntax and include screenshots for step-by-step instructions on how to use it. Here’s my guide on how to use VLOOKUP to get data from another sheet.

What is VLOOKUP in Google Sheets?

VLOOKUP is short for Vertical Lookup. It’s a popular function in Google Sheets because you can use it to call data across different spreadsheets and workbooks.

Just as importantly, it’s a time saver.

You can use the VLOOKUP function for data analysis on a singular sheet or by calling data across different spreadsheets. If you’re wondering how to use Google Sheets VLOOKUP from another sheet, that’s what I discuss here.

In short, you can look up and fetch specific values from large amounts of data quickly and efficiently.

Being able to use it across multiple spreadsheets makes it easier to keep your data clean and automatically updates when there are changes between sheets. In this article, we will show you how to Google Sheet VLOOKUP another sheet.

How to VLOOKUP from Another Sheet in Google Sheet

I want to give a simple step-by-step guide to get started. So here’s how to do VLOOKUP in Google Sheets from a different sheet:

1. Type =VLOOKUP( in a cell in the active sheet
2. Select the cell reference of the value you want to look for.
3. For the range, we can now go to the source sheet where we are performing the VLOOKUP. All you need to do is select the range of cells that you want to look up.
4. Add the index that represents the column you want to return the results from.
5. Add 0 or false for an exact match.
6. Copy the formula to the rest of the cells

If that seems a little confusing, don’t worry. I’ll break it down further with real-world examples below.

Before You Start

We’d recommend having a basic understanding of how VLOOKUP works. An even better idea would be to start out semi-fluent in Google Sheets altogether. To get to this level, you may want to consider taking a Google Sheets course, or even a full productivity course on Udemy to brush up your skills.

We even have our own comprehensive course that covers Google Sheets and Google Forms.

The Google Sheets VLOOKUP From Another Sheet Formula

You know how much I love to break down a Google Sheets function into its most fundamental parts. That’s the case here. And there’s good news.

The VLOOKUP syntax is pretty straightforward.

As the “vertical” in its name implies, VLOOKUP searches the leftmost column in the established range for a row match and returns the value in the cell identified by the index.

It searches a column and returns a value from the matching row, usually from a different column.

One of the biggest weaknesses in VLOOKUP is it can’t search for a match in a column on the right and bring back a result from a column to its left. In other words, VLOOKUP can only look right.

This is what the formula looks like when broken down by part:

=VLOOKUP(search_key,range,index,is_sorted)

Let’s look at each component individually:

• =VLOOKUP(): This is the function itself without any parameters. It’s what tells Google Sheets to act.
• search_key: This parameter defines what information we’re looking to match. It might be a name, a number, a boolean value, or something different. It might be a static value we’ve defined or it might be a relative value stored in a cell. If we set this as “A2” it will search for the value in cell “A2”. If we set it to “true” it’ll match the text string “true.”
• range: This tells the VLOOKUP function where to look for a match and the range in which the value it is supposed to return is located. If we’re searching for a value match in column B and want to return a value in columns C or D, we’d set the range as B:D.
• index: This parameter tells VLOOKUP which column value to return. The index is relative to the range, not the sheet. Hence it is a number instead of a letter like columns is usually defined. If our search is matching in column B and returns a value from column C, the index value is 2. If we’re returning column D from a column B match, the index value is 3.
• is_sorted: This parameter’s name isn’t as clear as it returns exact matches when it’s set to “false” and the closest match when set to “true.” True is set by default, but false is recommended for most uses.

Now let’s have a look at it all put together:

=VLOOKUP(A2,A2:B5,2,false)

How you define the range is extremely important and can be confusing if you’re new to how VLOOKUP works. The defined range needs to include both the data value you’re searching for and the data value you look to return. If you’re trying to search column A and bring back the value in column B, the range needs to include both columns A and B. If you limit the range to A, the VLOOKUP call will fail.

Performing VLOOKUP from Another Google Sheet in the Same Workbook

Ready for those real world examples I mentioned earlier? This is where you’ll find them.

While most of the time, we use the VLOOKUP formula in Google Sheets on the same sheet, many times, you may have to use it to VLOOKUP between two sheets in the same workbook or even across the different workbooks.

For example, you may want to fetch the data from specific items in a worksheet while the lookup data is in a different sheet or different workbook.

How you use the VLOOKUP function to bring in data from another sheet is somewhat different when you’re working in the same workbook or a different workbook.

The formula for the same workbook VLOOKUP looks like this:

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

Notice there’s a “!” between the sheet name and cell range. Also, there are no quotes around the range on a same-workbook VLOOKUP.

Let’s look at our example below:

This sheet is our Sales sheet. We can get the prices for each product from a different sheet called the Price sheet.

Below is the formula that will do this:

=VLOOKUP(B2,Price!A1:B10,2,0)

Here’s how to perform a VLOOKUP Google Sheets from another sheet:

Step 1: Type =VLOOKUP(  in the cell in the active sheet.

Step 2: Select the cell reference of the value you want to look for. In our example, the cell is B2.

Step 3: Go to the Price sheet and select the entire range.

Step 4: Add the index that represents the column you want to return the results from. In our case, it is column B, so the index is 2.

Step 5: Add 0 or false for an exact match.

Step 6: To copy the formula to the rest of the cells, we can click and drag the square at the bottom right corner of the cell.

The above formula fetches the value from the second column from the sheet named “Price” in the current sheet.

Performing VLOOKUP from Another Worksheet in a Different Workbook

The process of referencing data using VLOOKUP from another workbook, Google Sheets, is a little more complicated. You need to combine the VLOOKUP function with the IMPORTRANGE function.

Now, let’s look at an example where we need to fetch the value from a different Google Sheets workbook

=VLOOKUP(search_key,IMPORTRANGE(“{sheetsURL}”,“{sheet name}!{cell range}”),index,is_sorted)

This version uses a new command called “IMPORTRANGE()”.

This command’s syntax asks you to define the URL of the workbook you’re importing data from, define the specific sheet, and set the range.

The formula breaks down like this:

IMPORTRANGE(“{sheetsURL}”,“{sheet name}!{cel range}”)

• {sheetsURL}: In quotes, add the URL of the Google Sheets file you want to access. Example: “https://docs.Google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
• {sheet name}!{cel range}: Set this the same way you configured it in the same-workbook method. However, notice that this time there are quotes around the sheet name and cell range.

Now look at it with a value-added, and all values added :

=VLOOKUP(A2,IMPORTRANGE("https://docs.Google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)

To reiterate, there are three important values you need to set in a cross-sheet VLOOKUP reference:

• Workbook URL {sheetsURL}
• Sheet Page {sheet name}
• Cell Range {cell range}:

We’ll use the same product lineup of Gadgets, Gizmos, Thingamabobs, and Widgets from the previous example.

This time around, we want to bring in the same information into a sheet called “Outside” in a different workbook.

We want to bring in the information from the “Called” sheet in the other workbook:

Now let’s say we want to have the “Outside” tab show how many products are in stock, but we only want to worry about updating the “Called” spreadsheet page in the original workbook.

Step 1: Click on the cell you want to return the results

Step 2: Type in the VLOOKUP formula.

Step 3: Select the cell reference for the value you want to look up.

Step 4: Type the IMPORTRANGE formula.

Step 5: Go to the other work book and on the sheet and copy the URL.

Step 6: Paste the link into the formula.

Step 7: Get the range you want to lookup together with the sheet name.

Step 8: Add the index that represents the column you want to return the results from.

Step 9: Add 0 or false for an exact match.

Step 10: Click Allow Access.

Enter our VLOOKUP formula in the topmost cell — in our case, we’re using

=VLOOKUP(A2,IMPORTRANGE("https://docs.Google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.

Now our second workbook is referencing the first workbook’s inventory count:

This can be extremely useful for someone analyzing the information in the first worksheet without any risk of error in the original data.

It’s also very useful for bringing in a fraction of the information in a spreadsheet for easier analysis in another. The VLOOKUP command makes it easier to bring it only the information you want.

Performing VLOOKUP in Google Sheets From Multiple Tabs

Aside from using it with multiple columns, you can also perform a VLOOKUP from multiple sheets in Google Sheets. To do this, we will need to create an ARRAYFORMULA that will do VLOOKUP in Google Sheets from another tab.

In our example sheet, we have 3 different sheet tabs, two of which have the prices of the products.

This is the formula we will use:

=ARRAYFORMULA(VLOOKUP(A2:A14,{Price!A2:B10;Price1!A2:B10},2,FALSE))

Step 1: Type the ARRAYFORMULA in the cell in the active sheet.

Step 2: Type =VLOOKUP(

Step 3: Select the cell range of the value you want to look for.

Step 3: Add curly brackets and go to the Price sheet and select the entire range. Add a semi-colon after.

Step 4: Go to the Price 1 sheet and select the entire range. Close the curly brackets.

Step 5:  Add the index that represents the column you want to return the results from. In our case, it is column B, so the index is 2.

Step 6: Add 0 or false for an exact match.

This formula will look up in the two sheets (Price and Price1) and return matching values for the price of the products in each sheet. If it does Into find a match in the first sheet, it will then look in the second sheet.

Using VLOOKUP in Google Sheets from Multiple Different Sheets

Using the IMPORTRANGE function, you can also perform a Google Sheets VLOOKUP in more than one worksheet.

Just like with multiple sheets, you will need to create an array either with curly brackets or with the ARRAYFORMULA.

In our example sheets, if we wanted to perform a lookup from multiple sheets in different workbooks, we would use the formula:

=VLOOKUP (A2, {IMPORTRANGE ("https://docs.Google.com/spreadsheets/d/1tQfzx2_n97OHJiOUoij0rnDV6sZk0R3tP_Ryrcw/edit#gid=0", "Sheet1!A2:B10") ; IMPORTRANGE("https://docs.Google.com/spreadsheets/d/1aHwZyavTciiGRV31nGL43hlmiWl7JZgytBiftqE/edit#gid=0","Sheet1!A2:B10")}, 2, false)

Step 1: Click on the cell you want to return the results

Step 2: Type in the =VLOOKUP formula.

Step 3: Select the cell reference for the value you want to look up.

Step 4: Add curly brackets and type the IMPORTRANGE formula.

Step 5: Go to the other work book and on the sheet and copy the URL.

Step 6: Paste the link into the formula.

Step 7: Get the range you want to lookup together with the sheet name and close the brackets.

Step 8: Add a semi-colon and type the IMPORTRANGE formula again for the second workbook.

Step 9: Copy and Paste the URL into the formula.

Step 10: Get the range and sheet name and add it to the formula.

Step 11: Add the index that represents the column you want to return the results from.

Step 12: Add 0 or false for an exact match.

Step 13: Click Allow Access.

This formula will perform a Google Sheets lookup in another sheet in a different workbook and if it fails to find a match there, it will perform the lookup in the second workbook.

The formula for each workbook is separated by a semi-colon, and they are enclosed in curly brackets to indicate an array.

Some Tips when Using VLOOKUP to Reference Another Sheet/Workbook

Here are some tips to keep in mind when referencing another sheet or workbook in the formula:

Be specific about the range

The Google Sheets VLOOKUP feature can be very performance-hungry and cause a workbook’s performance to come to a crawl.

You can avoid slow performance by being specific with the ranges you reference.

• Instead of calling entire columns like “A:B” reference the specific starting and ending cells like “A1:B1000”. This cuts down on how much work Google Sheets needs to do to bring in the same amount of information.
• If you’re searching column A for information and bringing back the result in column D, use a reference like “A1:D1000” instead of “A1:F1000”. There’s no need to reference columns E and F if in the range if they’re not being used.

This is especially important when you’re calling information between different workbooks. When you’re doing a cross-workbook VLOOKUP, it requires Internet bandwidth to transfer data between the two.

Use conditional statements to prevent unnecessary calls

Another way you can prevent slow-down with cross-sheet VLOOKUP is to use a conditional statement to determine if Google Sheets should run the VLOOKUP at all.

For example, if there’s information on the sheet you’re using the VLOOKUP call that tells you there’s no need to run it, use that to your advantage.

In our product example, the “Active” sheet lists whether or not a product is in stock. Since we know a product is out of stock, we don’t need to use VLOOKUP to define the stock count.

For this, we’ll use the “=if()” function. This function asks if a given condition is true or false, then does something different for each case. The syntax looks like this:

=if(logical_expression, value_if_true, value_if_false)

In a simple use-case, we can use it to determine if the value in cell A1 is greater than the value in cell B1. So the expression A1>B1 would look like this:

=if(A1>B1, “A1 is greater”, “B1 is greater”)

The formula will return the text “A1 is greater” if A1 is the larger number and “B1 is greater” if B1 is the larger number.

In the case of our product worksheet, if the “In Stock” value is “No” we don’t want to run VLOOKUP. So we set up our if statement like this:

• logical_expression: B2=”YES” — This will run the “value if true” if the data in cell B2 is “YES”.
• Value_if_true: VLOOKUP(A2, Called!A2:B5,2, false) –This will run the VLOOKUP if the logical expression returns true.
• Value_if_false: “out of stock” — This will return the text “out of stock” if the value in cell B2 is anything other than “YES.”

If we put it all together, it comes out looking like this:

=IF(B2="YES",VLOOKUP(A2,Called!A2:B5,2,false),"out of stock")

Notice how cell C3 now says “out of stock” instead of returning the value “0”. In this case, we avoided running a VLOOKUP because we didn’t need to bring back data.

While you’re inputting more information into Google Sheets to calculate results, it is creating less work for the program.

It is much less work to run many “if checks” than a single VLOOKUP. Using this technique will help you speed up Google Sheets and improve performance.

Make sure you have permission

For obvious security reasons, Google Sheets won’t let you pull in data from another workbook unless you have the authorization to do so.

To reference one workbook from another with VLOOKUP, you need to either be the creator of both or have permission to use both. You can be added as an authorized user either by account or through a sharing URL.

Using VLOOKUP to reference information across different sheets and workbooks is an incredibly powerful tool to have at your disposal.

The command is particularly helpful with cross-sheet use because it will reflect any changes made to the original sheet across all referenced sheets.

Frequently Asked Questions

How Do I Do a VLOOKUP From Another Google Sheet?

Here’s how to perform a VLOOKUP Google Sheets from another sheet:

1. Type =VLOOKUP(  in the cell in the active sheet
2. Select the cell reference of the value you want to look for.
3. For the range, we can now go to the source sheet where we are performing the VLOOKUP. All you need to do is select the range of cells that you want to look up.
4. Add the index that represents the column you want to return the results from.
5. Add 0 or false for an exact match.
6. Copy the formula to the rest of the cells.

How Do I Do a VLOOKUP From Multiple Sheets in Google Sheets?

To perform a VLOOKUP from multiple sheets:

1. Select the cell reference of the value you want to look for. In our example, the cell is B2.
2. Go to the first sheet and select the entire range.
3. Go to the second sheet and select the range. You can keep selecting from the other sheets if you have more sheets.
4. Add the index that represents the column you want to return the results from.
5. Add 0 or false for an exact match.
6. Copy the formula to the rest of the cells

Here’s an example of the formula

=ARRAYFORMULA(VLOOKUP(A2:A14,{Price!A2:B10;Price1!A2:B10},2,FALSE))

How Do I Pull Matching Data from Another Sheet in Google Sheets?

You can use the VLOOKUP function to pull up data from another sheet in google sheets. All you need is to type in the formula and select the range you want to look up in the other sheet.

We’ve shown you just how to do this in this guide.

What is the Difference Between VLOOKUP and MATCH in Google Sheets?

The VLOOKUP can only search for values in the left column of the range and retrieve values from columns to the right of the search column.

On the other hand, the MATCH function is used to search for a value in a single row or column of a specified range, and return the relative position of that value within the row or column.

Conclusion

The wealth of advanced VLOOKUP features and use-cases also work in cross-sheet references. Using VLOOKUP to reference another sheet opens up a world of new possibilities to work with your data. It also helps you save time.

In this guide, I showed how to use Google Sheets VLOOKUP from another sheet in the same workbook and in different workbooks.

I hope you found this tutorial useful. Looking for more advice? I also wrote about how to use index-match in Google Sheets.

Related:

• Using Query function in Google Sheets.
• Using COUNTIF Function in Google Sheets.
• How to VLOOKUP Multiple Columns in Google Sheets?
• How to Use the INDEX function in Google Sheets
• How to Use Index Match in Google Sheets (Step-by-Step Guide)