Reply With QuoteTry:
=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)<=5,IF(ROW(Sheet2!$C$2:$C$14)>=9,IF(Sheet2!$C$2:$C$14="X",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"")
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
Try:
=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)<=5,IF(ROW(Sheet2!$C$2:$C$14)>=9,IF(Sheet2!$C$2:$C$14="X",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"")
. Tried entering formula but it says I have a circular reference? Can't seem to get it to work. Also would I still enter as an array formula?Originally Posted by NBVC
When I take out the greater than and less than section of the formula it works fine again.
I only get a circular reference if I enter the formula in C2 of the active sheet. Yes, you need to array enter it. The part your removed is only checking the row numbers. Did you change the formula in any way?
Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2.
Job Titles Dept. 1 2 3 4 Claims CSI Outb Calls CSI Notifications CSI Evaluator CSI In cell C3 I have formula:=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(ROW(Sheet2!$C$2:$C$14)<=5,IF(ROW(Sheet2!$C$2:$C$14)>=9,IF(Sheet2!$C$2:$C$14="x",ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1)))),C$2),1),"") entered as an array. I should see the name "C. Pepper" followed by "L. Smith" when I copy the formula to the right into D3, "J. Rast" would be skipped as though are the rows not being picked up, but nothing is showing. Sheet 2 looks like this with "Staff Name" starting in cell A1:
Staff Name Mgr Claims Outb Calls Notifications Evaluator A. Hawkins Adams x x C. Pepper Adams X x x x C. Sanders Adams x x D. Sanders Adams x x J. Arcomone Lewis x x J. Rast Lewis X x x J. Montalvo Lewis x J.Ramsey Jones x x K. Selman Jones x x L. Smith Jones X x x L.Young Jones x x M. McBride Jones x x R. Wilson Jones x x
Ok, try this formula in C3:
=IFERROR(INDEX(Sheet2!$A$2:$A$14,SMALL(IF(INDEX(Sheet2!$C$2:$F$14,0,MATCH($A2,Sheet2!$C$1:$F$1,0))="x",IF((ROW(Sheet2!$C$2:$C$14)<=5)+(ROW(Sheet2!$C$2:$C$14)>=9),ROW(Sheet2!$A$2:$A$14)-ROW(Sheet2!$A$1))),C$1),1),"")
confirmed with CTRL+SHIFT+ENTER not just ENTER and then copy down and across the matrix.
Note: This part: INDEX(Sheet2!$C$2:$F$14,0,MATCH($A2,Sheet2!$C$1:$F$1,0)) captures the corresponding column to look for X in by matching A2 the the column headers in the other sheet.
Note2: I mistakingly employed the looking for ROW <=5 and ROW >=9, which would not work, we need to check for ROW <=5 or ROW >=9, and with ARRAY formulas you do that by adding array results; (ROW(Sheet2!$C$2:$C$14)<=5)+(ROW(Sheet2!$C$2:$C$14)>=9)
Hello NBVC, still not working. Not picking up C.Pepper in cell C3 on sheet 1.
Did you confirm with CTRL+SHIFT+ENTER?
See attachment.
IT WORKED!!!! Not sure what I was doing wrong but once I saw your sheets, eureka! Can't thank you enough, this is making my life way easier! Thanks Gene
Posting Permissions
Bookmarks