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


    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:


    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.



  • 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!