Best Ways to Use COUNTIF in google sheets : 2022
Google Sheet, Spread Sheet Often, stockholders request to reformat and summarize data to process and analyze instead of large data build. For example in sales
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.
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.
Following syntax and features can be used with the VLOOKUP in Google sheets Function.
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.
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
Download Sample SheetTo Import the example datasheet into your spreadsheet,
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,
=VLOOKUP(A2,'Freelancers Details'!$A$2:$F$16,4,false)
Let’s define the arguments for the VLOOKUP in Google sheets formula.
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)
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
=VLOOKUP(A2,
IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/1BQ3N_lDmDqbmvRLVuhRfJe0_QzUJ0KR9n-l9qo7Px4g/edit#gid=0",
"Freelance_Detail!$A$2:$F$16"
), 4,false
)
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.
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.
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
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
Google Sheet, Spread Sheet Often, stockholders request to reformat and summarize data to process and analyze instead of large data build. For example in sales
Google Sheet, Spread Sheet As a data analyzer every time you deal with unstructured data for different purposes which stakeholder needs. Sometimes you have to
Google Sheet, Spread Sheet Google sheets extremely powerful that built with over 200+ reusable functions and shortcuts. It allows to create table and fill data
Google Sheet, Spread Sheet Google sheets offer various formulas to implement with your data manipulation. Many times you have to implement checkboxes in google sheets.