How to Offset Match Using QUERY in Google Sheets

Published on

What does “Offset Match using QUERY” mean? It simply refers to offsetting a certain number of rows based on a matching keyword.

In Google Sheets, you can use the QUERY function’s OFFSET clause to shift rows. For instance, if you specify “OFFSET 5”, the query formula will offset 5 rows. In this case, the number 5 is the ‘given number of rows.’

But what if we replace that offset number with a formula using the MATCH function? What’s the benefit of this approach?

In a one-dimensional array, you can use a search key in the MATCH function to return the relative position of that key within a range. You can then use that result to offset rows in the QUERY.

Example of Offset Match Using QUERY

Let’s say we have sample data in the range A1:C. You can use the following QUERY formula to match the search key “Wednesday” in column A, offset up to that row, and return 1 row:

=QUERY(A2:C, "SELECT * LIMIT 1 OFFSET "&MATCH("Wednesday", A2:A, 0)-1)
Example of using MATCH with the OFFSET clause in a QUERY in Google Sheets

MATCH in the QUERY Formula’s OFFSET Clause

Actually, the above QUERY formula is an alternative to the INDEX MATCH combination! INDEX MATCH is a well-known formula pair for lookup and offset tasks.

  • The MATCH function finds a value, and INDEX uses the result from MATCH to offset the rows. That’s what the QUERY formula above does!

So, how would the INDEX MATCH formula look?

Here you go:

=INDEX(A2:C, MATCH("Wednesday", A2:A, 0))

Both formulas return the entire row. If you want to return a value from the third column, replace "SELECT *" with "SELECT C" in the QUERY formula and use C2:C in the INDEX formula.

Formula and Explanation

In both formulas (the INDEX MATCH and QUERY MATCH), the same MATCH formula is used.

Example of using MATCH to return a relative position in Google Sheets

Here’s the syntax for MATCH in Google Sheets:

MATCH(search_key, range, [search_type])

In this case, the search key used in MATCH is “Wednesday,” and its relative position in the dataset is 4.

  • In INDEX, you can use this 4 directly (as shown in the formula above), which will return the value from the 4th row.
  • In QUERY, you need to offset 3 rows. If you set the offset number to 4, the formula will offset 4 rows, and you would return the value from the 5th row.

That’s why we use the MATCH formula like this in the QUERY:

MATCH("Wednesday", A2:A, 0) - 1

The minus 1 accounts for the offset.

So, the QUERY formula becomes:

=QUERY(A2:C, "SELECT * LIMIT 1 OFFSET 3")

Since the offset number is part of the query string, we close the string before the offset number and combine the result of the MATCH function with it using &.

What’s the Benefit of Using Offset Match with QUERY over INDEX MATCH?

The QUERY function is more flexible. You can modify the LIMIT clause in the QUERY to return a certain number of rows after the offset.

Example Formula 1:

=QUERY(A2:C, "SELECT * LIMIT 2 OFFSET "&MATCH("Wednesday", A2:A, 0)-1)

This will return 2 rows starting from the match.

Example Formula 2:

=QUERY(A2:C, "SELECT * OFFSET "&MATCH("Wednesday", A2:A, 0)-1)

In this formula, we exclude the LIMIT clause. This will offset the first 3 rows and return the rest of the rows without a limit.

This means “Offset Match using QUERY” in Google Sheets has an advantage over INDEX MATCH when it comes to data manipulation and returning multiple rows.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

2 COMMENTS

  1. Thank you for the article! I’ve managed to get it to work (the offset & match), but my select statement is using an aggregating formula (SUM in my case) and therefore I have to get rid of the automatic label by forcing the "label SUM(B) '' " (empty string).

    Now, the expected order seems to be “offset” first and “label” next… How do I go about it?

    Example query:

    =query(A:C,"select SUM(B) where A = 'Team_ABC' offset "&match("2020-W47",C:C,0)-1)

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.