How to use formula result in one cell in formula range in a different cell?

[EdNerd]

In U1, I have
=MATCH(U2,$A$1:$A100,0)
which returns a correct value of 2.

In U11, I have
=MATCH(TRUE,INDEX($B3:$R3<>"",0),0)

I'd like to change the $B3:$R3 to use the value in U1.
Something like:
"$B"&U1+1&":$R"&U1+1

But so far all I'm getting is #VALUE errors!
Anyone drop-kick me in the right direction, please??

 

[pgc01]

Hi Ed

Indirect() translates a string into a reference.

Try:

=MATCH(TRUE,INDEX(INDIRECT("$B"&(U1+1)&":$R"&(U1+1))<>"",0),0)

 

[pgc01]

P. S.

I used Indirect() just to go with your logic, that is correct.

Indirect() is however volatile, which means is not efficient and will hurt your workbook performance if you use many.

In this case, I would use instead:

=MATCH(TRUE,INDEX(INDEX(B:B,U1+1):INDEX(R:R,U1+1)<>"",0),0)

 

[EdNerd]

Works like a champ!!
Thanks!!!

Ed

 

[pgc01]

You're welcome. Thanks for the feedback.