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

1. ## 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.

2. ## Re: Help with Index Match function and formula based on another cell value

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)

3. ## Re: Help with Index Match function and formula based on another cell value

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))

4. ## Re: Help with Index Match function and formula based on another cell value

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1