Course contents · Lookup functions

OFFSET

Open the practice in the trainer →

OFFSET returns a reference to a range that is a given number of rows and columns away from a starting range:

=OFFSET(reference, rows, cols, [height], [width])

Example from the manual: sales per department

Departments are column headings, so SUMIFS is awkward. Take the first data column and shift it right by the department's position found with MATCH:

=SUM(OFFSET($B$2:$B$7,0,MATCH(G2,$B$1:$E$1,0)-1))

With a row condition too, via SUMPRODUCT:

=SUMPRODUCT(OFFSET($B$2:$B$7,0,MATCH(G2,$B$1:$E$1,0)-1)*($A$2:$A$7=I$1))

Tasks in the trainer

  1. In H2:H4 calculate the sales of the department in column G for all years using SUM + OFFSET + MATCH.
  2. In I2:I4 — the department sales for the year in I1 only (SUMPRODUCT + OFFSET).
  3. In H6 — the sum of the last two values of “Clothing”: offset from B2 by 4 rows down, height 2.
  4. In J2:J4 calculate the department’s average sales (AVERAGE + OFFSET + MATCH).
  5. In H8 return the last value of the “Home” column: offset from E1 down by the count of numbers in E2:E7.

Solve the tasks in the trainer →

Check yourself

  1. Which range does =OFFSET(A1,2,1,3,2) refer to?
  2. What does OFFSET return?