IF Function with OR(), using an array

[Terence Mervis]

Case - Column A is a long list of zip codes. Column B has in it only 6 specific zip codes.These 6 zip codes are also included among those zip codes listed in column A. How can the following formula be shortened using an array- =IF(OR(A1=$B$1,A1=$B$2,A1=$B$3,A1=$B$4,A1=$B$5,A1=$B$6),”Yes”,””)

Many Thanks

 

[bosco_yip]

Shortened ways.

1] Array formula, confirmed pressing with CTRL+SHIFT+ENTER 3 keystrokes together :

=IF(OR(A1=B1:B6),"Yes","")

2] Or, this non-array formula :

=IF(SUMPRODUCT(0+OR(A1=B1:B6)),"Yes","")

Regards

 

[John Jairo V]

Hi to all!

This formulae works too (no CSE):

=IF(COUNTIF($B$1:$B$6,A1),"Yes","")

Blessings!