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

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

September 8, 2016

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

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com