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

 

[seraja27]

i am using the index-match function to retrieve data from sheet1 which has data in columns by fiscal week. what i want to do is on sheet2 be able to type in FW1 or FW2 or FW3, etc into a cell and have it update cell B1 which retrieves the corresponding column from sheet1 (for instance FW1= column 1 on sheet1) which in turn will feed into the index-match function.

 

[NBVC]

I think then in B1 you need to type an IF() formula,

e.g. =IF(A1="FW",range1,IF(A1=FW2,Range2, etc...)

or you can create a little lookup tabel elsewhere in the sheet with all the FW choices and the corresponding index ranges to use in B1, then in B1 use:

=Vlookup(A1,$X$1:$Z$10,2,0)

where X1:Z10 would contain that table to look up.