Forum Discussion

DashTheBomber's avatar
DashTheBomber
Copper Contributor
Jul 21, 2022

Trouble with array formula using IFERROR and Index

I am using a spreadsheet that utilizes arrays to allocate personnel to different spots based on specific criteria. However, I'm running into an issue with people in vacation   I have so far managed...
Excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jul 21, 2022

    DashTheBomber  so I saw a message pop up from another member that made a great point but then it disappeared.  I don't mean to steal someone else's work but they noticed that the ranges are different.  Here I replaced the $DA$193 with $DA$192

    =IFERROR( INDEX( ROSTER!$CX$42:$CX$192,
                 SMALL(IF( ISNUMBER(SEARCH("LEAVE",ROSTER!$DA$9:$DA$192)) 
                          ,ROW(ROSTER!$DA$9:$DA$192)),
                       ROW()-ROW($N$67)+1)),
          "")

    so the lookup/index range is from 42 to 192, the criteria range is from 9 to 192 and the row return range is from 9 to 193.  The index and the ranges inside the IF don't have to be the same length but you need to be careful and make sure it won't return an index outside the range.  The ranges INSIDE the IF() probably DO need to be the same length as it may try to increment and one of them won't have a value (i.e. if tries to go to $DA$193, the 185th element, the criteria may give an error) 

mtarler's avatar
mtarler
Silver Contributor
I suspect the cells are not matching any. That could be because the "-" is a different ASCI version of the hyphen. What if you used something like ISNUMBER(SEARCH("LEAVE",ROSTER!$DA$9:$DA$192)) instead?
Better yet is if you have Excel 365 then you should just use the FILTER function instead of this older style function that you have to fill down:
=FILTER(ROSTER!$CX$42:$CX$192, ISNUMBER(SEARCH("LEAVE",ROSTER!$DA$9:$DA$192)), "None Found")
and let all the results "spill" down the column
DashTheBomber's avatar
DashTheBomber
Copper Contributor
Jul 21, 2022

Unfortunately it seems like my organization has not upgraded to the 365 so I'm unable to use the FILTER function. Closest we have is FilterXML

The good news is that the IS number is returning a true, however it's not returning the name of the person.

In the old formula the INDEX portion of it was the location of the name of the person. Whenever it found someone who's location said LEAVE it would then refer to the INDEX with the roster of personnel and allocate the name of that individual in the LEAVE section of the spreadsheet. If it's returning the true, then all I would need is to replace the true with the name of the row next to it.

 

From what I understand in the formula:

  • Index: Used to determine the name of the person that's on leave and place that name in the specific portion of the array.
  • IF is used to meet the specific wording LEAVE
  • The first ROW is used to match the row from the IF that says LEAVE
  • The Row()-Row(N67)+1 is used to start the allocation to that specific area and as more people take leave it goes down to the next line below.
  • The "" is because if it doesn't find anyone on Leave then it can leave that space blank.
  • The IFERROR is just how I was taught to do this, I'm honestly unsure if it's the best option.
  •  
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 21, 2022

    DashTheBomber sorry I meant for that to be substituted into the other equation:

    =IFERROR( INDEX( ROSTER!$CX$42:$CX$192,
                 SMALL(IF( ISNUMBER(SEARCH("LEAVE",ROSTER!$DA$9:$DA$192)) 
                          ,ROW(ROSTER!$DA$9:$DA$193)),
                       ROW()-ROW($N$67)+1)),
          "")

    so instead of the ROSTER!...="PAID-LEAVE"

    • DashTheBomber's avatar
      DashTheBomber
      Copper Contributor
      Jul 21, 2022

      mtarler 

       

      I truly appreciate all your help. But, I don't know why it still comes back blank 😞 I don't know what it's looking for...

      The formula is excellent, but for whatever reason I'm still getting blanks in the allocated space which is cell N67. I typed it out and dragged and dropped the cells to make sure it was perfect, but nothing... But, a picture is worth a thousand words:  

       

      • mtarler's avatar
        mtarler
        Silver Contributor
        Jul 21, 2022

        DashTheBomber  so I saw a message pop up from another member that made a great point but then it disappeared.  I don't mean to steal someone else's work but they noticed that the ranges are different.  Here I replaced the $DA$193 with $DA$192

        =IFERROR( INDEX( ROSTER!$CX$42:$CX$192,
                 SMALL(IF( ISNUMBER(SEARCH("LEAVE",ROSTER!$DA$9:$DA$192)) 
                          ,ROW(ROSTER!$DA$9:$DA$192)),
                       ROW()-ROW($N$67)+1)),
          "")

        so the lookup/index range is from 42 to 192, the criteria range is from 9 to 192 and the row return range is from 9 to 193.  The index and the ranges inside the IF don't have to be the same length but you need to be careful and make sure it won't return an index outside the range.  The ranges INSIDE the IF() probably DO need to be the same length as it may try to increment and one of them won't have a value (i.e. if tries to go to $DA$193, the 185th element, the criteria may give an error) 

Resources