Formula Errors

Check Whether a Value Falls Within Any Excel Interval

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Check whether a value falls within any of several start and end intervals using one formula.

Check Whether a Value Falls Within Any Excel Interval
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click an empty cell, such as E2, where you want the result to appear.

2
Enter the formula

Type =IF(SUMPRODUCT(--(D2>=$A$2:$A$100),--(D2<=$B$2:$B$100))>0,"Yes","No") and press Enter.

3
Adjust the interval ranges

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.

Use SUMPRODUCT to check all Excel intervals at once
Choose how to handle boundaries: The formula includes both endpoints. To exclude them, replace >= with > and <= with <.
Use WPS Spreadsheets

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. 1. Open your spreadsheet: Launch WPS Spreadsheets and use Open to select your workbook.
  2. 2. Check the input cells: Place numeric interval starts in column A, ends in column B, and the value to test in D2.
  3. 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. 4. Match the ranges to your data: In the formula bar, adjust both ranges to cover the same populated interval rows, then press Enter.
Check multiple intervals with one formulaOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.