top of page

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:


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

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

bottom of page