Hi Readers,
As we got some queries from our readers about importing data from one spreadsheet to another spreadsheet. Therefore we will use two important functions IMPORTRANGE() and VLOOKUP().We will understand about our IMPORTRANGE() in this article. We understand presenting data on conditions basis in our next post.
Let us follow below steps to import data from one spreadsheet to another spreadsheet in google sheet.
1.Create a file in google spreadsheet with named “student data” which have five columns Name , Gender , Roll No , Class & State.
2.Now , create another new file with named “student rewards” in which we try to import the class of the students who recieved the rewards.
We would require two most important function importrange() and vlookup().
In this article , we will just try to understand the importrange function.
importrange()
The importrange function has has two parameters as shown below.
=IMPORTRANGE(“1XEIIq-I3shSf4NmmrARYa1fu5SP8zgqUahpep4q06kg”,“C2:C250”)
It has two parameters. First for Spreadsheet ID and Second for Selected Range .
Let us know about them.
Spreadsheet id : This is id of the spreadsheet
Selected Range : This selected range of the columns.
Follow Steps:
3.Open spreadsheet named with “student rewards”.
Then Choose a cell and enter the below function.
=importrange(“spreadsheet id”,”selected range”);
Replace spreadsheet id of the file named “student data” . You can get spreadsheet id as shown below image.
Then Enter your range for selection as C2:C250. Please refer the below image.
When you proceed this function with enter. It will ask you to access the permission.
After that , You have imported data from the file “student data” as shown below image.
But our purpose is still incomplete. we needed the class of students So we use another formula i.e vlookup() .
We will get to know about in this vlookup in spreadsheet.
We hope this tutorial will help you to understand for importing the data from the spreadsheet into another spreadsheet sheet.
Vlookup()
Now Open our second file named with “student rewards”.
Vlookup has four parameters – Search key ,Range,Index,sorted as discussed below.
Search Key – it is the search column in the file with which we will search the column in other file. In our case, we have chosen all students in the column A2 as shown below image.
Data – it is the data that we have imported from our first file “student data”. In our case please copy this importrange function in second parameter : IMPORTRANGE(“1XEIIq-I3shSf4NmmrARYa1fu5SP8zgqUahpep4q06kg”,“C2:C250”)
Index-it is a address of the column in first file “student data” where desired column is mentioned. In our case , It is “D” Column in the first file “student data” because we need class column from the file “student data”.
Is sorted- it is a boolean (true/false) value if we need to get sorted data.
Please use the below function in a cell of the file “student rewards” you will get class of the rewarded student as shown below image.
=vlookup(A2,IMPORTRANGE(“1xcVJvsHfIvnsX4BFlkiwPfozqmLvJOrGddykmmw0qIU”,“C2:F250”),4,0)
Check out our another article for filter method