How do I pull back data from a column (not a vlookup) but must be similar

[alethea2022]

I have sheet one - the same will appear multiple times as the below

Ali.mcloughlin@email.com	Microsoft Word
Ali.mcloughlin@email.com	Microsoft Excel
Ali.mcloughlin@email.com	Microsoft Powerpoint

In another sheet I have the email address once

ali.mcloughlin@email.com

Whatsapp

Microsoft Word

Microsoft Excel

Windows 10

Microsoft Excel

Microsoft Access

I would like a formula in sheet one in column c that says if ali.mcloughlin@email.com appears in sheet 2 and has Microsoft Word in one of the columns bring back yes or Microsoft word

Hope this makes sense as i have a mamouth task, than k you so much

 

[kweaver]

One way might be:

=IFERROR(IF(SUM(--(FILTER(Sheet2!$B$1:$G$11,A2=Sheet2!$A$1:$A$11)=B2)),"Yes",""),"")