Check Whether a Value Falls Within Any Excel Interval
Question details
Check whether a value falls within any of several start and end intervals using one formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Start distances are in column A, end distances are in column B, and a separate value must be tested against all intervals.
- Observed behavior
- A single formula should return Yes if at least one interval contains the value and No otherwise.
Make sure the start and end distances are numeric, each start is no greater than its corresponding end, and the value to test is in D2. Adjust the formula ranges to cover the populated interval rows without including blank rows.
Use SUMPRODUCT to check all Excel intervals at once
Use this formula when each interval has a start in column A and an end in column B.
SUMPRODUCT counts rows where the value meets both boundary conditions. The formula returns Yes if at least one row matches.
Click an empty cell, such as E2, where you want the result to appear.
Type =IF(SUMPRODUCT(--(D2>=$A$2:$A$100),--(D2<=$B$2:$B$100))>0,"Yes","No") and press Enter.
Edit $A$2:$A$100 and $B$2:$B$100 in the formula bar to match your populated interval rows. Keep the two ranges the same size.

Check Multiple Intervals in WPS Spreadsheets
Use the same SUMPRODUCT formula in WPS Spreadsheets to test a value against all populated start and end intervals.
- 1. Open your spreadsheet: Launch WPS Spreadsheets and use Open to select your workbook.
- 2. Check the input cells: Place numeric interval starts in column A, ends in column B, and the value to test in D2.
- 3. Enter the interval formula: Click an empty cell such as E2 and enter =IF(SUMPRODUCT(--(D2>=$A$2:$A$100),--(D2<=$B$2:$B$100))>0,"Yes","No").
- 4. Match the ranges to your data: In the formula bar, adjust both ranges to cover the same populated interval rows, then press Enter.

Frequently Asked Questions
Does the formula include the start and end values?
Yes. The >= and <= comparisons include both endpoints. Change them to > and < in the formula bar to exclude both endpoints.
What happens if the value matches more than one interval?
The formula returns Yes whenever SUMPRODUCT finds one or more matching rows. It does not list the matching intervals.
Can I test values other than the one in D2?
Yes. Replace D2 in both comparisons with the cell containing the value you want to test.
Why should I avoid including blank interval rows?
Blank cells can be treated as zero in comparisons and may produce an unintended match. Adjust both ranges in the formula bar to cover only populated interval rows.




