+ 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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,889

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,889

    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)

Similar Threads

  1. [SOLVED] Need formula to match/index 1 of 2 sheets based on another cell value
    By Yokus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2017, 09:36 AM
  2. Creating INDEX MATCH MATCH formula based off text in cells
    By bbkdude in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-26-2017, 10:37 AM
  3. Replies: 4
    Last Post: 04-14-2017, 07:47 PM
  4. Conditional fomatting based on AND(), INDEX() and MATCH() function?
    By KForsman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2015, 06:04 PM
  5. Help using index match function to lookup value based on requirements
    By satcess in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-22-2015, 09:13 PM
  6. index function error using match function to get data from a cell in an array
    By mabildgaard in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-17-2013, 02:21 AM
  7. Replies: 2
    Last Post: 03-20-2009, 01:29 PM

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