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 Importrange. The 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.
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.”
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!
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.
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!