How to VLOOKUP from Another Sheet in Google Sheets

You can unlock the full potential of Vlookup, (vertical lookup), in Google Sheets by using it to call data across different spreadsheets and workbooks.

The Vlookup function is an incredibly powerful feature that can turn hours of work into mere seconds. While the function is useful for data analysis on a singular sheet, you can further enhance Vlookup’s usability by calling data across different spreadsheets.

With this function, you can lookup 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.

The VLOOKUP formula

The Vlookup syntax is pretty straightforward but can be used to a highly complex degree.

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 or turn 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 for “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 are usually defined. If our search is matching in column B and returning 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 in 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.

Using VLOOKUP in the Same Workbook and in a Different Workbook

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

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:

=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.

In our test, we want to set the range to A3:B6 on our sheet named “Called.” We’re searching for a match in column A and returning the value in column B.

Below is the formula that will do this:

=Vlookup(A2,Called!A2:B5,2,false)

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

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}:

Running through the Vlookup process

Let’s understand the process of how VLOOKUP works when referencing other sheets or workbooks.

VLOOKUP from Another Worksheet in the Same Workbook

Let’s use a simple case of product inventory to showcase how Vlookup works.

We have a four-product lineup in this example: Gadgets, Gizmos, Thingamabobs, and Widgets. Currently, we don’t have any Gizmos in stock. Our first sheet, “Active” gives us a list of all our products and tells us if a product is in stock:

Dataset in the current sheet

 

We also have a second sheet we’ve named “Called” that lists how many of each product we have in stock.

Another sheet in the same workbook

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

  • Enter our Vlookup formula in the topmost cell — in our case, we’re using
    =Vlookup(A2,Called!A2:B5,2,false)in cell C2.

    vlookup to reference another sheet

  • Drag the bottom right indicator down to apply it for each product (blue arrow).Drag the formula

Now our “Active” page is pulling in the values from the “Called” page. One of the benefits of using Vlookup is the products will still match up if both spreadsheets list them in a different order.

It also won’t get thrown off if a product is missing from the referenced spreadsheet.

VLOOKUP from Another Worksheet in a Different Workbook

The process of referencing data from a different workbook is a little more complicated.

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.

VLOOKUP used to fetch from another workbook

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

Another sheet in the same 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.

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.

Importrange to vlookup in another workbook

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

VLOOKUP used to fetch from another workbook

This can be extremely useful for someone analyzing the information in the first worksheet without any risk of error to 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.

Some Tips when Using VLOOKUP to Reference Another Sheet/Workbook

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

Be specific about the range

The 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")

Conditional IF statement

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.

VLOOKUP with IF fucntion

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.

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.

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

Leave a Comment