Vlookup Between Two Separate Google Sheets

Published on

You can use the function Vlookup between two separate Google Sheets files. Let’s learn how to Vlookup from another sheet with examples.

I am using two of the Google Sheets functions for this. No doubt one is the Vlookup, and the other is the ImportrangeThe function Importrange acts as the range in Vlookup from another Sheet.

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

Suppose you have two Google Sheets files named “Test1” and “Test2.” Let’s see how to do a vertical lookup in “Test1” from “Test2.”

That means the formula will be in “Test2,” and the data range in it will be in “Test1.”

How to Perform Vlookup Between Two Separate Google Sheets Files

Data in the “Test1” sheet.

Sample data to test Vlookup and Importrange Combo

This data shows the rank of countries by historical GDP (source). Please don’t waste your time typing this data. You can import this data.

For that, you may apply this IMPORTHTML formula in cell A1, in “Test1,” which will probably import the above source data.

=importhtml("https://en.wikipedia.org/wiki/List_of_countries_by_largest_historical_GDP","table",2)

The first column in this sample data contains the years. It will be our lookup/search column.

The second column contains the name of the first ranked country and its GDP.

The third column contains the name of the second-ranked country and its GDP, and so on.

My search key would be any specific year(s).

I want to return the corresponding country name and GDP in the specified column index.

We will go to Vlookup from another sheet later on. Let’s consider a single sheet this time.

If the formula is in “Test1,” it would be like this.

Formulas in Test1 (Single Workbook)

1. Vlookup Formula 1 within “Test1” (L2):

=vlookup(2015,A1:K,2,false)

It returns the column 2 (1st rank) rank, i.e., the value in cell B2.

2. Vlookup Formula 2 within “Test1” (M2):

=ArrayFormula(vlookup(2015,A1:K,{2,3,4,5,6,7,8,9,10,11},false))

It returns the values in columns 2 to 11, i.e., the values in the range B2:K2, in M2:N2.

Instead of typing the numbers (column index) in sequence, you can use the Columns function.

Tips: Use of COLUMNS Function in Vlookup in Google Sheets.

If you are not familiar with this type of formula, I mean Vlookup with multiple columns, you may please read this guide.

Must Check: Multiple Values Using Vlookup in Google Sheets is Possible [How to].

Also, you have the choice to use multiple search keys like;

3. Vlookup Formula 3 within “Test1” (L3):

=ArrayFormula(vlookup({2010;2015},A1:K,2,false))

Want to learn this type of vertical lookup? Then follow the below link.

You May Like: How to Use Vlookup to Return An Array Result in Google Sheets.

Can I use all these Vlookup formulas between two workbooks or two separate files in Google Sheets?

Yes! Here we go.

How to Do Vlookup from Another Sheet in Google Sheets

Now I am in my second Google Sheets named “Test2,” and I am applying all the above three formulas in this sheet.

Steps to Follow:-

First, copy the URL of the sheet “Test1.”

Importrange Tips in Vlookup

Then enter this formula in any cell in Sheet2, i.e., in “Test2.”

=importrange("URL","Sheet1!A1:K")

Please replace the URL with your copied URL.

I have the said formula in cell C2. It returns error #REF!

Importrange Allow Access Error [Solved]

The formula requires access to “Test1” that you can give as follows.

Just point your mouse on cell C2 and click the “Allow access” button.

Here are the formulas to Vlookup between two separate Google Sheets workbooks/files.

1. Vlookup from Another Sheet (Formula 1 within “Test2”):-

Double click on cell C2, where our Importrange formula resides, and modify it as follows.

=vlookup(2015,importrange("URL","Sheet1!A1:K"),2,false)

It is the same formula I’ve used in the first workbook, i.e., in Test1!L2.

You can scroll up on this page and see that formula.

Here I have just replaced the range A1:K with the Importrange formula.

In short, to Vlookup between two separate Google Sheets, just replace the range with IMPORTRANGE.

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

In the same way, you can use the above “Test1” formula 2 and 3 in “Test2.”

Here is one more example to Vlookup from another sheet in Google Sheets.

Vlookup Between Two Separate Google Sheets and Multiple Search Keys

In all the above examples, the search keys are hard-coded.

They are hand-entered within the formula instead of cell references.

Here I have entered them in cells A2, A3, and A4. That means the years to lookup are in cell range A2:A4 in “Test2.”

For the formula usage, please see the below image.

Vlookup Between Two Workbooks in Google Sheets

When there are multiple search keys, you should wrap the Vlookup with ARRAYFORMULA. It’s in line with formulas 2 and 3 in the “Test1” sheet.

That’s all about using Vlookup with IMPORTRANGE for Vlookup from another sheet in Google Sheets. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.