The OFFSET function

The OFFSET function is a lookup and reference function which modifies the selected range by either displacing it or increasing its length or width. The variables for displacing the selected range are mandatory, while those to extend or distend the range are option.

This is the format of the OFFSET function:

=OFFSET(INITIAL RANGE, ROW SHIFT, COLUMN SHIFT, TOTAL HEIGHT, TOTAL WIDTH)

Here is an example of the OFFSET function:

=OFFSET(A1, 1, 2, 2, 0) = C2:C3

-> A1 + 1 row + 2 columns -> C2

-> C2 + 1 row (for a total of 2 rows) -> C2:C3

This function is valuable when working with a variable range of cells within a fixed space. For example, a report which has multiple segments on a single sheet. OFFSET will allow you to return the range of cells required for other functions such as SUM or COUNT in the report.

An example of a functional implementation of this would be wanting to capture the range of cells depending on a set of dates and the type of information to be shown. In a standard two axis table OFFSET benefits from being able to use functions like MATCH to determine where the final range will rest.

ex: With a set of cells, end users can select the start and end date of the range they want, as well as which statistic they want to return. If the start date is Jan.07, and the end date is Jan.14 (inclusive), and they want Sales units to be returned it could be configured with OFFSET.

=OFFSET(A1, MATCH(“Jan.07”, A2:A100, 0), MATCH(“Sales units”, B1:G1, 0), MATCH(“Jan.14”, A2:A100, 0) - MATCH(“Jan.07”, A2:A100, 0))

* Note that this example uses multiple MATCH functions to provide a dynamic result array within a dataset, to learn more about MATCH please visit its function page *