Forum Discussion
Solved
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...
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)
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"
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:
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))," ")
