Show a particular letter/value if a specific cell is included with one of the lists

    Hi


    I have been struggling with this for a while and you haven't let me down before!!
    I have 5 lists, each with unique values. What I need is if a given cell starts with one of these unique values, indicate which list it comes from?!


    I have attached a file for you to look at.


    on tab 'All' call 'A3' I need a TW to show in that cell this is because cell 'E3' text 'A CARBON' begins with 'A C' which is included in TW list on tab 'Lists'


    Thank you for your help

    Re: Show a particular letter/value if a specific cell is included with one of the lis


    To simplify, you can add 3 helper columns, one to check 1st 3 characters, then one to check 1st 2 characters, then one to check 1st character.


    So in G3:


    [COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,3)&"#","#"&Lists!$A$2:$E$31&"#"))*(COLUMN(Lists!$A$2:$E$31)-COLUMN(Lists!$A$2:$A$31)+1)))[/COLOR]


    in H3


    [COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,2)&"#","#"&Lists!$A$2:$E$31&"#"))*(COLUMN(Lists!$A$2:$E$31)-COLUMN(Lists!$A$2:$A$31)+1)))[/COLOR]


    in I3


    [COLOR="#0000FF"]=SUMPRODUCT((ISNUMBER(SEARCH("#"&LEFT(E3,2)&"#","#"&Lists!$A$2:$E$31&"#"))*(COLUMN(Lists!$A$2:$E$31)-COLUMN(Lists!$A$2:$A$31)+1)))[/COLOR]


    each copied down.


    Then in A3 this Array* formula:


    [COLOR="#0000FF"]=IF(SUM(G3:I3)=0,"",INDEX(Lists!$A$1:$E$1,MIN(IF(G3:I3>0,G3:I3))))[/COLOR]


    copied down


    [arf]*[/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!

Register Yourself Login