Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Lookup and Return Names With Non- Consecutive Rows

  1. #1

    Lookup and Return Names With Non- Consecutive Rows



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    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),"")


  3. #3
    Quote Originally Posted by NBVC View Post
    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?

  4. #4
    Quote Originally Posted by ctiger View Post
    . 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.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    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?


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

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    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)


  8. #8
    Hello NBVC, still not working. Not picking up C.Pepper in cell C3 on sheet 1.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,464
    Articles
    0
    Excel Version
    Excel 2016
    Did you confirm with CTRL+SHIFT+ENTER?

    See attachment.
    Attached Files Attached Files


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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •