When you deal with a large data set, you can’t keep all data in the same sheet. You have to create interrelated data to speed up data analysis. In large data use normalization methods to ensure only related data is stored in each sheet. They use unique keys to build relationships with each set of data. In google sheets, VLOOKUP otherwise Vertical lookup is used for bringing data from one table to another table based on the relational key (unique key). Both tables need to use the same key to build links with each other.
This is the basic step to create a VLOOKUP in google sheets.
- Open a spreadsheet in Google Sheets.
- Select sheet (data table) & Identify column position(count left to right from column A:) which you need to fill data from Specify the datasheet (specified range).
- Specify the data table you decide to link ( make sure both have the same unique keys )
- Then select the first cell which you need to fill data and type =VLOOKUP(search_key, range, index(column count infill data table), [is sorted or not])
- Then VLOOKUP returns the value of a specific cell & fills in the selected cell.
In this article, describe what is VLOOKUP in Google sheets and how to use it with data sheets with practical examples and frequency issues you have to face with doing in real-world situations.
Contents
How VLOOKUP in Google sheets is working?
As mentioned before, an organization every time works with a large set of data and it keeps as an interrelation to speed up manipulation and analysis results. Actually google sheet acts as a simple database, sometimes you have to use a data set in a different datasheet, which is why interrelation data is more important to avoid generating duplicate data.
ok, let’s take a relational database. It is the main approach to creating meaningful information by joining the tables. To communicate each table using primary key & foreign key to describe how to build the relationships between the data. The primary key is used to describe that specific column unique to the table. cannot be duplicated. The foreign key is used for grouping data in relational data tables that provide two tables.
Let’s check how to work this relational part in google sheets
Based on the above example there are 2 tables, the student details table has students use as a unique key, and all student department table data grouping on student Id. that means student departments use studentId as a foreign key and join with each other. VLOOKUP in Google sheets Function represents a SELECT JOIN query in the relational database. That Function is used for returned data to fill the same row from another column which given range.
Features & Syntax of VLOOKUP in Google sheets
Following syntax and features can be used with the VLOOKUP in Google sheets Function.
-
- Search_key: search key (Unique key) is the value that is used for search data in the relational (Join) table using VLOOKUP in Google sheets.
- Range: the range is two or more data columns that are used for search data in the Vlookup function. It always starts with the key specified in search_key and automatically combines with each row.
- Index: this is the column position of the relation(Join) table that is used to value return. It counts left to right and always starts number 1.
- Is_sorted: sort is an indication of whether a column needs to be ordered or not. by default, it’s TRUE then the column will be returned to the database in ascending order ( from A to Z or from smallest to largest) but most of the time recommended to keep it as FALSE because auto sorting query will reason to kill your app performance and it will affect to optimize data processing performance.
How to use VLOOKUP in google sheets from a different Tab & Sheet ?
I hope you can get a good idea of vlookup and the behavior of features and syntax. Then let’s start with how to use VLOOKUP in Google sheets to accomplish your task.
About example
Let’s take a small software development company, This is a Startup company and all development team members are freelancers. Their work hour base and each freelancer has his own agreed hour rate with the company. They receive payment each month base count of working hours multiplied by hour rate. Company owner maintains google sheet for month base data sheet and he keeps freelancers data in separate tab and monthly payments in another tab.
Hour Base Payment (January 1 to 31) Tab – this tab sheet using for update number of hour each freelancer
Freelancers Details Tab – Each Freelancer detail with their agreed hour rate.
– Company owners need to get an hourly rate into the monthly payment sheet to calculate each freelancer total payment in each month
To Import the example datasheet into your spreadsheet,
- Open a spreadsheet in Google Sheets.
- On the top of navigation click File > Import (import datasheet)
VLOOKUP in Google sheets from another Tab
Let check how to organize both tables and how to implement VLOOKUP in Google sheets to get the hour rate into the payment tab
At first sight, You can see that both tables join using freelancers’ detail table studentId. The payment table uses a student ID to group each row. So we can use a unique key as studentId to search and group data.
Second sight, hour base payment looking to fill hour rate from freelancers detail table. In google sheets, vlookup needs to know which index contains that column data. Column positions need to count left to right and start from column A and always start number 1.
Excellent now know,
- unique key ( search key) – Student Id can use search value (start from A2)
- Rage – we already know that is freelance detail A1 to F16 contain our data range
- Index – column 4 in freelancers detail (Lookup Table ) contains hour rate.
- Is sort – keep as sorting FALSE because our hour rate table student id not sorted
$ sign absolute reference that used to row or column coordinate $A$2:$F$16 means that pay attention that we lock the range in formula & row and column will not change when you copie.
Let’s define the arguments for the VLOOKUP in Google sheets formula.
- Open a spreadsheet in Google Sheets.
- In Hour Base Payment (January 1 to 31) tab, select Hour Rate first cell to insert vlookup Function.
![select Hour Rate first cell to insert vlookup Function - google sheets vlookup]()
- Insert =VLOOKUP(A2,’Freelancers Details’!$A$2:$F$16,4,false) in HourRate($) column(C2)and press enter.
![.insert formula in vlookup - google sheets vlookup]()
- Now you can see that in C2 cell fill unique key-related freelancer hour rate.
- To implement for all cells in column C select C2 cell then click the square icon in the bottom right then drag till the last row which data exit.
![result in vlookup - google sheets vloopup]()
Note: to make bit professional, in Hour Base Payment (January 1 to 31) table implement some calculation using google sheets Functions . in column D (Total $) calculate the number of working hours multiply by hour rate (=B2*C2. In D12 calculate the total hour payment in January month =SUM(D2:D10)
VLOOKUP in Google sheets from another Sheet ( different location)
We all know that in real-life situations an organization doesn’t keep data in the same datasheet. It will be store different sheets in different locations. May we have to join that different location datasheet to get the actual result. In google VLOOKUP in Google sheets Function cannot access the external sheet itself. We need to use the support Function to import the sheet intoVLOOKUP in Google sheetsFunction and return lookup value into the spreadsheet.
To use google sheets vlookup from a different location, embed the IMPORTRANGE function in the vlookup argument.
IMPORTRANGE in google sheets use for Imports a range of cells from a specified spreadsheet. Make sure that you may need to get granted permission to pull data from other spreadsheets using IMPORTRANGE Function.
Let’s define the arguments for the google sheets Vlookup & IMPORTRANGE Function
Note : I move Freelance detail into an external sheet & enable grand permission to the lookup table & replace query like this
I just replace the sheet range part and replace with IMPORTRANGE Function. Then Insert that query into HourRate($) column(C2)and press enter.
Now you can see that in C2 cell fill unique key-related freelancer hour rate.
Frequency Problem in VLOOKUP in Google sheets
Can Vlookup be case-sensitive?
VLOOKUP in Google sheets isn’t case-sensitive and doesn’t handle case-sensitive lookup values. Example same unique key value multiple times in sheet, vlookup will always return 1st matching value.
How does Error handling (if not exist ) in VLOOKUP in Google sheets?
This is a native problem with every formula and we cannot where error can appear. To resolve that, You can implement Vlookup inside the IFERROR formula and your Vlookup will execute if it is doesn’t contain an error value, otherwise, it returns blank or your return value in IFERROR.
Still don’t you have any idea about how to use If Function in google sheets, we highly recommend reading Right Way To Use Google Sheets IF Function ( With 6 Helpful Tips ) Article,
That’s it. I hope this article will help to get a good idea about the usage of VLOOKUP in Google sheets and how to usage with multiple situations
Conclusion
When you work with interrelational data. May time you have to build relationship with each other. Google sheets more modern now and they introduce a special function call VLOOKUP in Google sheets. It helps to build interrelation with the sheet in same spread sheets and also differences location
