Рет қаралды 148
Use the Google Sheets QUERY function's WHERE clause and starts with and ends with operators to extract data based on conditions.
For an intro to QUERY function, please refer to the tutorial How to Use Query in Google Sheets whose link is given below.
-------------------------------------
How to Use QUERY in Google Sheets?
QUERY helps, among others, to extract specific or all data from a range:
• Google Sheets | QUERY ...
-------------------------------------
How to Use QUERY with WHERE clause in Google Sheets?
To conditionally extract data, use the QUERY function with its WHERE CLAUSE:
• Google Sheets | QUERY ...
-------------------------------------
How to Use XLOOKUP to Extract Multiple Values in Google Sheets?
XLOOKUP can return a single row or column with the search key:
• Google Sheets | XLOOKU...
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to get a single value:
• VLOOKUP Google Sheets ...
-------------------------------------
How to Use IFS in Google Sheets?
IFS allow multiple logical expressions:
• Google Sheets IFS | Te...
-------------------------------------
How to Use IF in Google Sheets?
Use IF to compare one value with another:
• Google Sheets IF | Tut...
-------------------------------------
How to Use IF with AND in Google Sheets?
Use IF with AND to Use Multiple Logical Expressions in IF:
• Google Sheets | IF | A...
-------------------------------------
How to Create a Pivot Table in Google Sheets?
Create a pivot table for calculation and in-depth data analysis:
• Google Sheets | Pivot ...
-------------------------------------
Which one to Use? A Pivot Table, or COUNTBLANK Function, to Count Blank Cells in Google Sheets:
Gives steps to use a pivot table or COUNTBLANK to count blank cells:
• Google Sheets | Pivot ...
-------------------------------------
How to Use a Pivot Table to Sum by Rows or Columns without using Formula in Google Sheets?
Sum numbers by rows and or columns easily, using a pivot table:
• Google Sheets | Pivot ...
-------------------------------------
The format of the QUERY function formula is:
=QUERY(data, query, [headers])
Include the WHERE clause and starts with and ends with operators in the query part of the formula.
Here are some examples:
Example
Assume column B of the range B2 to E15 has names of students.
Say you want to extract data of students whose first name is John.
The QUERY function formula with where clause and starts with operator is:
=QUERY(B2:E15, "select * where B starts with 'John'")
Here, select * means select everything.
where B starts with "John" means the cells in column B should start with "John".
The formula will extract all the data, corresponding to the cells in column B that fulfill the above condition.
Example
Assume column C of the range A2 to C20 has mobile numbers.
Further assume that you want to extract all mobile numbers that end with 33.
The QUERY function formula with where clause and ends with operator is:
=QUERY(B2:E15, "select C where C ends with '33'")
Note that this example extracts data only from column C, if the mobile number ends with 33.
Check out this video tutorial, which gives the step to use the Google Sheets
QUERY function with the WHERE clause and starts with and ends with operators, with examples.