Results 1 to 4 of 4

Thread: Index Formula with multiple criteria

  1. #1
    Neophyte Mitch1976's Avatar
    Join Date
    Jun 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 Subscription

    Index Formula with multiple criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a large database with Items and the corresponding months they are over due for delivery. I would like to Index the list to show items that are overdue between a month range (e/g < 1month, 1-3 months etc).

    I have used an array formula to get < 1 month as there is only 1 criteria but I am struggling to add the second criteria to the Array.

    I have attached an example of what I am trying to achieve.

    All help welcome.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You can use an Array* formula like:

    =IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7>=1,IF($B$2:$B$7<=3,ROW($A$2:$A$7)-ROW($A$2)+1)),ROWS(F$2:F2))),"")

    confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down

    or you can add a helper column to C2:

    =IF(AND(B2>=1,B2<=3),MAX(C$1:C1)+1,0)

    copied down, then in F2:

    =IFERROR(INDEX($A$2:$A$7,MATCH(ROWS(F$2:F2),$C$2:$C$7,0)),"")

    copied down.


  3. #3
    Neophyte Mitch1976's Avatar
    Join Date
    Jun 2018
    Posts
    2
    Articles
    0
    Excel Version
    Office 365 Subscription

    Thank You

    Thanks NBVC,

    Works perfectly. I had something like this from an on-line tutorial but it had additional formula included and I couldn't work out the correct syntax and what to remove.

    Appreciate your quick response as I have spend most of the day trying to sort this out. Now I can go to bed!!

    How do I mark as solved and what other formalities do I have to take care of to close this?

    Mitch

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    no problem... you're good as is.


Posting Permissions

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