Excel SWITCH Greater Than: Test Ranges and Conditions

How-to basics · Updated November 18, 2026

Excel’s SWITCH function cannot use a greater-than sign as a direct match test, but it can evaluate greater-than conditions when you set its first argument to TRUE. With a score in A2, for example, the formula =SWITCH(TRUE,A2>90,"A",A2>80,"B",A2>70,"C","D") returns B when A2 is 87; the conditions are checked in order, and the first true one supplies the result.

What SWITCH Does in Excel

SWITCH is designed to compare one expression with a list of exact values, then return the result paired with the first match. It is useful when a cell might contain one of several known codes, statuses, or labels. For instance, a cell containing the text Open could return an instruction such as Review, while Closed could return Archive.

A greater-than comparison is different from an exact match. The condition A2>80 asks whether a value is above 80 and produces either TRUE or FALSE. A normal SWITCH call compares its expression with the values you list; it does not interpret a case such as >80 as a range rule. That distinction is why a straightforward formula such as SWITCH(A2,>80,"B") is not valid Excel syntax.

Using TRUE as the expression is a practical workaround: each condition becomes a candidate value that can match TRUE. This approach makes SWITCH act like a sequence of ordered tests, although it is not a special greater-than mode built into the function.

How SWITCH(TRUE, …) Evaluates Conditions

In a SWITCH(TRUE,...) formula, Excel evaluates each condition from left to right. A comparison such as A2>90 returns TRUE if it passes and FALSE if it does not. The first condition that returns TRUE matches the expression TRUE, so Excel returns the result beside that condition and stops checking later cases. If none matches, Excel uses the final default argument, if one is supplied.

With a score in A2, this formula checks whether the score is greater than 90, then greater than 80, then greater than 70, and otherwise returns D. For a score of 87, the first condition is false and the second is true, so the result is B:

Cell layout: A2 contains the score 87. Formula: =SWITCH(TRUE,A2>90,"A",A2>80,"B",A2>70,"C","D"). Result: B.

The last item, "D", is the default result, not another condition-result pair. It makes the output predictable for values that do not meet any listed test. Without a default, SWITCH returns #N/A when no value matches, which is often less useful in a report.

Set the Thresholds and Their Order

Because evaluation stops at the first true condition, put overlapping thresholds in the order that gives the intended answer. For descending score bands, test the highest threshold first. If you test A2>70 before A2>90, a score of 95 already passes the first condition and will receive the lower band’s result. The later test is never reached.

Choose the comparison operator to match the rule precisely. > means strictly greater than, so a value equal to the threshold does not qualify. >= includes the threshold. Similarly, < means less than and <= means less than or equal to. These boundary choices matter for grades, service levels, discounts, and any rule where a value exactly on a cutoff must be classified consistently.

For example, with 90 as the top grade boundary, a rule using A2>90 does not give an A for exactly 90. If the policy says 90 and above earns an A, use A2>=90. Write the rule in words before building the formula, then check values just below, exactly at, and just above each boundary. This simple test catches many off-by-one errors.

  • Use descending thresholds when higher values should receive higher bands.
  • Use strict > or inclusive >= deliberately, rather than choosing one by habit.
  • Add a default for values outside the listed bands, and decide what blanks or invalid entries should display.

A Worked Example with Inclusive Grade Bands

Suppose a grading rule says 90 or higher is A, 80 through 89.99 is B, 70 through 79.99 is C, and anything lower is D. Since the thresholds are inclusive at the lower end of each band, the formula can test from highest to lowest using >=. With a score of 80 in A2, the first condition is false and the second is true, so the formula returns B.

Cell layout: A2 contains a numeric score of 80. Formula: =SWITCH(TRUE,A2>=90,"A",A2>=80,"B",A2>=70,"C","D"). Result: B.

Notice that the formula does not need to spell out an upper limit such as “less than 90” for the B band. Once the 90-or-higher test fails, any value from 80 upward necessarily falls into B, until the formula has already excluded higher values. This is one benefit of ordered thresholds: they can keep a tier formula compact. If the rules have gaps or exceptions, however, write explicit tests or use a clearer approach rather than assuming the bands are continuous.

When IF or IFS Is a Better Choice

If the only question is whether one value is greater than a cutoff, IF is usually the clearest option. It directly expresses a true result and a false result. With a sales amount in A2, the following returns Target met for amounts above 1,000 and Below target otherwise.

Cell layout: A2 contains a sales amount of 1,250. Formula: =IF(A2>1000,"Target met","Below target"). Result: Target met.

For several ordered conditions, IFS is often easier to read because it accepts condition-result pairs without requiring the TRUE pattern. It is available in Excel 2019 and later, including Microsoft 365. Put the most selective or highest threshold first, just as with SWITCH(TRUE,...).

Cell layout: A2 contains a score of 87. Formula: =IFS(A2>90,"A",A2>80,"B",A2>70,"C",TRUE,"D"). Result: B.

The final TRUE,"D" pair provides a catch-all result in this IFS example. Without a condition that catches the remaining cases, IFS can return an error when no condition is true. Use SWITCH when you are mapping one expression to exact options or when your team finds the SWITCH(TRUE,...) layout readable; choose IF or IFS when it makes the logic more obvious. The best formula is the one a future editor can verify against the stated rules.

Common Problems and How to Fix Them

A common mistake is putting a comparison operator where SWITCH expects a value to match. The function does not accept a range expression in that position as a greater-than rule. Use SWITCH(TRUE,...) with complete comparisons, or replace it with IF or IFS.

Another frequent issue is putting conditions in the wrong order. If a broad condition appears first, more specific tests later may never run. Review each condition with a sample value and confirm which branch should win. This is particularly important when ranges overlap, such as “greater than 50” and “greater than 100.”

Check the cell’s data type as well. A number stored as text may not behave like an ordinary numeric value in comparisons. If a result seems unexpected, inspect the source data for leading apostrophes, imported text, spaces, or formulas that return text. Also decide how a blank should behave: depending on the surrounding data and comparisons, it may fall through to the default rather than produce a meaningful category. Add a deliberate blank test if blanks need a distinct label.

Finally, include a default result and test edge cases. For a grading formula, test values below the lowest band, on each cutoff, and above the highest band. For a business rule, test a blank, a typical value, and an unusual but valid value. A worksheet can display a plausible answer while still applying the wrong boundary, so checking only one ordinary example is not enough.

Availability and Editing the Formula

SWITCH is available in Excel 2019 and later, including Excel for Microsoft 365. The formula examples here work in Microsoft 365 for Windows; the function is also available in current Excel for Mac and Excel for the web. Ribbon placement for editing a cell differs by platform, but the formula itself does not need a Windows-specific version. If a workbook must open in a much older Excel release, confirm compatibility with that release before relying on SWITCH; IF is a more widely supported alternative.

To edit a formula in a worksheet on Windows, select the cell and press F2, or click in the formula bar, then change the conditions and press Enter. On Mac, select the cell and use Control+U to edit the active cell, or edit in the formula bar; shortcut behavior can vary with keyboard settings. If Excel displays a formula error, check that every condition-result pair is present, text results use straight double quotation marks, commas separate arguments in the regional settings shown by your Excel installation, and parentheses are balanced.

Frequently Asked Questions

Can SWITCH directly test whether a number is greater than 10?
No. A normal SWITCH call compares an expression with exact values. Use SWITCH(TRUE,...) and a condition such as A2>10, or use IF for a single comparison.

Does SWITCH(TRUE,…) return the first matching result?
Yes. Excel evaluates the conditions in order and returns the result paired with the first condition that evaluates to TRUE. Later conditions do not override an earlier match, so order matters.

Should I use greater than or greater than or equal to?
Use > when a value exactly equal to the threshold should not qualify. Use >= when the threshold itself should qualify. Check the rule’s wording and test the exact boundary.

Why does my SWITCH formula return #N/A?
Usually, none of the listed values or conditions matched and no default result was provided. Add a final fallback result, or check that the tests cover every value you expect.

Is SWITCH better than IFS for ranges?
Not automatically. Both can handle ordered conditions, but IFS states condition-result pairs directly, while SWITCH(TRUE,...) uses TRUE as a matching device. Use whichever makes the thresholds and fallback easiest to understand and maintain.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.