Index-Match Formula - Array Part

[seraja27]

Hi, I want to know how to make the array/reference part in an index-match formula look to another cell that in turn looks to a column.

My formula is =INDEX('Sheet1!$AU$27:$AU$284,MATCH(A30&B30,'Sheet1'!$G$27:$G$284&'Sheet1'!$H$27:$H$284,0))

For the array reference - 'Sheet1!$AU$27:$AU$284 i want it to look to....for example cell B1 (which will reference the column of data)...then cell C1 to say Fiscal Week 1...Basically I want to be able to update the FW number in cell C1 which in turn updates cell B1 to the appropriate FW column which in turn updates the index-match formula


Thank you in advance for any help

 

[NBVC]

So you mean that Sheet1!$AU$27:$AU$284 is in B2 and you want to reference that?

If so, replace Sheet1!$AU$27:$AU$284 in the formula with INDIRECT(B2)

 

[seraja27]

How can i make a cell reference a column?

so yes i want cell B1 to contain the (Sheet1!$AU$27:$AU$284)

and then this will change my formula to the following:

=INDEX(B1,MATCH(A30&B30,'Sheet1'!$G$27:$G$284&'Sheet1'!$H$27:$H$284,0))

 

[NBVC]

So as I mentioned replace with INDIRECT(B1)

=INDEX(INDIRECT(B1),MATCH(A30&B30,'Sheet1'!$G$27:$G$284&'Sheet1'!$H$27:$H$284,0))

 

[seraja27]

ok when i put (=Sheet1!$AU$27:$AU$284) in cell B1 i receive (#value!) what do i have to put in cell B1?

 

[NBVC]

Don't put the = sign in B1

 

[seraja27]

when i enter the formula =INDEX(INDIRECT(B1),MATCH(A30&B30,'Sheet1'!$G$27:$G$284&'Sheet1'!$H$27:$H$284,0))...i receive (#REF!)

in b1 i have 'Sheet1'!$AU$27:$AU$284

 

[NBVC]

if you enter: 'Sheet1'!$AU$27:$AU$284 you will have to precede it with another apostrophe: ''Sheet1'!$AU$27:$AU$284 otherwise, don't use any apostrophes, since the sheetname is one word.... Sheet1!$AU$27:$AU$284

 

[seraja27]

perfect it worked. thank you!

Also now that I have cell B1 worked out, what do you think is the best way say if in cell A1 I input FW1 to automatically update the corresponding column in sheet 1 into cell B1 which in turn will feed into the index-match formula.

For instance FW1 = Column A in Sheet 1....FW2 = Column B in Sheet 1

 

[NBVC]

I am not sure I understand this one... you want to replace the INDIREC(B1) with a conditional check in order to determine if you should get the index range from different cells?

Please elaborate.