Referencing the value of a cell to form a range within a function

[teefy]

I am using the Match function to locate the position of some headers in a worksheet:

=MATCH(A$9,'Pivot HY'!$[COLOR=#ff0000]4[/COLOR]:$[COLOR=#ff0000]4[/COLOR],0)

The row number (in red above) is also stored as a value in a cell in another worksheet. Rather than hard-coding "$4:$4" into the formula, is it possible to merely reference the cell containing the value?

The address of the cell containing the value is "Factors!$B$6". The value of this cell is simply "4".

I.e. I'm after something like this (but that actually works!):

=MATCH(A$9,'Pivot HY'!$[COLOR=#ff0000]Factors!$B$6[/COLOR]:$[COLOR=#ff0000]Factors!$B$6[/COLOR],0)

Thank you in advance for your help!

 

[Jonmo1]

Try

=MATCH(A$9,INDEX($1:$1000,Factors!$B$6,0),0)


Make sure the 1:1000 is large enough to encompass all possible values of B6

 

[teefy]

Thanks for your reply. It didn't solve my issue, but I don't think I articulated the problem very well (or I misinterpreted the feedback). It's OK though, I'll live with the Row 4 coded as-is in the Match formula.

 

[Jonmo1]

OK, if that's what you want.
But when you say "I'll live with it", to me that really means "I don't want to live with it"..

Perhaps invest a little time to better articulate the problem.
It will be worth your while, because if your example is anywhere close to your actual issue, then it is definately doable.