Posts by PLucas1

Updated.

See the ExpectedFinal sheet.

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.

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