CODEWITHSUNDEEP
excel
Tam Coton
Tam Coton

Reputation: 864

Find if any value from a range is in an array

I want a formula that will look at a (one-wide) array of cells, and return TRUE if any of those cells take a value between 22 and 30, and FALSE otherwise. Is there an elegant way to do this, or should I just search for every value individually, and shove them inside an OR?

The only way I can think of to do it is

=NOT(AND(ISNA(VLOOKUP(22,ArrayAddress,1)),ISNA(VLOOKUP(23,ArrayAddress,1)),ISNA(VLOOKUP(24,ArrayAddress,1))...))

Upvotes: 3

Views: 81

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

try to use following formula:

=SUMPRODUCT((ArrayAddress>=22)*(ArrayAddress<=30))>0

or 

=COUNTIFS(ArrayAddress,">=22",ArrayAddress,"<=30")>0

Upvotes: 3

Related Questions