The STARTS WITH operator is a string comparison operator used for prefix matching in the Google Sheets QUERY function. Understanding its role is essential when you want to filter rows based on how text begins in a column.

This complex string comparison operator allows you to filter rows that contain partial text matches at the beginning of a string. In other words, it helps you include or exclude values based on a specific starting pattern.

For example, you can use it to filter names that start with or do not start with a specific letter, word, or substring in a column.

Both STARTS WITH and NOT STARTS WITH are explained here in detail as part of the String Matching in Google Sheets QUERY guide.

How to Use the STARTS WITH Prefix Match in Google Sheets Query

In the sample data below, the STARTS WITH prefix match is applied to the second column.

• The first column contains employee names
• The second column contains their assigned departments

Requirement

Filter all employees who are working in departments that start with “Engineering”.

Formula

=QUERY(A1:B, "SELECT * WHERE B STARTS WITH 'Engineering'", 1)

Result Explanation

This formula returns all rows where the department name starts with “Engineering”, including values such as:

• Engineering – Civil
• Engineering – Mechanical
• Engineering – Electrical

This makes Starts with Prefix Match in Google Sheets Query ideal when your data contains structured naming patterns.

Why Use STARTS WITH Instead of LIKE or MATCHES?

While STARTS WITH is not the only string comparison operator available, it is often the best choice for prefix-based filtering.

You can also use:

• MATCHES (regular expressions)
• LIKE (pattern matching)

However, for simple prefix matching, STARTS WITH offers better performance and cleaner formulas.

How to Use NOT STARTS WITH Prefix Match in Google Sheets Query

Now let’s look at the opposite scenario.

Requirement

Filter all rows where the department does NOT start with “Engineering”.

Formula

=QUERY(A1:B, "SELECT * WHERE NOT B STARTS WITH 'Engineering'", 1)

Important Note

Pay attention to the position of the NOT operator.
It must be placed before the column identifier, not before STARTS WITH.

✅ Correct

WHERE NOT B STARTS WITH 'Engineering'

❌ Incorrect

WHERE B NOT STARTS WITH 'Engineering'

Case Sensitivity in STARTS WITH Query

By default, string matching in Google Sheets Query is case-sensitive.

To make it case-insensitive, use scalar functions such as LOWER() or UPPER().

Example (Case-Insensitive Prefix Match)

=QUERY(A1:B, "SELECT * WHERE LOWER(B) STARTS WITH 'engineering'", 1)

This formula returns rows that start with:

• Engineering
• engineering
• ENGINEERING

…regardless of the text case.

Note: If you use LOWER(), ensure the comparison text is also in lowercase.
If you use UPPER(), use uppercase text instead.

Using Cell References with STARTS WITH in QUERY

Instead of hardcoding the prefix inside the formula, you can place it in a cell and reference it dynamically.

For example, if cell D1 contains the prefix:

Engineering

You can use:

=QUERY(A1:B, "SELECT * WHERE B STARTS WITH '"&D1&"'", 1)

This approach makes your formulas more flexible and reusable, especially in dashboards and dynamic reports.

A detailed explanation is available here:
How to Use Cell Reference in Google Sheets Query

Conclusion

The Starts with Prefix Match in Google Sheets Query is a powerful and efficient way to filter data based on how text begins in a column.

Key Takeaways:

• Use STARTS WITH for fast and clean prefix matching
• Use NOT STARTS WITH to exclude specific prefixes
• Prefer STARTS WITH over LIKE or MATCHES for prefix matching, as it avoids wildcard or regex-style pattern processing.
• Apply LOWER() or UPPER() for case-insensitive matching
• Use cell references for dynamic and scalable formulas

Mastering this operator will significantly improve your ability to work with structured text data in Google Sheets.