0

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?

Improve this question

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

not sure if Oliver's looking for a single value or a range. For the example you show with a single value LOOKUP can work more easily, e.g. =LOOKUP(99^99,A:A,C:C)
@barryhoudini Glad to see you back!
Thanks guys. I think the underlying issue was an error when referencing a text string in the HLOOKUP. Which was solved by an INDIRECT function. Apologies, I don't think I explained the issue 100% clear when asking the question. All solved now. All the best!
1

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)
Improve this answer

1 Comment

Thanks @Chris Mack. It was an issue with referencing a text sting (which contained the cell reference) in the HLOOKUP. The INDIRECT func sorted it. i.e. '=OFFSET(INDIRECT(HLOOKUP(O14,$P$4:$R$7,4,FALSE),TRUE),0,0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.