The IFS function is one of Excel’s most powerful tools for testing multiple conditions and returning values. But for new users, wrapping your head around the syntax and uses cases can be tricky.

In this comprehensive guide, I’ll explain everything you need to know to start using IFS formulas like a pro. You’ll learn:

- What the IFS function is and why it’s helpful
- The syntax and arguments required to build IFS formulas
- Examples of common use cases and scenarios
- How to troubleshoot errors and audit your IFS formulas
- Creative ways to use IFS for advanced conditional logic
- When to use IFS vs. nested IF statements

So if you’re struggling to figure out this function read on! By the end you’ll have the skills to use IFS to simplify your worksheet logic.

## What is the IFS Function in Excel?

The IFS function allows you to test multiple conditions and return values based on the first TRUE result.

You can check up to 127 conditions in a single formula, making it perfect for scenarios that require lengthy nested IF statements.

For example, here’s a basic IFS formula:

`=IFS(A1>100, "A", A1>90, "B", A1>80, "C")`

This checks if cell A1 is greater than 100, 90, or 80, returning the letter grade (“A”, “B”, “C”) for the first TRUE condition.

The key benefit is simplifying complex nested IFs Without IFS, you’d need

`=IF(A1>100,"A",IF(A1>90,"B",IF(A1>80,"C")))`

As you add more conditions, this gets very hard to manage. IFS makes it much cleaner and easier to read.

Now let’s look at the syntax structure.

## IFS Function Syntax and Arguments

The syntax for IFS looks like:

`=IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...)`

It accepts the following arguments:

**logical_test1**(required): The first condition that returns TRUE or FALSE**value_if_true1**(required): The value to return if logical_test1 is TRUE**logical_test2, value_if_true2, etc.**(optional): Up to 127 additional test/value pairs

The logical tests can use any valid Excel comparison operators or functions that output TRUE/FALSE.

For the value returns, you can input numbers, text, cell references, or formulas.

Let’s break down a real example:

`=IFS(A1>80,"Pass",AND(A1>60,A1<=80),"Marginal Pass",A1<=60,"Fail")`

This checks three conditions:

- If A1 is greater than 80, return “Pass”
- If A1 is between 60 and 80, return “Marginal Pass”
- If A1 is less than or equal to 60, return “Fail”

The AND statement counts as a single logical test. This formula returns the first matching value, stopping after a TRUE.

Now that you understand the basic syntax, let’s look at some common use cases.

## Using IFS to Return Values from a List

One of the most common IFS examples is returning values from a list based on a lookup value.

For example, you could lookup student names based on their ID number:

![IFS return list example][]

The formula in cell D2 is: ` =IFS(C2=1,A2,C2=2,A3,C2=3,A4,C2=4,A5,C2=5,A6)`

If C2 equals 1, return the value in A2. If C2 equals 2, return A3, and so on.

You can drag the formula down to return all matching names.

This is useful any time you want to return data from a table based on an input value.

## Grading Test Scores with IFS

Grading exams or assignments is another common IFS scenario.

For example, you could grade scores based on percentage thresholds:![IFS grade scores example][]

The formula in B2 is:` =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")`

If the score is >= 90%, return “A”. >= 80% returns “B”, etc. The TRUE condition catches any score below 60%.

You can also use BETWEEN to simplify the logic:`=IFS(A2>=90,"A",BETWEEN(A2,80,89),"B",BETWEEN(A2,70,79),"C",BETWEEN(A2,60,69),"D",TRUE,"F")`

Either approach lets you easily assign letter grades.

## Applying Bonuses or Commissions with IFS

Using IFS to calculate payouts and commissions is another useful trick.

For example, you could give sales bonuses based on amounts sold:![IFS sales bonus example][]

The formula in B2 is:` =IFS(A2>100000,1000,A2>75000,750,A2>50000,500,A2>25000,250,TRUE,0)`

This returns a $1,000 bonus for sales over $100k, $750 for sales over $75k, etc. The TRUE condition gives $0 if no bonus applies.

You can use a similar structure to assign commissions, reimbursements, or any calculations based on tiered thresholds.

## Troubleshooting Errors in IFS Formulas

After building an IFS formula, it’s smart to audit your work to catch errors. Here are some common mistakes:

**Wrong logic order**– IFS returns the first TRUE, so test highest values first. Put the most specific cases before general cases.**Incorrect syntax**– Missing commas between arguments or incorrect logical operators lead to #VALUE! errors. Double check syntax.**FALSE logic**– If no tests return TRUE, you’ll get #N/A errors. Add a TRUE condition as a final catch-all.**Circular references**– If an IFS references its own cell, you’ll see a circular reference warning. Audit cell references.**Too many arguments**– Exceeding 127 test/value pairs results in #ARG! errors. Split into multiple IFS formulas if needed.**Testing non-logicals**– If a test resolves to non-logical values like text, you’ll get #VALUE! errors. Stick to logical outputs.

Auditing your formulas with F2 or the Evaluate Formula tool helps catch issues. Trace precedents to find the source of any unexpected results.

## Creative Ways to Use IFS for Advanced Logic

Once you master the basics, IFS can handle very advanced conditional logic:

- Use
**nested IFS**to test multiple sets of criteria - Compare
**across worksheets**by referencing other sheet names - Input entire
**Arrays**for logical tests - Combine with
**MATCH**to do lookups based on multiple criteria - Use with
**VLOOKUP**and**HLOOKUP**in creative ways - Nest
**IFERROR**to handle errors within IFS

For example, here’s a nested IFS formula that first checks the Day, then assigns special cases:` =IFS(WEEKDAY(A2)=1,"Weekend",WEEKDAY(A2)=7,"Weekend",A2="7/4","Holiday",TRUE,"Weekday")`

This returns “Weekend” if it’s Saturday or Sunday, “Holiday” if it’s July 4th, else “Weekday”.

The possibilities are endless! Try combining IFS with arrays, tables, and other Excel tools that support logical outputs.

## IFS vs. Nested IF Statements

The main reason to use IFS is simplifying nested IF formulas.

For example, this assigns letter grades with nested IFs:`=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F")))) `

The IFS version is much cleaner:` =IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F")`

So when choosing IFS vs nested IFs, use these guidelines:

## Tutorials Tutorials filter input

W3Schools offers a wide range of services and products for beginners and professionals, helping millions of people everyday to learn and master new skills.

## How to use the IFS function in Microsoft Excel

## FAQ

**How do I use multiple IFS conditions in Excel?**

**What is the formula for if then else in Excel?**

**Why wont IFS work in Excel?**

**What is IFS function in Excel?**

The IFS function in Excel is meant to test more than one criterion. If the first criteria passes the logical test; the IFS function returns the value_if_true and the formula stops. However, if the first criteria, it then tests the next logical criteria and so on.

**What does the IFS function return?**

The IFS function will return the value next to the first condition that evaluates to TRUE. value1, value2, …, value_n: These values are returned based on the conditions. Each value corresponds to a condition listed. The IFS function returns the value next to the first condition that evaluates to TRUE.

**What is if function in Excel?**

If you’re familiar with using the IF function in Excel, then you might be ready to check out the IFS function. With it, you can test multiple conditions at once, instead of using nested IF statements. Using IFS, you can test up to 127 conditions in a single Excel formula.

**When should I use the IFS function?**

You can use the IFS function when you want a self-contained formula to test multiple conditions at the same time without nesting multiple IF statements. Formulas based on IFS are shorter and easier to read and write. Conditions are provided to the IFS function as test/value pairs, and IFS can handle up to 127 conditions.