Find adj. cells with specific text strings. Return a text string based on findings.

  • Ok,


    I am trying to look at a row for a specific text string in a cell. If found, I want to look at the cell to the right for a text string. Based on the text string in the second cell I want to return a specific text string.


    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"] [/TD]
    [TD="width: 87"]A[/TD]
    [TD="width: 87"]B[/TD]
    [TD="width: 87"]C[/TD]
    [TD="width: 87"]D[/TD]
    [TD="width: 87"]E[/TD]
    [TD="width: 87"]F[/TD]
    [TD="width: 87"]G[/TD]
    [TD="width: 87"]H[/TD]
    [TD="width: 83"]I[/TD]
    [TD="width: 98"]J[/TD]
    [TD="width: 79"]K[/TD]

    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Skill

    [/td]


    [td]

    Status

    [/td]


    [td]

    Skill

    [/td]


    [td]

    Status

    [/td]


    [td]

    Skill

    [/td]


    [td]

    Status

    [/td]


    [td]

    Skill

    [/td]


    [td]

    Status

    [/td]


    [td]

    Skill

    [/td]


    [td]

    Status

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Start

    [/td]


    [td]

    Training

    [/td]


    [td]

    start

    [/td]


    [td]

    Stand Alone

    [/td]


    [td]

    Start

    [/td]


    [td]

    Verified

    [/td]


    [td]

    Start

    [/td]


    [td]

    Qualified

    [/td]


    [td]

    Excel

    [/td]


    [td]

    Training

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/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]


    [td]

    4

    [/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]


    [td]

    5

    [/td]


    [td][/td]


    [TD="colspan: 2"]Cell for Return Value[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="width: 87"]Data Validation List for "Skills" columns[/TD]

    [td][/td]


    [TD="width: 87"]Data Validation List for "Status" columns[/TD]

    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Start

    [/td]


    [td][/td]


    [td]

    Training

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Excel

    [/td]


    [td][/td]


    [td]

    Stand Alone

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Word

    [/td]


    [td][/td]


    [td]

    Verified

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Qualified

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    As in this example;
    I want to look for a specific skill such as "Start" in Row 2:2. When I find "Start", I want to look to the cell to the right. If this cell is "Qualified", I want to return "Q". If not then look at the second cell for "Verified" and return "V". then on to "Stand Alone" with "S" as the returned value. And lastly "Training" with a return of "T". If none are found I want to return " ".


    I am wanting the formula to work down the list from "Qualified" to "Verified" to "Stand Alone" to "Training".



    Thanks in advance for your help!
    XJmikeT

  • Not sure exactly what you are trying to do, but assuming you are trying to track the status of training of individuals based on what's the training start status, and what I could understand, use this formula in column K. Sample file attached


    [SW]=IFERROR(LEFT(INDEX(A1:J2,,SUMPRODUCT(MAX((A2:I2="Start")*(B2:J2<>"")*COLUMN(B2:J2)))),1),"Not Started")[/SW]

Participate now!

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