Im trying to change the fixed end point of an array to make it dynamic by replacing $D$613 with INDEX($P$2:$P$37,ROWS($A$2:C2)).
both INDEX functions return the correct value when input on there own but I get a "REF" error when I replace $D$613 with INDEX($P$2:$P$37,ROWS($A$2:C2))
before
=VLOOKUP("L3:",INDIRECT("ukdox2!"&INDEX($P$1:$P$37,ROWS($A$2:C2))&":$D$613"),2,0)
after
=VLOOKUP("L3:",INDIRECT("ukdox2!"&INDEX($P$1:$P$37,ROWS($A$2:C2)):&INDEX($P$2:$P$37,ROWS($A$2:C2))),2,0)
both INDEX functions return the correct value when input on there own but I get a "REF" error when I replace $D$613 with INDEX($P$2:$P$37,ROWS($A$2:C2))
before
=VLOOKUP("L3:",INDIRECT("ukdox2!"&INDEX($P$1:$P$37,ROWS($A$2:C2))&":$D$613"),2,0)
after
=VLOOKUP("L3:",INDIRECT("ukdox2!"&INDEX($P$1:$P$37,ROWS($A$2:C2)):&INDEX($P$2:$P$37,ROWS($A$2:C2))),2,0)