IFS function
Quick answer
IFS checks a list of conditions in order and returns the value paired with the first one that is TRUE.
- Syntax
=IFS(test1, value1, [test2, value2], ...)- Most common use
=IFS(B2>=10000,"Gold",B2>=2500,"Silver",TRUE,"Bronze")returns Gold (The first test is TRUE, so Gold)
It replaces nested IFs for tiers and grades: =IF(A,x,IF(B,y,z)) becomes a flat list of test and value pairs that is easier to read and edit.
- Available in
- Excel 2019 and later, including Microsoft 365
- Category
- Logical functions
- Returns
- The value for the first TRUE test, or #N/A when none of the tests is TRUE.
- Google Sheets
- Available in Google Sheets.
Syntax
=IFS(test1, value1, [test2, value2], ...)
| Argument | Required? | What it is |
|---|---|---|
test1 | Required | The first condition to check. |
value1 | Required | What to return when test1 is TRUE. |
test2 | Optional | The next condition, checked only when the ones before it are FALSE. Add as many pairs as you need, up to 127. |
value2 | Required | What to return when test2 is TRUE. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =IFS(B2>=10000,"Gold",B2>=2500,"Silver",TRUE,"Bronze") | Gold | The first test is TRUE, so Gold |
| D3 | =IFS(B3>=10000,"Gold",B3>=2500,"Silver",TRUE,"Bronze") | Silver | The first test is FALSE and the second is TRUE, so Silver |
| D4 | =IFS(B4>=10000,"Gold",B4>=2500,"Silver") | #N/A | No catch-all at the end, so a value below every threshold returns the #N/A error |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Customer | Annual spend | Tier | |
| Lakeview Dental | $14,200 | Gold | |
| Birch Cafe | $3,800 | Silver | |
| Northline Freight | $620 | #N/A |
Practice IFS
From lesson 6, AND, OR & IFS. Graded instantly, then re-checked on changed data.
Letter grades
C2, use IFS to assign a grade: 90+ is A, 80–89 B, 70–79 C, below 70 F. Fill down to C7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A | None of the tests was TRUE. End the list with TRUE and a default value, such as TRUE,"Other". |
| Tiers in the wrong order | IFS stops at the first TRUE test. Put the strictest condition first (>=10000 before >=2500), or every large customer lands in the lower tier. |
| #NAME? | IFS needs Excel 2019 or later. In Excel 2016, use nested IFs or a lookup table with an approximate match. |
| "You've entered too few arguments" | Every test needs a value after it. Look for a test that is missing its result. |
Tips
- TRUE as the last test works as "everything else".
- For long tier lists, a small table with XLOOKUP and match_mode -1 is easier to maintain than a long IFS.
Questions
What is the difference between IF and IFS?
IF handles one test with two outcomes. IFS handles many tests in a flat list and returns the value for the first one that is TRUE, which replaces nested IFs.
How do I add an "else" to IFS?
Make the last pair TRUE and your default value: =IFS(A2>90,"A",A2>80,"B",TRUE,"C").
Is IFS available in Excel 2016?
No. It arrived in Excel 2019 and is in Microsoft 365. Older versions show #NAME?.
Lessons that use IFS
Answers that use IFS
In other languages
IFS is SI.CONJUNTO in Spanish Excel and WENNS in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .