Google Sheets | QUERY | Function | MATCHES Operator | WHERE Clause | Example | Spreadsheet Tutorial

The Google Sheets QUERY function's WHERE clause and MATCHES operator assists to get data based on conditions. Use MATCHES to conduct exact or wildcard search. This operator uses regular expressions, which are search templates with a set of characters like . and *, to conduct search.
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 and starts with or ends with operators in Google Sheets?
Use where clause and starts with, ends with or both the operators to get data based on one or more conditions:
• 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 ...
-------------------------------------
The format of the QUERY function formula is:
=QUERY(data, query, [headers])
Use the WHERE clause and matches operator in the query part of the formula.
Let's look at some examples:
Example 1
Assume A2 to A100 has names of students.
Further assume you want to extract all the data of students whose first name is William.
The QUERY function formula with where clause and matches operator is:
=QUERY(A2:A100, "select * where A matches 'William'")
Here, select * where A matches 'William' means, return all the data if data in
column A matches William.
Example 2
Say you want to get all the data of students whose name starts with the letter S.
The QUERY function formula is:
=QUERY(A2:A100, "select * where A matches 'S.*'")
.* is a regular expression, which specifies that the first letter of the name should be S and any and all characters that follow this letter should be matched.
For example, Sam, Sophia, and Smith will be matched.
Example 3
Assume a database of part numbers, part name, cost/item, and other related data has part numbers in the range A2:A341. Say you want to extract all the data, relating to part numbers, which have the text PT3.
The QUERY function formula is:
=QUERY(A2:A341, "select * where A matches '.*PT3.*'")
Here, .*PT3.* means any and all data before and after the string PT3 should be matched.
AQPT3Z1, RL15PT3, NPT3J62, for example, will be matched.
Review this video tutorial, which gives the step to use the Google Sheets
QUERY function with the WHERE clause and matches operator, with examples.