# offset function

• How do I write a formula that will use the offset function in a changeable reference?
For example, I want cell D100 to equal a given cell (a date I change once a month), then I want cell D105 to equal the cell 3 columns to the left and 100 rows down from whatever cell D100 changes to.

• sorry Bob, this makes no sense at all to me

with just 3 or 4 cells in your question, it might be easier to post up an example sheet

• your attachement's knackered!

that said:

the formula is:

=INDEX(A2:D21,MATCH(F1,D2:D21,0)+10,1)

change the references to suit, and the +10 to +100 to get the appropriate offset.

...is that what you meant?

• via U2U:

"No this is not exactly what I meant. In your example I would like to get the result 2
for entering the date 2/01/2001. "

...but you said you wanted a three column, 100 row offset? Anyway, if all you want to do is a "left lookup", simply remove the +10 from the formula I posted:

=INDEX(A2:D21,MATCH(F1,D2:D21,0),1)

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!