Forum Discussion

Copper Contributor

Apr 12, 2020

Please explain the logic behind this issue? Cell range / array question.

Hi there, I've just learned that you can use a function within an range instead of a cell reference. I however do not understand the logic behind it and need some assistance in understanding it. ...

Diamond Contributor

Apr 16, 2020

nevinkamath

Few functions return a reference, not only OFFSET. For example, to return the range A5:A10 you may use formulas like

=OFFSET(A1,4,0):OFFSET(A1,9,0)

=IF(1,A5,0):IF(1,A10,0)

or other functions which could return references: OFFSET, INDEX, XLOOKUP, IF, SINGLE, CHOOSE, SWITCH. I lost the source there they are listed, you may google for them.

If only one range reference is returned by formula, it looks like

=A5:OFFSET(A1,9,0)

You may use such dynamic ranges in other formulas same way as fixed one, e.g.

=SUM(A5:OFFSET(A1,9,0))

is equivalent of

=SUM(A5:A10)

but

=SUM(A5:10)

is just wrong syntax.