Forum Discussion

Copper Contributor

Jul 21, 2022

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

excel

Formulas and Functions

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)

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

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)

DashTheBomber
Copper Contributor
Sep 20, 2022
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))," ")