You might already know that VLOOKUP in Google Sheets returns a single value or multiple values from a row. But what if you need to return the VLOOKUP result plus the next n rows? By combining VLOOKUP with OFFSET, you can dynamically retrieve multiple rows below the lookup result.

This method is useful in various real-world scenarios:

• Fetching multiple transaction records after a specific entry.
• Retrieving product details or sales figures for subsequent dates.
• Handling merged cell data that spans multiple rows.

This guide will show you how to extract VLOOKUP results plus the next n rows in Google Sheets.

Using VLOOKUP and OFFSET to Return Multiple Rows

The VLOOKUP function alone can’t return multiple rows, but by pairing it with OFFSET, we can dynamically extract the lookup result plus additional rows.

Sample Data

Here’s a dataset (in A1:C) showing mango production (in million metric tons) for 2021 and 2022 in the top three producing countries:

We want to look up “Indonesia” and return both the 2021 and 2022 production values.

Example 1: Return VLOOKUP Result Plus Next N Rows (1D Lookup)

Formula:

=OFFSET(VLOOKUP("Indonesia", A1:C, 3, FALSE), 0, 0, 2)

How It Works:

• VLOOKUP("Indonesia", A1:C, 3, FALSE): Finds “Indonesia” and returns the value from column C (2022 production).
• OFFSET(..., 0, 0, 2): Extends the result to include the next row (2021 production).

Output:

4.1
3.6

Example 2: Return VLOOKUP Result Plus Next N Rows (2D Lookup)

Now, let’s return both the Year and Production.

Formula:

=OFFSET(VLOOKUP("Indonesia", A1:C, 2, FALSE), 0, 0, 2, 2)

Explanation:

• VLOOKUP(..., 2, FALSE): Fetches the year column instead of production.
• OFFSET(..., 0, 0, 2, 2): Extracts both Year and Production.

Output:

Fixing “Argument Must Be a Range” Error in VLOOKUP + OFFSET

If you get the “Argument must be a range” error, it’s because VLOOKUP only returns a value, not a reference when the lookup column is the first column.

Fix: Modify the index from 1 to 2 so that VLOOKUP returns a valid reference, then offset by -1 column:

=OFFSET(VLOOKUP("Indonesia", A1:C, 2, FALSE), 0, -1, 2, 3)

Output:

Alternative: Using MATCH + CHOOSEROWS Instead of VLOOKUP + OFFSET

If your data comes from dynamic ranges (e.g., QUERY, IMPORTRANGE, FILTER), VLOOKUP won’t work because it doesn’t return a cell reference. In that case, use MATCH + CHOOSEROWS instead.

Formula:

=LET(range, A1:C, rowN, MATCH("Indonesia", CHOOSECOLS(range, 1), FALSE), CHOOSEROWS(range, {rowN, rowN+1}))

How It Works:

• MATCH("Indonesia", CHOOSECOLS(range,1), FALSE): Finds the row number for “Indonesia”.
• CHOOSEROWS(range, {rowN, rowN+1}): Extracts the matched row and the next row.

Output:

Why Use This Alternative?

• Works with both physical ranges and array formulas.
• Supports dynamic data sources.

FAQ – Common Questions About VLOOKUP Returning Multiple Rows

1. Can VLOOKUP return multiple rows in Google Sheets?

No, VLOOKUP alone returns values from a single row only. However, by combining it with OFFSET, you can dynamically retrieve multiple rows.

2. Why do I get the “Argument must be a range” error?

This error occurs because VLOOKUP returns a value instead of a cell reference when the lookup and search values are in the same column. To fix this, ensure your lookup range includes at least two columns, even if you don’t use the second one. This allows us to offset by -1 column when needed.

3. Is there an alternative to VLOOKUP + OFFSET for multiple row lookups?

Yes! You can use:

• MATCH + CHOOSEROWS (Works with arrays and dynamic ranges).
• FILTER (If you need all matching rows, not just the next n).

Conclusion

Now you know how to return multiple rows using VLOOKUP in Google Sheets! Whether you’re dealing with transactions, schedules, or structured data, this technique will help extract the lookup result plus the next n rows dynamically.

Try it out, and let me know if you have questions!

Related Resources

• Retrieve Multiple Values Using VLOOKUP in Google Sheets
• VLOOKUP with Multiple Criteria in Google Sheets: The Proper Way
• XLOOKUP and Offset Results in Google Sheets
• How to Filter Next Row to the Filter Criteria Row in Google Sheets