Cell Reference instead of number, in a range

[tprez23]

I'm trying to replace a number in a cell reference with another cell reference. I know there are a series of & and "" to use but what makes this tricky is that I want it as part of a range in a formula. Example: I want to replace the 5 in both instances of the formula below with a cell reference (let's say B4) which has the number 5 in it.

=match($B$1,'Sheet1'!$A$5:$AI$5,0)

 

[Norie]

You can use INDIRECT for this

=MATCH($B$1,INDIRECT("'Sheet1'!$A$"&B4&":$AI$"&B4),0)

 

[Fluff]

Hi & welcome to MrExcel.
Another option that does not use volatile functions

=MATCH($B$1,INDEX(Sheet1!$A:$AI,B4,),0)

 

[tprez23]

Hi & welcome to MrExcel.
Another option that does not use volatile functions

=MATCH($B$1,INDEX(Sheet1!$A:$AI,B4,),0)

Thank you!!

 

[tprez23]

You can use INDIRECT for this

=MATCH($B$1,INDIRECT("'Sheet1'!$A$"&B4&":$AI$"&B4),0)

Thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.