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)

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.

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.
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)
Hi, Anastasia,
I fear you are using the formula wrongly. If you explain the purpose I may be able to offer my free assistance.