Return text from colomn if meets criteria in another colomn

  • Hi
    Would be grateful for any help with this,
    I have a table of names and information relating to how long individuals have left in training in a Worksheet called EYE.
    I need to select in another work sheet called EED only the names who have gone over their allotted time


    e.g.
    1. return name in Column A if days left in Column L shows -90 or more


    2. return name in Column A if days left in Column L shows between -90 and 0


    3. return name in Column A if days left in Column L shows between -0 and 45


    Spreadsheet is like this


    [TABLE="width: 755"]

    [tr]


    [TD="class: xl72, width: 117, bgcolor: transparent"]A[/TD]
    [TD="class: xl72, width: 91, bgcolor: transparent"]B[/TD]
    [TD="class: xl72, width: 91, bgcolor: transparent"]C[/TD]
    [TD="class: xl72, width: 80, bgcolor: transparent"]D[/TD]
    [TD="class: xl74, width: 80, bgcolor: transparent"]E[/TD]
    [TD="class: xl72, width: 65, bgcolor: transparent"]F[/TD]
    [TD="class: xl72, width: 66, bgcolor: transparent"]G[/TD]
    [TD="class: xl72, width: 66, bgcolor: transparent"]H[/TD]
    [TD="class: xl72, width: 102, bgcolor: transparent"]I[/TD]
    [TD="class: xl72, width: 86, bgcolor: transparent"]J[/TD]
    [TD="class: xl72, width: 70, bgcolor: transparent"]K[/TD]
    [TD="class: xl72, width: 89, bgcolor: transparent"]L[/TD]

    [/tr]


    [tr]


    [TD="class: xl72, width: 117, bgcolor: transparent"]Learner Name[/TD]
    [TD="class: xl72, width: 91, bgcolor: transparent"]Location[/TD]
    [TD="class: xl72, width: 91, bgcolor: transparent"]Start Date[/TD]
    [TD="class: xl72, width: 80, bgcolor: transparent"]EED[/TD]
    [TD="class: xl74, width: 80, bgcolor: transparent"]Previous Month Progress[/TD]
    [TD="class: xl72, width: 65, bgcolor: transparent"]Progress To Date[/TD]
    [TD="class: xl72, width: 66, bgcolor: transparent"]Target Progress[/TD]
    [TD="class: xl72, width: 66, bgcolor: transparent"]Variance[/TD]
    [TD="class: xl72, width: 102, bgcolor: transparent"]Assessor Name[/TD]
    [TD="class: xl72, width: 86, bgcolor: transparent"]Last review [/TD]
    [TD="class: xl72, width: 70, bgcolor: transparent"]Days since last seen[/TD]
    [TD="class: xl72, width: 89, bgcolor: transparent"]Days Left / Overdue [/TD]

    [/tr]


    [tr]


    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]John Smith[/TD]
    [TD="class: xl66, bgcolor: transparent"]A[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]08/01/2014[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]01/03/2017[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]94%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]94%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]100%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]-6%[/TD]
    [TD="class: xl66, bgcolor: transparent"]John Macenroe[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]18/11/2016[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]75[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]28[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]Robert Wagner[/TD]
    [TD="class: xl66, bgcolor: transparent"]B[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]13/10/2013[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]01/01/2017[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]92%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]92%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]100%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]-8%[/TD]
    [TD="class: xl66, bgcolor: transparent"]John Macenroe[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]28/11/2016[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]65[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]-31[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]Phil Jones[/TD]
    [TD="class: xl66, bgcolor: transparent"]A[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]24/09/2013[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]01/10/2016[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]95%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]100%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]100%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]0%[/TD]
    [TD="class: xl66, bgcolor: transparent"]John Macenroe[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]29/11/2016[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]64[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]-123[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]Dave Ryder[/TD]
    [TD="class: xl66, bgcolor: transparent"]D[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]25/08/2015[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]01/11/2016[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]65%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]65%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]74%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]-9%[/TD]
    [TD="class: xl66, bgcolor: transparent"]Bjorn Borg[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]11/01/2017[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]21[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]-92[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]william tell [/TD]
    [TD="class: xl66, bgcolor: transparent"]B[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]30/09/2015[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]30/01/2017[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]38%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]40%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]70%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]-30%[/TD]
    [TD="class: xl66, bgcolor: transparent"]Bjorn Borg[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]11/01/2017[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]21[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]-2[/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent"]John Stevens[/TD]
    [TD="class: xl66, bgcolor: transparent"]D[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]30/09/2015[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]30/09/2016[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]68%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]66%[/TD]
    [TD="class: xl71, bgcolor: transparent, align: right"]64%[/TD]
    [TD="class: xl68, bgcolor: transparent, align: right"]2%[/TD]
    [TD="class: xl66, bgcolor: transparent"]Bjorn Borg[/TD]
    [TD="class: xl70, bgcolor: transparent, align: right"]09/01/2017[/TD]
    [TD="class: xl67, bgcolor: transparent, align: right"]23[/TD]
    [TD="class: xl69, bgcolor: transparent, align: right"]-124[/TD]

    [/tr]


    [/TABLE]



    Thank you



    [TABLE="width: 1003"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [td][/td]


    [TD="align: right"][/TD]
    [TD="align: right"][/TD]
    [TD="align: right"][/TD]

    [/tr]


    [/TABLE]

  • Re: Return text from colomn if meets criteria in another colomn


    You'll probably need an array* formula like:


    =IFERROR(INDEX(EYE!$A$2:$A$100,SMALL(IF(EYE!$L2:$L$100<-90,ROW(EYE!$A$2:$A$100)-ROW(EYE!$A$2)+1),ROWS($A$2:$A2))),"")


    copied down after you make appropriate adjustments.


    [ARF]x[/ARF]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!