OFFSET
TheOFFSET
formula in Google Sheets returns a cell or range of cells that is a specified number of rows and columns from a starting cell reference. This formula is most commonly used to create dynamic ranges that can expand or contract as data is added or removed from a sheet.
 How to use
OFFSET
formula?  Examples of using
OFFSET
formula OFFSET
formula not working? Similar formulas to
OFFSET
Usage
Use the OFFSET
formula with the syntax shown below, it has 3 required parameters and 2 optional parameters:
 cell_reference (required):
The starting cell reference from which the offset is measured. This can be any valid cell reference.  offset_rows (required):
The number of rows to offset from the starting cell reference. This value can be positive or negative, and can be an expression or a reference to another cell.  offset_columns (required):
The number of columns to offset from the starting cell reference. This value can be positive or negative, and can be an expression or a reference to another cell.  height (optional):
Optional. The height of the range of cells to return. Defaults to 1 if not specified.  width (optional):
Optional. The width of the range of cells to return. Defaults to 1 if not specified.
Examples
Here are a few example use cases that explain how to use theOFFSET
formula in Google Sheets.
Creating dynamic ranges
By using the OFFSET
formula with a dynamic range reference, you can create a range that automatically expands or contracts as data is added or removed from a sheet.
Navigating large datasets
The OFFSET
formula can be used to navigate large datasets by returning a range of cells that is a specified number of rows and columns away from a starting cell reference.
Referencing noncontiguous ranges
By using multiple OFFSET
formulas with different starting cell references, you can create a noncontiguous range reference that can be used in other formulas and functions.
Common Mistakes
OFFSET
not working? Here are some common mistakes people make when using the OFFSET
Google Sheets Formula:
Incorrect cell reference
One common mistake is to provide an incorrect cell reference as the first argument. Double check that the cell reference is correct and points to the desired cell.
Missing arguments
Another common mistake is to forget to provide all the required arguments. Make sure to include the cell reference, offset rows and columns, and optionally the height and width.
Using hardcoded values instead of references
It's important to use cell references instead of hardcoded values when using the OFFSET formula. Hardcoding values can lead to errors when the data changes.
Using negative values for height or width
The height and width arguments should be positive numbers. Using negative values will result in an error.
Overlapping ranges
Be careful not to create overlapping ranges when using the OFFSET formula. This can lead to unexpected results.
Related Formulas
The following functions are similar to OFFSET
or are often used with it in a formula:

INDEX
The
INDEX
formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells. 
MATCH
The
MATCH
formula is used to search for a specified value within a range and returns the relative position of the value within the range. This formula is commonly used in conjunction with theINDEX
formula to retrieve a value from a specific cell in a range. Thesearch_type
parameter is optional and determines the type of match to be performed, such as exact match, less than, or greater than. 
VLOOKUP
The
VLOOKUP
function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table. 
HLOOKUP
The
HLOOKUP
function is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.
Learn More
You can learn more about the OFFSET
Google Sheets function on Google Support.