Trying to match name in 2 different sheets and then mark a Y value based on specific text

[PLucas1]

Hello:

I am working with skillsets downloaded from a phone system (Sheet2). The goal is to:
1. Match the name from Sheet1 and Sheet2
2. Then look at the columns B and C in Sheet2 for data that contains "TG1"
3. Then put a Y in column B of Sheet1 if if the person has TG1 skill.

Sheet2 can contain the engineers out of order and more names than Sheet1.


Any assistance here would be greatly appreciated as I've been wracking my brain against multiple formulas for days.

Sample workbook is attached.
Sample2sheetmatch.xlsx

Sincerely,

Patrick

[JohnTopley]

In B2

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

Copy across and down

[PLucas1]

Hi John:

Thank you for the reply. That worked but, to complicate things a little more...

Is there any way for it not to look at the B1 text of Sheet1 to see if it matches B or C in Sheet2?

For example, the column name in Sheet1 will be unabbreviated and shown as Tech Grade 1. So anything that matches TG1 in Sheet2 gets marked as Y in column B of Sheet1.

I tried modifying the formula to contain text instead of the B1 header reference, but it errors out.
=IF(SUMPRODUCT((Sheet2!$B$2:$C$7='TG1')*(Sheet2!$A$2:$A$7=Sheet1!$A2)),"Y","")

Again, thank you for your help here.

Sincerely,

Patrick

[Gregb11]

You would have to have a different formula in each column then. You aren't using quotation marks in your formula. Try this in B2 and copy down, and then in C2 you'll have to change the text to "TG2", etc.)

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