CODEWITHSUNDEEP
excelexcel-formula
Chris Bak
Chris Bak

Reputation: 31

Determine if values in a column are between 2 numbers in an array

Excel formula that looks up if X is in between 2 values in a matrix and says yes or no (1 or 0). This formula is intended to be copied as the Y-value to the X. X column will be up to 50,000 rows and the matrix will be up to 100rows

enter image description here

This code works:

=IF(OR(AND(A2>=$D$2,A2<=$E$2),AND(A2>=$D$3,A2<=$E$3)),1,0)

but will get very cumbersome if the matrix gets larger, ie up to 50 rows. I also tried:

{=IF(AND(A2>=$D$2:$D$3,A2<=$E$2:$E$3),1,0)}

but returns #VALUE!

enter image description here

Upvotes: 0

Views: 249

Answers (2)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

If you can, add an extra column to the "matrix" to store the result (1,0) for each range.

Use vlookup with approximate results

enter image description here

Upvotes: 0

JvdV
JvdV

Reputation: 75990

You could also make use of SUMPRODUCT() like so in B2 and drag down:

=IF(SUMPRODUCT((A2>=$D$2:$D$3)*(A2<=$E$2:$E$3))>0,1,0)

enter image description here

And here is a way to make it easy on yourself, create a table out of your ranges, and if you add or remove from there the formula will adjust. Like so:

=IF(SUMPRODUCT((A2>=Tabel1[X1])*(A2<=Tabel1[X2]))>0,1,0)

enter image description here

Upvotes: 1

Related Questions