Having an issue with an array formula. {IFERROR(INDEX('sheet2'!$A$2:$A$14,SMALL(IF('sheet2'!$C$2:$C$14="X",ROW('sheet2'!$A$2:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}. Originally I did not need to skip any rows, but now I have to skip certain rows. I've tried using my current array formula and skipping the rows not needed but can't get it to work. {IFERROR(INDEX('sheet2'!$A$2:$A$5,$A$9:$A$14:SMALL(IF('sheet2'!$C$2:$C$5,$C$9:$C$14="X",ROW('sheet2'!$A$2:$A$5,$A$9:$A$14)-ROW('sheet2'!$A$1)),C$2),1),"")}. Can this still be done with an array formula? I need to skip rows 6, 7, 8.
SHEET 1 | ||||||
1 | Job Titles | Dept. | 1 | 2 | 3 | 4 |
2 | Claims | CSI | L. Smith | |||
3 | Outb Calls | CSI | C. Pepper | |||
4 | Notifications | CSI | A. Hawkins | J. Ramsey | ||
5 | Evaluator | CSI | ||||
SHEET 2 | ||||||
1 | Staff Name | Mgr | Claims | Outb Calls | Notifications | Evaluator |
2 | A. Hawkins | Adams | x | x | ||
3 | C. Pepper | Adams | x | |||
4 | C. Sanders | Adams | ||||
5 | D. Sanders | Adams | ||||
6 | J. Arcomone | Lewis | ||||
7 | J. Rast | Lewis | x | |||
8 | J. Montalvo | Lewis | x | |||
9 | J.Ramsey | Jones | x | |||
10 | K. Selman | Jones | ||||
11 | L. Smith | Jones | x | |||
12 | L.Young | Jones | ||||
13 | M. McBride | Jones | ||||
14 | R. Wilson | Jones |