IFS tests multiple conditions in order and returns the value for the first test that is true. It replaces long nested IF formulas with one readable function.
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)What does IFS do?
IFS is a logical function that checks a series of conditions from left to right and returns the result attached to the first condition that evaluates to TRUE. Instead of stacking IF inside IF inside IF, you list each test and its result as a pair, which makes formulas shorter and much easier to read.
The function matters because nested IFs are one of the most common sources of spreadsheet errors. A missing parenthesis or a misplaced comma in a five-level nested IF is hard to spot. IFS keeps the logic flat: test one, result one, test two, result two, and so on. Office workers and students can audit the logic at a glance.
A typical situation is grading or banding. A teacher wants to turn a numeric score into a letter grade, or a sales manager wants to label orders as Small, Medium, or Large based on order value. With IFS you write one formula such as =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",TRUE,"F") and fill it down the column.
When to use IFS
- Converting numeric scores into letter grades or performance bands.
- Assigning shipping or pricing tiers based on order value or quantity.
- Labelling records as Overdue, Due Soon, or On Time from a date difference.
- Categorising employees or students into experience levels from years of service.
- Flagging survey responses or ratings into sentiment groups such as Positive, Neutral, or Negative.
- Choosing a commission rate or discount percentage from a sales threshold.
IFS syntax
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], …)| Argument | Required? | What to enter |
|---|---|---|
logical_test1 | Required | The first condition to check. It must evaluate to TRUE or FALSE, for example B2>=1000. |
value_if_true1 | Required | What to return when the first test is TRUE. Can be a number, text in quotes, a cell reference, or another formula. |
logical_test2 | Optional | The second condition, checked only if the first test was FALSE. You can add up to 127 test/value pairs. |
value_if_true2 | Optional | What to return when the second test is TRUE. Each test needs its own matching result. |
What it returns: The value paired with the first logical test that evaluates to TRUE. If no test is TRUE and no fallback is supplied, IFS returns the #N/A error.
IFS examples
1 Grade a single score
The goal is to turn one exam score into a letter grade. The formula checks the score in B2 against 90, then 80, then 70. Because 85 is not at least 90 but is at least 80, the second test is the first TRUE one and the formula returns B. The order matters: always list the highest threshold first.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | |||
| 2 | Ana | 85 | B | ||
| 3 | Ben | 92 | |||
| 4 | Cara | 67 | |||
| 5 |
2 Label order size from sales value
Here the goal is to categorise a sales amount into Small, Medium, or Large. The formula tests B2 against 1000 first, then 500. The value 750 fails the first test but passes the second, so the result is Medium. The final TRUE pair acts as a catch-all for anything below 500.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Sales | |||
| 2 | SO-101 | 750 | Medium | ||
| 3 | SO-102 | 1200 | |||
| 4 | SO-103 | 320 | |||
| 5 | SO-104 | 980 | |||
| 6 |
3 Flag overdue invoices from days outstanding
This example labels an invoice by how many days it is past due. The formula checks C2 against 30 and then 1. A value of 45 is at least 30, so the first test wins and the result is Overdue. A value of 0 would fail both tests and fall through to the TRUE pair, returning Current.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Customer | Days Past Due | |||
| 2 | INV-2001 | Northwind | 45 | Overdue | ||
| 3 | INV-2002 | Contoso | 12 | |||
| 4 | INV-2003 | Fabrikam | 0 | |||
| 5 | INV-2004 | Adventure | 31 | |||
| 6 |
4 Assign a commission rate by sales band
The goal is to return a commission percentage based on total sales. The formula tests B2 against 10000, 5000, and 1000 in descending order. Because 6200 is below 10000 but at least 5000, the second test is TRUE and the formula returns 0.05. The final TRUE pair gives 0.02 to any amount below 1000.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | |||
| 2 | Diaz | 6200 | 0.05 | ||
| 3 | Evans | 14500 | |||
| 4 | Fischer | 800 | |||
| 5 | Gupta | 3100 | |||
| 6 |
5 Categorise employees by years of service
This realistic HR example groups staff into Senior, Mid, or Junior based on years of service. The formula checks C2 against 10 and then 3. With 7 years, the first test fails and the second passes, so the result is Mid. The TRUE fallback covers anyone with fewer than 3 years.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee | Department | Years | |||
| 2 | Hughes | Finance | 7 | Mid | ||
| 3 | Ibrahim | IT | 12 | |||
| 4 | Jensen | Sales | 2 | |||
| 5 | Kaur | HR | 4 | |||
| 6 | Lopez | Finance | 15 | |||
| 7 |
Common IFS errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | No logical test evaluated to TRUE and no fallback pair such as TRUE,"Other" was included. | Add a final TRUE test with a default result, or make sure your thresholds cover every possible value. |
| #VALUE! | A logical test compares incompatible types, for example text against a number, or the formula is missing an argument. | Check that each test compares like with like. Use ISNUMBER or ISTEXT to inspect the cell, and confirm every test has a matching result. |
| #NAME? | The function name is misspelled, or the workbook uses an Excel version older than 2019 or Microsoft 365 where IFS is not available. | Spell it as IFS. If your version lacks IFS, use nested IF formulas or update Excel. |
| Wrong result | Tests are listed in the wrong order, such as smallest threshold first, so a broad test catches values meant for a later test. | Order tests from most restrictive to least restrictive, for example highest threshold first, and end with a TRUE fallback. |
| #VALUE! (too many arguments) | An odd number of arguments was supplied, leaving a test without a matching result. | Count the arguments. Every logical test must be followed by exactly one value_if_true. |
Tips for using IFS
- Always end with a
TRUEpair as a catch-all so the formula never returns #N/A. - List the strictest condition first. For numeric bands, that usually means the highest threshold first.
- Keep each test simple. If a condition needs AND or OR, combine them inside the test with
AND(...)orOR(...). - Use cell references for thresholds stored in a small lookup area so you can change bands without editing the formula.
- Press Alt+Enter inside the formula bar to put each test on its own line, which makes long IFS formulas easier to audit.
- Test the formula on the boundary values, such as exactly 90 or exactly 1000, to confirm which band wins.
Frequently asked questions
What is the difference between IFS and nested IF?
They do the same job, but IFS is flat and easier to read. A nested IF repeats the word IF for every condition, while IFS lists test and result pairs once. IFS is available in Excel 2019, Microsoft 365, and later.
Does IFS check every condition?
No. IFS stops at the first test that is TRUE and returns its paired value. Later tests are ignored, which is why order matters.
Why does my IFS formula return #N/A?
No test was TRUE and you did not supply a fallback. Add a final pair such as TRUE,"Other" to catch everything else.
Can I use IFS with text or dates?
Yes. Tests can compare text with = or dates with >= as long as both sides use the same type. For dates, compare against a date value or a cell containing a date.
How many conditions can IFS handle?
Up to 127 test/value pairs, which is far more than most business formulas need. If you approach that limit, a lookup table with VLOOKUP or XLOOKUP is usually a better design.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. IFS reference