Function Problems

Use Excel IF or IFS to Test Values Between Two Limits

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

The user wants to test whether a value falls between two limits using an Excel formula.

Use Excel IF or IFS to Test Values Between Two Limits
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet needs to identify values greater than 11 and less than or equal to 12, or classify values into multiple ranges.
Observed behavior
A chained comparison such as =11<A2<=12 does not test both limits as intended.
Before you start

Identify the cell containing the numeric value and decide whether each limit should be included. The examples use A2, exclude 11, and include 12.

Solution 1Recommended

Use AND or IF to Test Both Limits in Excel

Use AND for a TRUE or FALSE result, or wrap it in IF to return custom labels.

Excel evaluates a chained comparison from left to right. The first comparison produces TRUE or FALSE, which is then compared with the second limit instead of testing the original value again.

1
Enter the value

Type a numeric value in cell A2 of your Excel worksheet.

2
Test both conditions

Select an empty cell and enter =AND(A2>11,A2<=12). The result is TRUE only when A2 is greater than 11 and at most 12.

3
Return a label instead

To display text instead of TRUE or FALSE, enter =IF(AND(A2>11,A2<=12),"In range","Out of range") in an empty cell.

4
Apply the formula to other rows

Select the formula cell and drag its fill handle down to test the values in subsequent rows.

Use AND or IF to Test Both Limits in Excel
Adjust the boundaries: Use >= instead of > to include the lower limit, or use < instead of <= to exclude the upper limit. If Excel requires semicolons as argument separators, replace the commas in the formula.
WPS Spreadsheet

Test Values Between Two Limits in WPS Spreadsheet

Use AND and IF in WPS Spreadsheet to check both boundaries and return a clear result for each value.

  1. 1. Open a spreadsheet: Open WPS Office, choose Spreadsheet, and create a workbook or open an existing Excel file.
  2. 2. Enter a value: Type the numeric value you want to test in cell A2.
  3. 3. Enter the range formula: Select an empty cell and enter =IF(AND(A2>11,A2<=12),"In range","Out of range").
  4. 4. Test additional rows: Select the formula cell and drag its fill handle down to apply the formula to other values in column A.
Test inclusive and exclusive range boundaries with familiar formulasOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface to fill formulas down a columnWork with spreadsheets using the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't =11<A2<=12 work as a range test?

Excel evaluates the first comparison and then compares its TRUE or FALSE result with 12. Use =AND(A2>11,A2<=12) to compare A2 with both limits separately.

How do I include both limits in the test?

Enter =AND(A2>=11,A2<=12) in an empty cell. This returns TRUE for both 11 and 12, as well as values between them.

How can I show text instead of TRUE or FALSE?

Wrap the AND test in IF, for example: =IF(AND(A2>11,A2<=12),"In range","Out of range").

What can I use if my Excel version doesn't support IFS?

Use IF with AND for a single range, or nested IF formulas for multiple ranges. For many ranges, consider a lookup table instead.