VLookup is a very commonly used & useful function in excel and I also do always use especially I require to look up certain columns of data to retrieve another column of data if another tab or excel sheet. But a few years back, the company I work for switched over to Gsuite and I seldom use this vlookup in Google Sheets.
Until I did some research and in fact, it also works as almost similarly to Excel too. If you want to vlookup from another sheet in your google sheet, I will try to make it a simple and easy-to-follow guide.
So in this article, I will show you how to vlookup from another sheet in your google sheet. Keep reading to learn more!
Table of Contents
What is Vlookup in MS Excel & Google Sheets?
Vlookup aka Vertical Lookup is a function in MS Excel & Google Sheets that allows you to look up a value in a column or row. You can use it to find specific values or to find a value in a range of cells.
It is a valuable function to have in your spreadsheet, especially you dealing with huge data, and it can be used to find a variety of values. Try out Vlookup in your next spreadsheet project to see how it can help you get the results you’re looking for.
How to Vlookup in the same google sheet?
Before we do a vlookup of different google sheets, we will start off with the easy one to look up on the same google sheet but in different tabs.
In this simple tutorial, I created a google sheet called “Testing – Source” and this will be source data under the Source tab which I need to lookup the value and retrieve the data in the other columns like salary, bank account info etc.
As you can see in the screenshot below – In total, there are 11 rows of data excluding the header.
In the google sheet, Testing -Source but under this tab called Sheet 2 and I have a list of names
Step by Step Tutorial with Screenshots
Step 1:
In the sheet 2 tab, let’s say I would like to retrieve the data of their Salary in the Source tab.
Firstly, I will go to the B1 cell where I want the data to be retrieved and type:
=vlookup
Step 2:
There are a four parameters like search_key, range, index and is_sorted for the VLOOKUP you will need to input. I will briefly go through each of these parameters below:
search_key – It is used to lookup a specific value or reference to a cell that contains a value. This key is used to look up the required information. So for my case, I will select & click the A1 cell which is James and press, to proceed to define the range parameter.
range – The VLOOKUP function will search within a range of cells in the source table. This range should include the column with the search key and the column with the value to be retrieved. Usually, VLOOKUP will search the first column of the range to find the search key.
So in this case, the source table will be the Source tab hence I will click & highlight the entire data from A1 to E12 and press, to proceed to define the index. You can refer to the screenshot below
index – The column number of the value corresponding to the search_key, which is located in the same row, should be retrieved from the range. The index of the first column within the range is 1, the second column has an index of 2, and so on.
So in this case based on my Source tab – The name column will be 1, the Salary will be 2, and so on.. This time, I would like to retrieve the Salary then I will put 2 and a comma to proceed with the last parameter – is sorted.
Lastly, the is_sorted parameter is an optional value and is either True or False. The default will be False.
If the parameter is_sorted is set to TRUE, then the first column of the range must be sorted in ascending order for VLOOKUP to work properly.
If an exact match of the search_key is not found, VLOOKUP will search for the closest match. If there is more than one value that is exactly equal to search_key, then VLOOKUP will access the first occurrence of the search_key.
In this case, I will put 2 which is false. If you input 1 it will be true. So this is my entire vlookup looks like in the B2 cell
=VLOOKUP(A1,Source!A1:E12,2)
As you can see below, for B2 cell value is retrieved when it does look up in the A1 cell which James from the Source tab to retrieve the value (index = 2 which is Salary)
Lastly, to retrieve the other 4 people’s salary value, you can click on the small blue box and simply drag it down to the B5 cell.
In this way, the google sheet will automatically apply the same formula to the rest of the cells in column B. Below is the final output:
How to VLOOKUP From Another Sheet in your Google Sheet?
To do VLOOKUP from another Google sheet, it will be the same except when you define the range value, then require to need an additional function called IMPORTRANGE
IMPORTRANGE(spreadsheet_key, range_string)
To use IMPORTRANGE, there are 2 parameters you will need to define
a. spreadsheet key – it will be the URL of the Google Sheet (referring to the source) that you need to lookup to.
b. range_string – it is similar as the range in the earlier example.
Example of using IMPORTRANGE to VLOOKUP on another google sheet
In this example, I will create a new google sheet called IMPORTRANGE and similarity will lookup from the the B1 cell
I will start off with VLOOKUP with the index of A1 cell which is James again and followed by IMPORTRANGE
Then go back to the google sheet called Testing – Source, copy and paste the entire URL
Go back again to your IMPORTRANGE google sheet and paste the entire URL with the double quotes (“) required before and after the URL. It will look something like this:
Next, you will need to put a comma (,) after the URL pasted.
We proceed to use double quotes (“) again to input the tab name called Source in this example from the Testing – Source google sheet.
Then followed by an exclamation mark, ( !)
After that, you defined the range from A1:E12. At this stage, it will look something like this:
=VLOOKUP(A1,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/aaadf9CTTPPMTjXu9ZViOLfRPdFUdKgUa5ovemvEYyl3g/edit#gid=0″,”Source!A1:E12”)
Similarly to the last example, it will index the Salary information which is 2
Lastly, I will do a false to define for the is_sorted and press ENTER
It should look something like this
And you should see something like this with this REF! is your B1 cell.
Not to worry, simply click on the red arrow in the cell and you prompted to this message below:
Click on the Allow access button and you will see the result:
That’s all and this is how you can VLOOKUP on another google sheet by using IMPORTRANGE function.
Final Thoughts
I hope you found it useful and if you have any questions, please let me know in the comments. Thanks for reading!