Dynamic Range - INDEX + MATCH

[OvernightCellebrity]

I would like to dynamically change the red higlighted reference, based on the value in cell A1 of my sheet which is currently 45. The goal is to avoid having to amend the reference evrytime new data ro rows are added.

=IFERROR(INDEX('Allocations - Pipeline'!$D$60:$Z$1000,MATCH($C33,'Allocations - Pipeline'!$D$60:$D$1000,0),MATCH(D$2,'Allocations - Pipeline'!$D$67:$AV$67,0)),"No Allocations")

 

[kvsrinivasamurthy]

It is not clear that 45 is not appearing in formula or how 45 is related to formula.

 

[Fluff]

How about

=IFERROR(INDEX('Allocations - Pipeline'!$D$60:$Z$1000,MATCH($C33,'Allocations - Pipeline'!$D$60:$D$1000,0),MATCH(D$2,INDEX('Allocations - Pipeline'!$D:$AV,A1,0),0)),"No Allocations")

 

[OvernightCellebrity]

thanks for the responses, this seems to have worked

=IFERROR(INDEX('Allocations - Pipeline'!$D$60:$Z$1000,MATCH($C34,'Allocations - Pipeline'!$D$60:$D$1000,0),MATCH(D$2,INDIRECT("'Allocations - Pipeline'!$D$"&$A$1&":$AV$"&$A$1),0)),"")

 

[Fluff]

Did you try what I suggested? It will be far more efficient than using indirect.