Excel formula to find if list of numbers falls in range of numbers listed in two columns

# Excel formula to find if list of numbers falls in range of numbers listed in two columns

November 18, 2016

You can use the SUMPRODUCT formula to check and see if a list of numbers in one column falls within a range of numbers given in two other columns.   This can come in handy, if for example, you have a list of Bates numbers and want to know if they all fall with PRODBEG and PRODEND ranges in a document production.   In this example, we have a list of numbers in column A, and ranges of produced documents in columns D and E.   Column F simply contains a reference to be returned by the formula.  So as you can see this formula:

=SUMPRODUCT((A1>=\$D\$1:\$D\$10)*(A1<=\$E\$1:\$E\$10)*\$F\$1:\$F\$10)

. . . finds when the value in column A is between the values in columns D and E, and then when this is true gives you the number in column F.  Thanks to CRondao for posting this solution at www.stackoverflow.com.