Search code examples
excelexcel-formularowoffset

OFFSET() + ROW() wont give the desired result

I'm trying to combine the OFFSET function with the ROW function but they don't work as expected.

If I try:

{=OFFSET(A1:F1,ROW(F6)-1;0)}

It gives me the wrong results giving only an array constant with 1 column.

'It returns:
{0}

Else if I use:

{=OFFSET(A1:F1,5,0)}

It works perfectly and returns an array constant with the corresponding column values of the offset row.

'Would return:
{0,0,0,0,0,0}

If ROW returns a number why the hell doesn't this work as it should?

Solution

  • Per my previous comment, ROW returns an array not a scalar value (ie in your formula ROW(F6) returns {6} not 6, which stuffs up your OFFSET (as it requires a scalar for the rows/column offsets).

    Per https://answers.microsoft.com/en-us/msoffice/forum/all/row-function-returning-array-though-it-should-not/fcce040c-c297-43d9-a02e-a0038b6be5e0 if you wrap the ROW(F6) in a SUM or MAX it should fix the problem ie =OFFSET(A1:F1,SUM(ROW(F6))-1;0)