Excel formula to determine if a value falls between two ranges in a list

You can use the SUMPRODUCT formula in Excel to find whether or not a value in falls with in ranges given in two other columns.

In this example we have the values we want to check in column A, and the possible ranges in which this value may fall in columns F and G. In column H we need to put a number to be returned when a hit is returned. The formula will not work if text is entered in column H.

=SUMPRODUCT(($F$1:$F$7<=A1)*($G$1:$G$7>=A1)*$H$1:$H$7)

As you can see if one of the values in column A equals the beginning or ending of the range, a hit will still be returned.

Thanks to Paddy D for posting this solution. See:

http://www.mrexcel.com/forum/excel-questions/21727-lookup-between-two-values.html