SOLVED

Trouble with array formula using IFERROR and Index

Copper Contributor

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 to make it work for the various offices that our members are allocated to and their office number and their shift. The way it functions is that it detects a string of words allocated to a separate sheet and it places the name of the individual in one of two spots per office. Those who work during the day and the ones who work the deadman's shift. 

 

for example for members who are working in office space 310 during the day it reads:

=IFERROR(INDEX(ROSTER!$CX$1:$CX$200,SMALL(IF(ROSTER!$DA$10:$DA$200="310DX",ROW(ROSTER!$DA$10:$DA$200)),ROW()-ROW($B$12)+1)),"")

 

if I wanted to look for people in office 220 it would read like this:

 

=IFERROR(INDEX(ROSTER!$CX$1:$CX$200,SMALL(IF(ROSTER!$DA$10:$DA$200="220DX",ROW(ROSTER!$DA$10:$DA$200)),ROW()-ROW($B$25)+1)),"")

 

Both of these work perfectly and don't have any issues, if I changed their shifts from daytime to midnight it would work allocate them to the correct spot with the following formula for office 220 during the night:

 

=IFERROR(INDEX(ROSTER!$CX$1:$CX$200,SMALL(IF(ROSTER!$DA$10:$DA$200="220NX",ROW(ROSTER!$DA$10:$DA$200)),ROW()-ROW($B$25)+1)),"")

 

However I'm running into an issue with people on paid leave

 

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

 

I have an area that specifically allocated to personnel who are on paid specifically for the paid leave it's (N67) and unpaid leave. both of these are causing problems. I can't find any issues with formula, it's no different than the others besides the Paid-Leave, or Unpaid-Leave portions. But, whenever I hit ctrl+shift+enter, it comes back blank.

 

I have members who are currently in this status and I just can't get them to populate the spreadsheet. What am I doing wrong?

 

 

6 Replies
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

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.
  •  

@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"

@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:  

 

IMAGE OF EXCEL.png

best response confirmed by DashTheBomber (Copper Contributor)
Solution

@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) 

You got it. I was able to solve it by using the following:
=IFERROR(INDEX(ROSTER!$CX$1:$CX$200,SMALL(IF(ROSTER!$DA$9:$DA$200="LEAVE",ROW(ROSTER!$DA$9:$DA$200)),ROW()-ROW($I$64)+1))," ")
1 best response

Accepted Solutions
best response confirmed by DashTheBomber (Copper Contributor)
Solution

@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) 

View solution in original post