- Enter the IFS function. Click the cell where you want the function to return a value. …
- Create the first logical test. Once you enter the IFS function, =IFS( appears in the cell you selected. …
- Enter the first value if true. …
- Enter more logical tests.
By allowing you to enter up to 127 conditions, this function replaces the outdated practice of nesting multiple IF functions, making your formulas simple to read and comprehend. The only drawback of this function is that it does not allow you to specify an ELSE condition, but we do have a solution that we will demonstrate later in the tutorial.
How to use the IFS function in Excel
When to use the IFS function in Excel
Excel’s IFS function allows you to return values based on a variety of logical tests. Here are a couple of examples of practical uses:
Example 1
You want to organize student grades so that you can enter them in your grade book. Your grading scale is:
Considering this grading system, you choose to use an IFS function. The function evaluates your students’ numerical scores and returns the appropriate letter grade after you enter the logical test results and their corresponding values. For instance, a student who receives a score of 75 receives a C. The function returns an F letter grade for a numerical score of 50 because it determines that the first four conditions are false but the last one is true.
Example 2
A manager wants to assess his staff members’ hours to determine if they completed the required 40 for the week. The manager is not required to take any further action if an employee completes their 40 hours. Employees who work more than 40 hours are eligible for overtime, and those who work fewer than 40 hours must undergo a performance review to find out why they didn’t meet their hourly requirement. The manager develops an IFS function in Excel that verifies the conditions listed below:
What is the IFS function in Excel?
When a true condition is evaluated, the Excel formula’s IFS function evaluates one or more conditions and returns a value. The values that the function returns for each circumstance are selectable by users.
The function assesses the second condition if the first condition is false. It continues to evaluate the conditions sequentially if the second condition is false. The function returns the value that corresponds to the true condition once it has discovered one. 127 conditions, also referred to as logical tests, can be entered for the function to evaluate.
Excel introduced the IFS function in 2016. Before this function, people would evaluate a condition and return a specific value using the IF function. The IFS function is more practical when carrying out multiple logical tests because the IF function can also evaluate multiple conditions. The IFS function is simpler to read because it only requires conditions and their true values, as opposed to using multiple IF functions inside of a single IF function.
How to use the IFS function in Excel
Heres how to use the IFS function in Excel:
1. Enter the IFS function
To direct the function to return a value, click the desired cell. Type =IFS and choose IFS from the drop-down menu. Alternatively, you click the “Function” button in the toolbar. Browse the menu or use the search bar to look up the IFS function.
2. Create the first logical test
When you enter the IFS function, the cell you chose will display =IFS(. After the open parenthesis, enter the first logical test you want the function to run. Acceptable operators for your logical tests include:
The IFS function is frequently used to establish the connection between a value and a number. For instance, the first logical test would be A1>5 to see if the value of A1 is greater than five. The IFS function can also be used to establish a value’s relationship to a value in a different cell.
For instance, the first logical test would be A1>A2 to see if the value of A1 is greater than the value of A2. If you want to continue comparing other cell values to A2 when you copy the formula to other cells, fix A2 by typing A1>$A$2 instead of A1>A2.
3. Enter the first value if true
Your first logical test should be followed by a comma, and then type the value you want to show up if the condition is true. Type a number followed by a comma if you want the value to be a number. For instance, the beginning of your IFS function would resemble this if you wanted Excel to return a value of 10 if A1 was higher than five:
=IFS(A1>5, 10, )
Put the word or phrase in quotes and add the comma at the end of the quote if you want the value to be a word or phrase. For instance, the beginning of your IFS function would resemble this if you wanted Excel to return a value of “Yes” if A1 is greater than five:
=IFS(A1>5, “Yes”, )
4. Enter more logical tests
However, you can enter more by repeating steps two and three. The IFS function only requires one logical test and value if true. Make sure to separate each logical test and value, if true, with a comma. When finished, if true, add a closing parenthesis to the end of the final value.
Heres an example IFS function with multiple logical tests:
IFS=(A1>100, “Exceptional”, A1>90, “Good”, A1>80, “Satisfactory”, A1<=80, “Needs improvement”)
If A1 is greater than 100, the first condition instructs the function to produce a value of “Exceptional.” The function runs the other tests until it discovers a true condition if A1 is not equal to 100. For instance, the function would return “Satisfactory” if A1 contained the value 82. “.
Tips for using the IFS function in Excel
Here are some pointers for using Excel’s IFS function:
Copy the formula to other cells
The IFS function can be copied to additional cells in the same manner as other Excel formulas. Select the fill handle in the bottom right corner of the cell where the original IFS function appears by clicking on it, then dragging it to other cells. To send the formula to neighboring cells, you can also double-click the tiny green box in the bottom right corner of the cell.
Keep in mind that by doing this, the IFS function is now able to run logical tests in relation to its new location. Consider, for instance, that the original IFS function contained the following formula and was located in cell B1:
=IFS(A1>10, 5, A1<=10, 0)
When you copy the formula to B2, the formula becomes:
=IFS(A2>10, 5, A2<=10, 0)
Ensure at least one condition can be true
The function returns an “#N/A” error if it determines that none of the conditions are true. By making sure your conditions account for all scenarios, you can avoid these mistakes. For instance, imagine that you have this function:
=IFS(A1>100, “Yes”, A1<100, “No”)
If A1 is equal to 100, the function returns a “#N/A” error because 100 is neither greater than nor less than 100. This error can be avoided by including an additional condition to account for the number 100. The new formula looks like this:
=IFS(A1>100, “Yes”, A1<100, “No”, A1=100, “Maybe”)
Add TRUE as the final logical test
Additionally, by including TRUE as the last logical test, errors can be avoided. If none of the aforementioned conditions are true, this allows the function to return a value. For instance, consider this function:
=IFS(A1>150, “High”, A1<100, “Low”, TRUE, “OK”)
If A1 is greater than 150, the function returns “High,” and if A1 is less than 100, it returns “Low.” The function returns “OK” if A1 is neither greater than 150 nor less than 100. If A1 is a number between 100 and 150 or equal to 100 or 150, then these “OK” values are possible.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
Is ifs still in Excel?
…
For example:
- =VLOOKUP(A2,A10:C20,2,TRUE)
- =VLOOKUP(“Fontana”,B2:E7,2,FALSE)
- =VLOOKUP(A2,’Client Details’!A:F,3,FALSE)
What is the difference between if and IFS in Excel?
When a code is not recognized, IFS will return #N/A in the absence of this last requirement. Note: Excel 365 and Excel 2019 both include a new function called IFS.
How do you create an IF THEN formula in Excel?
In the case of the IFS function, only one expression will be evaluated based on the condition as opposed to both expressions when the IF function is used. Using the IFS function in the aforementioned mapping yields the same result. It is challenging to demonstrate how both expressions are evaluated for the if function using standard function.
