CHOOSE

CHOOSE returns a value based on an index number.

Syntax

=CHOOSE(index_num,value1,value2,…)

Example:

=CHOOSE(3,”Sales”,”HR”,”IT”,”Finance”)

Result: IT

Because IT is the third item.

Real-World Example

If B2 contains department number:

=CHOOSE(B2,”Sales”,”HR”,”IT”,”Finance”)

OFFSET

OFFSET returns a reference that is a specified number of rows and columns away from a starting cell.

Syntax

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

Example:

=OFFSET(A2,2,1)

This moves:

  • 2 rows down
  • 1 column right

from A2.

Uses

OFFSET can be used for:

  • Dynamic ranges
  • Dynamic reports
  • Dashboard calculations
  • Flexible data references
PreviousNext