Google Sheets | QUERY | Function | Extract Specific Data | Example | Spreadsheet | Tutorial
In Google Sheets, QUERY is a function that helps extract specific data from a spreadsheet data range. For example, you can extract specific data, say from a bank statement, for such tasks as preparing the income tax return or creating a budget.
For more info on QUERY function, please refer to the tutorial below.
-------------------------------------
How to Use QUERY in Google Sheets?
QUERY helps, among others, to extract specific or all data from a range:
• Google Sheets | QUERY | Function | Ex...
-------------------------------------
How to Use Convert to Table in Google Sheets?
Convert a data range into a table easily:
• Google Sheets | Convert to Table | Ch...
-------------------------------------
How to Use FREQUENCY in Google Sheets?
FREQUENCY returns the frequency distribution of data:
• Google Sheets | FREQUENCY | Function ...
-------------------------------------
How to Use COUNTIFS in Google Sheets?
Count values that fulfill one or more criteria:
• How to use COUNTIFS in Google Sheets ...
-------------------------------------
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 | XLOOKUP | Function | ...
-------------------------------------
How to Use VLOOKUP in Google Sheets?
Use VLOOKUP to get a single value:
• VLOOKUP Google Sheets | How to Use VL...
-------------------------------------
How to Use IFS in Google Sheets?
IFS allow multiple logical expressions:
• Google Sheets IFS | Test Multiple Con...
-------------------------------------
How to Use IF in Google Sheets?
Use IF to compare one value with another:
• Google Sheets IF | Tutorial | IF Goog...
-------------------------------------
How to Create a Pivot Table in Google Sheets?
Create a pivot table for calculation and in-depth data analysis:
• Google Sheets | Pivot Table | How to ...
-------------------------------------
What is the Syntax of the QUERY Function Formula?
The syntax is:
=QUERY(data, query, [headers])
data is the range from which to extract data.
query is the command, constructed using the Google Visualization API Query Language, to extract data.
headers is optional, and specifies the number of header rows.
How to Extract Data using QUERY?
Let's assume you want to extract the amount credited as dividend from your bank statement. Let's further assume that the information in the Description column of the statement, for the dividend credit, starts with ACH/.
The QUERY function formula for the above task is:
=QUERY(E3:G7,"select G where E matches '.*ACH/.*'")
Here E3:G7 is the value for the data argument and is the address of the range with data.
"select G where E matches '.*ACH/.*'" is the value for the query argument.
The command specifies to select the value in the cell in the G column corresponding to the cell in the E column, if the data in that cell has the text ACH/. The characters . and * preceding and following the text ACH/ specify that any text can precede or follow ACH/.
IMPORTANT: While specifying the string to be matched, ensure that is fully unique to the data you want to extract. Otherwise, unrelated data would also be extracted.
Review this video tutorial, which gives the steps to use the Google Sheets
QUERY function to extract specific data, say from a bank statement, with examples.