If statement to encompass an Array

[Excel_77]

I have the following statment within my formula and it work as it is, it bascally returns "Yes" if K2 = "Liverpool".

=IF(K2="Liverpool","Yes","No")

However I have a list of 12 names in tab "Lookup" A1-A12 and I want to expand the formula above to also check L2 and if it matches any of the names within the looup then "yes" should be returned -how is this done?

Thanks in advance

 

[WaterGypsy]

If I understand you correctly, this should work:

=IF(OR(COUNTIFS(Lookup!A1:A12,K2)>0,COUNTIFS(Lookup!A1:A12,L2)>0),"Yes","No")

 

[Excel_77]

If I understand you correctly, this should work:

=IF(OR(COUNTIFS(Lookup!A1:A12,K2)>0,COUNTIFS(Lookup!A1:A12,L2)>0),"Yes","No")

So in order to get a "Yes" k2 needs to = "Liverpool" an L2 needs to match one of the names on the list.

 

[WaterGypsy]

With this formula you will get Yes if either K2 or L2 has one of the names in A1:A12 ... that's what I thought you needed
If you need Liverpool in K2 and one of the names from A1:A12 then you need

=IF(AND(K2="Liverpool",COUNTIFS(Lookup!A1:A12,L2)>0),"Yes","No")

 

[Excel_77]

With this formula you will get Yes if either K2 or L2 has one of the names in A1:A12 ... that's what I thought you needed
If you need Liverpool in K2 and one of the names from A1:A12 then you need

=IF(AND(K2="Liverpool",COUNTIFS(Lookup!A1:A12,L2)>0),"Yes","No")

That worked, thanks so much.