CODEWITHSUNDEEP
exceldynamicoffset
Oliver Matthews
Oliver Matthews

Reputation: 3

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?

Upvotes: 0

Views: 5631

Answers (2)

teylyn
teylyn

Reputation: 35915

You can get the last number in a column with a Match function. If you want to offset DOWN from that last number, add a value to the row offset parameter. If you want to offset to another column, use the column offset parameter.

=OFFSET(A1,MATCH(99^99,A:A,1)-1,2)

In the screenshot the Offset returns a cell two columns to the right of the last number in column A.

enter image description here

Upvotes: 2

Chris Mack
Chris Mack

Reputation: 5208

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)

Upvotes: 1

Related Questions