Formula Errors

Excel Formula Errors: Use AND for Multiple Range Conditions

Adam DavisAdam Davis Nov 12, 2026 868 views

Question details

The user needs to test whether values fall within numeric ranges while also checking another condition in an Excel formula.

Excel Formula Errors: Use AND for Multiple Range Conditions
Product
Microsoft Excel formulas
Device & OS
not provided
Scenario
A formula must verify conditions such as income being between two limits and household size matching a target value before returning a result.
Observed behavior
A chained comparison such as 15>A3>=10 returns an unexpected FALSE because Excel does not evaluate chained comparisons as a single range test.
Before you start

Identify the cells and boundary values for each condition, and check whether the limits should be inclusive or exclusive. Keep the lower and upper limits in separate comparisons before combining them with AND.

Solution 1Recommended

Replace chained comparisons with separate AND conditions

Use this solution whenever a formula needs to confirm that a value falls between two limits or satisfies several conditions at once.

Excel evaluates a chained expression such as 15>A3>=10 from left to right rather than treating it as a mathematical range test. Write each comparison separately inside AND so every condition is evaluated against the original cell value.

1
Select the result cell

Click the cell where the TRUE/FALSE result or conditional output should appear, then activate the formula bar or press F2.

2
Write separate range comparisons

Enter a formula such as =AND(A3>=10,A3<15) to test whether A3 is at least 10 and less than 15. Use <= when the upper boundary should be included.

3
Add the other condition

Include the additional test as another AND argument, for example =AND(B2>=10,B2<=35) when B2 must be between 10 and 35.

4
Return a conditional result with IF

To display a value only when all conditions are met, enter =IF(AND(G2=B2,D2>=H2,D2<C2),A2,"") in the target cell.

Correct range test: For the example values A3=10 and B2=28, use separate comparisons such as =IF(AND(A3>=10,A3<15,B2>=15,B2<=35),"true","false") rather than a chained comparison.

Test Multiple Conditions in WPS Spreadsheet with AND and IF

WPS Spreadsheet lets you replace invalid chained comparisons with separate AND conditions, combine them with IF, and use compatible lookup formulas to return matching table results. The familiar formula bar and worksheet menus make the migration straightforward.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel .xlsx file from the home screen or File > Open.
  2. 2. Select the formula cell: Click the cell containing the incorrect chained comparison, then select the formula bar to edit the expression.
  3. 3. Replace the chained comparison: Enter separate tests inside AND, such as =AND(A3>=10,A3<15), and press Enter to calculate the result.
  4. 4. Add a conditional output: Use the IF function when needed, such as =IF(AND(G2=B2,D2>=H2,D2<C2),A2,""), then fill the formula down the required range.
Build accurate inclusive or exclusive range tests with AND.Combine household, income, and other criteria in one IF formula.Use FILTER and XLOOKUP-style table workflows when a matching record is required.Open, edit, and save Microsoft Excel .xlsx workbooks with format compatibility.Use a lightweight spreadsheet interface without changing your existing worksheet structure.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel return FALSE for 15>A3>=10?

Excel does not support mathematical chained comparisons. It evaluates 15>A3 first, produces TRUE or FALSE, and then compares that logical result with >=10. Use =AND(A3>=10,A3<15) instead.

How do I include both endpoints in a range?

Use <= for the upper or lower comparison when the boundary is inclusive. For example, =AND(A3>=10,A3<=15) includes both 10 and 15.

How can I return a cell value when every condition is met?

Wrap AND inside IF. For example, =IF(AND(G2=B2,D2>=H2,D2<C2),A2,"") returns the value in A2 when all tests are true and an empty string otherwise.

Can I use this method for household size and income tables?

Yes. Use AND for direct range checks, or use FILTER to narrow the table by household size and XLOOKUP to find the corresponding income range or salesperson.