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.

Resources

• Using OFFSET and MATCH Together in Google Sheets: Advanced Tips
• How to Use OFFSET and XMATCH Functions Together in Excel
• Slicing Data with XLOOKUP in Google Sheets
• Excel OFFSET-XLOOKUP: Better Alternative to OFFSET-MATCH
• Match Function in QUERY Where Clause in Google Sheets