Find offset reference

[BP]

Hello!

I'm trying to find the offset reference based on a
variable. Column A contains dates. In cell G10 I enter a
date and find that same date in column A and want to use
that cell address as the offset reference. I've got the
cell address with this much of the formula -

=CELL("ADDRESS",INDEX(A:A,MATCH(G10,A:A)))

This returns A4 which is correct. However, when I wrap
this into an offset function I get the general formula
error.

Any ideas?

Thanks,
Bob

 

[Andy Brown]

"BP",

Throw in INDIRECT. With your formula in G1 (returning $A$4), I used

=OFFSET(INDIRECT(G1),0,1)

to return the value in B4. Tho' this all sounds a bit lateral ; make sure
you don't end up going up-across-down when you could've just gone across.

HTH,
Andy

 

[Aladin Akyurek]

It's...

=OFFSET(INDEX(A:A,MATCH(G10,A:A)),...)

You could also use the INDEX bit directly in a range specifying expression
if that's what you're after.

 

[Andy Brown]

Aladin,

You're obviously more awake (OK, loads smarter) than I. However, might OP
need to NB Excel Help that "If match_type is omitted, it is assumed to be 1"
(?)

Rgds,
Andy