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

  • Sorry, I uploaded the outdated one. B1 on sheet 1 has a full label and does not match TG1.


    Also on sheet 2, Joe has 2 skillsets as primary so I'm looking for partial match instead of full. The formula works for a single skillset and only as long as B1 is named exactly the same.

  • 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