Help with Index Match function and formula based on another cell value

Hi all,

A great forum that i have used plenty of times but have a weird question.

I have a workbook that keeps tally of a league and weekly results. I have a weighting that i give each team depending on their league position each week. I tried having one sheet with multiple instances of the league table but it didn't work so i now am in the process of creating worksheets named "wk1", "wk2", "wk3" etc

I have a sheet that has all results on and i am trying to reference easily to the sheet name. There is a column with the week no in (column U). In column A my formulas are as follows:

INDEX('wk1'!$B$2:$B$21,MATCH($Y3,'wk1'!$E$2:$E$21,0))

The ranges in columns B and E do not change on each weekly worksheet, so how do i change my formula so that i can drag it down a column so that the 'wk1' part relates to the value in the column U cell?

For instance

Cell A3: INDEX('wk1'!$B$2:$B$21,MATCH($Y3,'wk1'!$E$2:$E$21,0))

Cell U3: 1


Cell A15: INDEX('wk2'!$B$2:$B$21,MATCH($Y15,'wk2'!$E$2:$E$21,0)

Cell U15: 2

So i want to change the part INDEX('wk2'! to be U15 somehow?

Thanks for any help, this would really save me some time as i need to do similar with other Index Match formulas.

Try

=INDEX(INDIRECT("'" &"Wk" & A2 &"'!$B$2:$B$21"),MATCH($Y3,INDIRECT("'" &"Wk" & A2 &"'!$E$2:$E$21"),0))

A2=1

A3=2

etc

change A2 to reflect your values i.e U1, U15 etc

=INDEX(INDIRECT("'" &"Wk" & U3 &"'!$B$2:$B$21"),MATCH($Y3,INDIRECT("'" &"Wk" & U3 &"'!$E$2:$E$21"),0)

Thanks it worked perfectly with the following formula:

INDEX(INDIRECT("'" &"Wk" & U15 &"'!$B$2:$B$21"),MATCH($Y15,INDIRECT("'" &"Wk" & U15 &"'!$E$2:$E$21"),0))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.