Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

    Hello:


    Trying to figure out how I can match the name across 2 sheets and then search columns B and C for the TG1 skill in sheet 2, then mark the names with a Y in column B of sheet1.


    Sheet 1

    A1 Name / B1 Tech Grade 1

    A2 Joe / B2 Box to be filled or not based on result for Joe in sheet2

    A3 Dave / B3 Box to be filled or not based on result for Dave in sheet2

    A4 Karen / B4 Box to be filled or not based on result for Karen in sheet2

    Etc...


    Sheet 2 export from a phone system.

    A1 Name / B1 Primary Skill / C1 Secondary Skill

    A2 Frank / TG1 / TG2

    A3 Dave / TG3 / TG1

    A4 Karen/ TG2 / TG3



    I have a formula that works as long as B1 in Sheet 1 is named TG1:

    =IF(SUMPRODUCT((Sheet2!$B$2:$C$7=Sheet1!B$1)*(Sheet2!$A$2:$A$7=Sheet1!$A2)),"Y","")


    But I need to have the label for B1 in Sheet1 expanded to a full name. Is there any way to adjust this formula to not look for a match of the value B1 in Sheet 1 and only search for a text string or partial text string?


    Thank you.


    Sincerely,


    Patrick

    Hi,


    Your formula work for me.

    Please put in your file (without formula) how you want to be.

    With your formula:

    Name TG1 TG2 TG3
    Joe Y Y
    Dave Y Y
    Karen Y Y
    Bob Y Y

    Try this:

    Instead Tech Grade 1 in B1, use Custom Formatting "Tech Grade "# then in cell B1 put just number (1 or 2 or ...)

    In Sheet1, cell B2, use this formula:

    =IF(SUM(ISNUMBER(FIND("TG" & B$1,Sheet2!$B$2:$C$7))*(Sheet2!$A$2:$A$7=$A2))=1,"y","") than drag down till last name

    and if you put in C1 number 2, then you can drag formula from B1 to C1 then drag down and so on. :)


    Name Tech Grade 1 Tech Grade 2 Tech Grade 3
    Joe y y y
    Dave y   y
    Karen   y y
    Bob   y y

Participate now!

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

Register Yourself Login