Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Multiple Criteria index, match formula

  1. #1
    Board Regular
    Join Date
    Aug 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple Criteria index, match formula

    Hi there,

    got an issue with an index match formula im trying to do.

    I want to say if the value in cell K10 (report tab) is equal to either a15:b15 or a28:b28 (merged cells in monthly tab).

    then i want to return the value between d15:au15 or d28:au28 (both monthly tab) respectively IF the value in cells D2:au2 (monthly tab) is equal to that in d11 (report tab)

    I hope that makes sense.

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Posts
    200
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    Can you explain

    the value between d15:au15
    in greater detail?

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,108
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Multiple Criteria index, match formula

    It's not worded very well.

    Looking at this again I think OP is after retrieving a value from D15:AU28, where there is a matching column between Report!D11 and Monthly!D2:AU2
    and the row to retrieve is either row 15 or row 28 dependent on Report!K10 matching A15:B15 or A28:B28

    Try (this assumes Report!K10 MUST match either A15:B15 or A28:B28)

    =INDEX(Monthly!D15:AU28,IF(COUNTIF(A15:B15,Report!K10)<>0,1,14),MATCH(Report!D11,Monthly!D2:AU2))

  4. #4
    Board Regular
    Join Date
    Aug 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    Quote Originally Posted by Special-K99 View Post
    It's not worded very well.

    Looking at this again I think OP is after retrieving a value from D15:AU28, where there is a matching column between Report!D11 and Monthly!D2:AU2
    and the row to retrieve is either row 15 or row 28 dependent on Report!K10 matching A15:B15 or A28:B28

    Try (this assumes Report!K10 MUST match either A15:B15 or A28:B28)

    =INDEX(Monthly!D15:AU28,IF(COUNTIF(A15:B15,Report!K10)<>0,1,14),MATCH(Report!D11,Monthly!D2:AU2))
    I get the error #ref ... I shall try to make this clear.

    Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
    d15:au15 or d28:au28 (monthly tab)

    the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)

    to select which column from d to au needs to be selected within the two ranges I need to match the value in C11 (report tab) to the range d3:au3 (monthly tab)

    Hope this clears it up I dont know if this can be done with multiple ranges that is my issue.

    - this might work using d15:au28 as you suggested but i tend to get error messages or maybe my previously poor wording messed the formula up
    Last edited by PGD15; Jun 20th, 2018 at 07:37 AM.

  5. #5
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,108
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Multiple Criteria index, match formula

    I guess this is a typo

    Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
    d15:au15 or d28:au28 (monthly tab)

    the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)


    With the existence of a Report sheet and a Monthly sheet this works (syntactically, I havent checked with data)

    =INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))
    Last edited by Special-K99; Jun 20th, 2018 at 07:57 AM.

  6. #6
    New Member
    Join Date
    Jun 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    =IFERROR(INDEX(Monthly!D15,MATCH(K10,Monthly!A15,0)),INDEX(Monthly!D28,MATCH(K10,Monthly!A28,0)))

  7. #7
    Board Regular
    Join Date
    Aug 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    Quote Originally Posted by Special-K99 View Post
    I guess this is a typo

    Based on the following criteria I want to return a value from either of these two ranges (this is my issue)
    d15:au15 or d28:au28 (monthly tab)

    the value of which range to use to will be located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example)


    With the existence of a Report sheet and a Monthly sheet this works (syntactically, I havent checked with data)

    =INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))
    Thanks, almost works just 1 slight issue. what is the l10 cell pointing too? as l10 is going to equal a year such as 2017 then the text YTD Total

    so this cell will either say 2017 ytd total or 2018 ytd total and this will be rolling. so if i change my target year to 2017 the formula doesn't pick up the 2017 data (like it should from the cell ranges i gave you)

    I believe thw 1,1,14 my be directing/matching it slightly wrong. as if it 2017 this should return the values in the row 15 if 2018 values in row 28 (which it currently does)


    ignoore the above this bit below is the edit of my problem

    wait i f'ed up i forgot to say the cell range L10 needs to be matched to either a15 or or a28 in addition to everything else
    Last edited by PGD15; Jun 20th, 2018 at 08:37 AM.

  8. #8
    Board Regular
    Join Date
    Aug 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    Dont worry I figured it out! I got it to work by adapting your formula. Thank you very much!

  9. #9
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,108
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Multiple Criteria index, match formula

    You said this

    "located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example) "

    So thats what I put in the formula.

    =INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

    Now youre saying its neither of them.
    You need to make your mind up.
    Last edited by Special-K99; Jun 20th, 2018 at 08:55 AM.

  10. #10
    Board Regular
    Join Date
    Aug 2016
    Posts
    105
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple Criteria index, match formula

    Quote Originally Posted by Special-K99 View Post
    You said this

    "located in L10 (report tab) so if l10 = 1 choose a15:au15 or 2 d28:au28 (example) "

    So thats what I put in the formula.

    =INDEX(Monthly!D15:AU28,IF(Report!L10=1,1,14),MATCH(Report!C11,Monthly!D3:AU3))

    Now youre saying its neither of them.
    You need to make your mind up.
    I know, but i said (example) as these werent the actual values, then i realised you used them and i noticed so i changed these to the correct values and it works.

    Many thanks for the help

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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