+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    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. #3
    Registered User
    Join Date
    01-03-2018
    Location
    Devon, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    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. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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