Lookup and Return Names With Non- Consecutive Rows

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
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
1Job TitlesDept.1234
2ClaimsCSIL. Smith
3Outb CallsCSIC. Pepper
4NotificationsCSIA. HawkinsJ. Ramsey
5EvaluatorCSI
SHEET 2
1Staff NameMgrClaimsOutb CallsNotificationsEvaluator
2 A. HawkinsAdams xx
3C. PepperAdams x
4C. SandersAdams
5D. SandersAdams
6J. ArcomoneLewis
7J. RastLewisx
8J. MontalvoLewis x
9J.RamseyJones x
10K. SelmanJones
11L. SmithJonesx
12L.YoungJones
13M. McBrideJones
14R. WilsonJones
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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),"")
 

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
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?
 

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
. 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?
When I take out the greater than and less than section of the formula it works fine again.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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?
 

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
Not sure what I'm doing wrong but no names are appearing. Sheet 1 looks like this with "Job Titles" starting in A2.
Job TitlesDept.1234
ClaimsCSI
Outb CallsCSI
NotificationsCSI
EvaluatorCSI
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 NameMgrClaimsOutb CallsNotificationsEvaluator
A. HawkinsAdams xx
C. PepperAdamsXxxx
C. SandersAdams xx
D. SandersAdams xx
J. ArcomoneLewis xx
J. RastLewisX xx
J. MontalvoLewis x
J.RamseyJones xx
K. SelmanJones xx
L. SmithJonesX xx
L.YoungJones xx
M. McBrideJones xx
R. WilsonJones xx
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
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)
 

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
Hello NBVC, still not working. Not picking up C.Pepper in cell C3 on sheet 1.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Did you confirm with CTRL+SHIFT+ENTER?

See attachment.
 

Attachments

  • Book2.xlsx
    10.2 KB · Views: 15

ctiger

New member
Joined
Nov 4, 2013
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2013
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
 
Top