Search code examples
exceldynamicoffset

Excel - Offset function with dynamic reference argument

I'm having trouble making an offset function which allows the reference argument to be dynamic.

Effectively I want the offset function to always reference the last figure in a certain column, where the column of which is determined by a look up function from a name in another table. So if I input another new number below the last, the offset function's reference will automatically move to reference the new (last) data point.

I think the issue may be that I'm using a HLOOKUP to try and look up the new reference value.

i.e. =OFFSET((HLOOKUP(CELLX,CELLY,2, FALSE),0,0))

Is there any way to make the reference argument dynamic?

Solution

  • Could you use INDIRECT? This will cause the OFFSET to reference a cell whose address is returned as a string by the HLOOKUP.

    =OFFSET(INDIRECT(HLOOKUP(CELLX,CELLY,2,FALSE)),0,0)