Passing Cell Address to Offset

[Bob]

The following function returns the cell address I want to use in an offset
function:

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

however, if I imbed this function in the first argument of the offset
function i.e.

OFFSET(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4),-9,0,1,1)

I get "The formula you typed contains an error." Any suggestions?

 

[Peo Sjoblom]

Try

=OFFSET(INDIRECT(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)),-9,0,1,1)

having said that there is probably better to skip INDIRECT and ADDRESS and
use the match and the other data directly in either OFFSET or INDEX, this is
like swimming to the other side of the river to get a drink

For instance this

=OFFSET(A1,MATCH(C$8,C$10:C$28,0)+8-1,D1-1)

would return what's in

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

meaning

=OFFSET(A1,MATCH(C$8,C$10:C$28,0)-2,D1-1)

will return

=OFFSET(INDIRECT(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)),-9,0,1,1)




Regards,

Peo Sjoblom

 

[Don Guillett]

Use match to find the row and then use offset from a given to use the match.
look at help for offset to see what I mean
=offset(a1,matchrow,column)