INdex with part of the range

[RCBricker]

I am trying to build an Index formula. I have the first part of the range, can I use address to get the rest of the range?


so I have A1 to start

so I am thinking index(A1: and then the address function to fill in the second part of the range.

index(A1:&IF(ISERROR(ADDRESS(ROW(INDEX(G:G,MATCH("XXX",G:G,0))),COLUMN(INDEX(G:G,MATCH("XXX",G:G,0))))),ADDRESS(ROW(INDEX(G:G,MATCH("YYY",G:G,0))),COLUMN(INDEX(G:G,MATCH("YYY",G:G,0)))),ADDRESS(ROW(INDEX(G:G,MATCH("XXX",G:G,0))),COLUMN(INDEX(G:G,MATCH("XXX",G:G,0)))))

This produces an error

any thoughts?

 

[njimack]

Take a look at INDIRECT

 

[RCBricker]

yeah I just landed on that and it works. Now the next part.

 

[RCBricker]

Here is my total solution case anyone was following the three threads

=IF(H7="Start",INDEX(INDIRECT("$G$"&ROW()&":$G$"&IF(ISERROR(ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("XXX",INDIRECT("$G$"&ROW()&":$G$1048576"),0)))),ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("YYY",INDIRECT("$G$"&ROW()&":$G$1048576"),0))),ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("XXX",INDIRECT("$G$"&ROW()&":$G$1048576"),0))))),4,1),"")