Excel Offset Function Returns #VALUE! Error When Combined with Row Function

Question

Why does following Excel function return an error? The error occurs only when the reference that is returned is larger than 1 cell. In the formula, the returned reference is 1 row high and 2 rows high.

    =OFFSET($A$2,ROW(G6),0,1,2) 

Answer 1

There are two different ways that your formula might return #VALUE! error here, depending on how you are trying to employ it:

Firstly if you have a formula which returns an array of values then it makes a difference where you put the formula (if not "array entered").

Put that formula in A10 (not array entered) and you see the value from A8, put the formula in B10 and you see the value from B8, put the formula somewhere other than columns A and B and you get #VALUE! That's standard behaviour in Excel for these type of functions.

Secondly, if you try to "array enter" the formula in a 2 cell horizontal range to see both values (as chuff suggests), then you also get #VALUE! error which is caused by ROW function.

That happens because ROW() returns an "array" (even when it's a single row like here) which some functions don't process properly - you can see that if you highlight ROW(G6) and press F9 key - you see {6} rather than 6. The solution is to either use just 6 in the formula or, presuming you want the flexibility provided by ROW, you can wrap in another function, like SUM, e.g.

=OFFSET($A$2,SUM(ROW(G6)),0,1,2)

several other functions will do the same job as SUM here, e.g. MAX, MIN.....or you can replace ROW with ROWS, e.g.

=OFFSET($A$2,ROWS(G$1:G6),0,1,2)