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

#### teefy

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

Code:
``=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!):

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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

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.

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.

Replies
6
Views
239
Replies
2
Views
217
Replies
1
Views
182
Replies
3
Views
233
Replies
1
Views
201

1,196,057
Messages
6,013,160
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back