In this article, you will discover what the OFFSET function is and how to use it effectively in Google Sheets.
What is the OFFSET Formula in Google Sheets and Why Do We Use It?
The OFFSET function returns a cell or a range that is displaced by a certain number of rows and columns from a specified cell in the formula. This formula is particularly handy when you need to retrieve information from a dynamic range or when you don’t know the exact address of the range you want to refer to, but you know the starting cell. We understand that grasping how this function works in Google Sheets may seem challenging. So, let’s dive into the next section to learn how it works through some examples.
How to Use the OFFSET Formula in Google Sheets
-
Type “=OFFSET(” or go to “Insert” → “Function” (or directly access the “Functions” icon) → “Logical” → “OFFSET.”
-
Set a starting point from which a reference range moves by the specified number of rows and columns in the other arguments.
-
Specify the number of rows and columns by which the starting point moves from the original cell.
-
Determine the size of the range that is returned by the OFFSET formula.
-
Press the “Enter” key.
The general syntax is as follows:
- Cell_reference: This is the initial address from which a cell moves by a certain number of rows and columns (specified in the next two arguments).
- Offset_rows: This number should be an integer. The specified range moves by the number of rows defined in this argument.
- Offset_columns: This argument should also be an integer. The specified range moves by the number of columns.
- Height [optional]: The height of the range.
- Width [optional]: The width of the range.
Note that “offset_rows” and “offset_columns” can be negative integers, but if the target destination is outside the spreadsheet, the OFFSET formula returns “#REF!”. Additionally, the displaced range should not overlap with the same-sized range starting from the starting points as it causes a circular reference.
Let’s explore some examples to illustrate how the OFFSET function works in Google Sheets. In the first example, we’ll explain how the first argument (“cell_reference”) and optional arguments (“height” and “width”) function because they are easier to understand. Then, we’ll delve into how the remaining parameters (“offset_rows” and “offset_columns”) affect the result.
Example 1
The arguments for this example formula and their descriptions are as follows:
- Cell_reference: B3
- Offset_rows: 0
- Offset_columns: 0
These three mandatory arguments mean that the starting point (cell B3) remains unchanged and propagates, and if optional arguments are entered, the specified range propagates from cell B3.
- Height [optional]: 3
- Width [optional]: 4
The way these two optional arguments work is visualized in the image above. The green boxes illustrate how the “height” argument affects the output range, and the red boxes show how the “width” modifies the output. As a result, a 3×4 table starting from cell B3 is specified, and the range propagates from cell B11, where the OFFSET formula is inserted.
Example 2
The arguments for this example formula and their descriptions are as follows:
- Cell_reference: A14
- Offset_rows: 2
- Offset_columns: 1
These three mandatory arguments mean that the starting point (cell A14) moves to cell B16 because the “offset_rows” and “offset_columns” arguments indicate that the starting point moves down by two rows (visualized by the arrow and dark blue box) and right by one column (visualized by the arrow and purple box).
- Height [optional]: 6
- Width [optional]: 2
Due to these two optional arguments, a range with a size of 6×2 starting from cell B16 is specified, and the table propagates from cell B24, where the OFFSET formula is inserted in this example.
How to Use the MATCH Function with the OFFSET Function?
The OFFSET formula is often combined with other functions to maintain the dynamic output brought by the OFFSET function. Here, we present one of the combinations. Let’s say you have monthly revenue data per company, and you want to extract the revenue of a specific company for a given month (e.g., the revenue amount of “Potato” in “March 2022”). Refer to the following screenshot to learn how to create a formula that returns the information by combining the OFFSET and MATCH functions.
The arguments for this example formula and their descriptions are as follows:
- Cell_reference: B32
- Offset_rows: MATCH($C$39, B33:B37, 0) – This MATCH function sets the number of rows the starting point moves down. The MATCH function returns 2 because “Potato” is the second row in the selected range in the formula.
- Offset_columns: MATCH($C$40, C32:H32, 0) – The second MATCH function determines the number of columns offset from the initial point. This MATCH function returns 3 because “Mar 2022” is in the third column of the chosen range in the formula.
In summary, these three mandatory arguments indicate that the starting point (cell B32) moves to cell E34 (B+”3″=E and 32+”2″=34) because the “offset_rows” and “offset_columns” arguments indicate that the starting point moves down by two rows (visualized by the arrow and dark blue box) and right by one column (visualized by the arrow and purple box).
Note that the “height” and “width” arguments are empty and considered null.
In this example, OFFSET is used to search for a specific value. The combination of INDEX and MATCH functions is also often used for this purpose. Check out this article to learn how to create a dynamic lookup: How to Create Dynamic Dashboards with Index/Match. If you’re interested in other formulas for extracting specific information, read the following articles.
