MATCH changing range without volatile INDIRECT()

Hi all,

My formula is as follows:

{=IFERROR(INDEX('Summary'!$2:$2,MATCH(X2,INDIRECT("'Summary'!J"&MATCH(R2,'Summary'!A:A,0)&":V"&MATCH(R2,'Summary'!A:A,0)),1)+9),"")}

It works to do what I need it to, but are you all aware of an alternative to using INDIRECT()? It seems to be slowing things down pretty substantially. In essence, I'm retrieving a row number using MATCH and want to MATCH "X2" within a range in that same row (J:V).

The formula is repeated 45,000 times in the sheet, so avoiding the volatility of INDIRECT() would be amazing.

Try

=IFERROR(INDEX('Summary'!$2:$2,MATCH(X2,INDEX('Summary'!$J:$V,MATCH(R2,'Summary'!A:A,0),0),1)+9),"")

Originally Posted by Jonmo1

Try

=IFERROR(INDEX('Summary'!$2:$2,MATCH(X2,INDEX('Summary'!$J:$V,MATCH(R2,'Summary'!A:A,0),0),1)+9),"")

Absolutely perfect! Thank you!

You're welcome.