Return text if multiple criteria are met

[megera716]

How can I use INDEX/MATCH (or another formula) to return a text value if multiple criteria are met?

I have a table with 5,000 rows of data. On any given row, I want it to return "Yes", if the Name (Column E) on that row is found elsewhere in the table AND (on the row where the Name is found) if the Year of Sale (Column T) is equal to 2020 AND if the Account contains the word "Subscription"


I adapted this from a Google result, but it is returning a "Yes" where the Name and Account matches, but the Year of Sale does not (e.g., this customer has a sale in 2018 and 2019 and on those two rows, it is returning "yes", even though the year is not 2020).
=IF(ISNUMBER(MATCH(1,IF([Name]=[@Name],IF(ISNUMBER(SEARCH("Subscription",[Account])),IF([Year of Sale]=2020,1))),0)),"Yes","No")

EDIT:
I guess I sort of answered my own question in that the 2018 and 2019 rows are returning the yes because there are other rows where this customer DOES have a 2020 sale that meets the criteria. But that's not the intended result.

Basically, I want to know if a customer (Name) invoiced in 2020 was invoiced for a subscription, even if it's not on THAT row. And then I was going to adapt it (in another column to be added) to find out if they were invoiced in a prior year for a subscription.

 

[steve the fish]

This will produce TRUE or FALSE and you will need to adapt it to suit your ranges. I used row 1 here:

=IF(AND(ISNUMBER(SEARCH("subscription",A1)),T1=2020),COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>1,COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>0)

 

[megera716]

This will produce TRUE or FALSE and you will need to adapt it to suit your ranges. I used row 1 here:

=IF(AND(ISNUMBER(SEARCH("subscription",A1)),T1=2020),COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>1,COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>0)

Thanks, that looks to be working! Is there a way to make it return "yes" or "no"" instead of true or false?

 

[steve the fish]

Sure use another IF. So:

=IF(IF(AND(ISNUMBER(SEARCH("subscription",A1)),T1=2020),COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>1,COUNTIFS(E:E,E1,T:T,T1,A:A,"*"&"subscription"&"*")>0),"Yes","No")