EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Multiple IFS in Excel

Multiple IFS in Excel

By Madhuri ThakurMadhuri Thakur

Multiple IFS in Excel

Multiple IFS in Excel

The Multiple IFS function in Excel is a powerful and time-saving tool for checking multiple conditions in a single formula. It tests each condition in order and stops when it finds the true condition. Once it finds the true condition, it gives you the result that is associated with that condition.

What’s really great about this function is that it can check many conditions and give you a different value for each condition. This is different from the regular IF function, which can only check one condition and give you one of two results.

You can include up to 127 pairs of conditions and values in the Multiple IFS function. It’s compatible with Excel 2016 and later versions, including Excel for Microsoft 365.

Arguments

  • Logical _test: This is a logical expression that can be either “TRUE” or “FALSE.”
  • Value_if_true: This will return the value as TRUE if the specified condition is in sync.
  • Value_if_false: This will return the value as FALSE if the specified condition is not met.

For example, the formula =IF(A2=2, “TRUE,” “FALSE”), checks if A2 is equal to 2. If it is, the formula will show “TRUE“. If A2 is not equal to 2, the formula will show “FALSE“.

In Excel, we can find the IF function categorized under the LOGICAL condition group in the FORMULAS menu, as shown in the screenshot below.

Multiple IFS in Excel 1

How to Use IF Function in excel?

Multiple IFS in Excel is very simple and easy. Let’s understand Multiple IFS in Excel, which are as below.

You can download this Multiple IFS Excel Template here – Multiple IFS Excel Template

Example #1 – Using IF Function in Excel

Consider the following table where students’ marks with the subject are shown:

Multiple IFS in Excel - Example #1

Here, we will use the IF condition to find out the student’s PASS or FAIL status by following the below steps.

  • First, select cell E2. We want to display PASS or FAIL results in this cell.
  • Enter the IF function – =IF(D2>C2, “PASS”, “FAIL”). Here, the IF function compares students’ marks in cell D2 with the passing marks in cell C2.

IF condition to find out the student's PASS or FAIL status

  • Apply the formula and press enter to get the output as follows.

Result:
Since James’ marks (20) are less than the passing marks (100), the IF function returns the result as  “FAIL“.

IF condition to find out the student's PASS or FAIL status-Result

Example #2 – Multiple IFS in Excel with TEXT

We will learn how to use the multiple IF function using a simple example. Consider the table below where we have an Online Bus Ticket Booking System, and we need to know the booking status of all the seats. We can derive the output using the Multiple IFS function in such cases.

Example 2-1

To calculate the status for each booking using the IF function in Excel, follow these steps:

  • Select the cell where it is necessary to display the status (in this case, E4).
  • Type the equal sign (=) to begin the formula.
  • Type the IF function and an opening bracket.
  • Select the cell that contains the total seats (in this case, C4).
  • Add the first condition: C4=D4 (total seats equal to sold).
  • Type a comma.
  • In double quotes, type “BUS BOOKED” to indicate a confirmed booking.
  • Type another comma.
  • Add the second condition: C4<D4 (total seats are less than seats sold).
  • Type a comma.
  • In double quotes, type “OVERBOOKED” to indicate more bookings than available seats.
  • Type another comma.
  • Then type “SPACE AVAILABLE” to show seats available for booking.
  • Type the closing bracket for the IF function.
  • Press Enter to apply the formula to the selected cell.
  • Hence, the formula should look like this: =IF(C5=D5, “BUS BOOKED,” IF(C5<D5, “OVERBOOKED,” “SPACE AVAILABLE”))

Multiple IFS Example 2-3

  • Once we apply the Multiple IFS, we will get the below output status:

Multiple IFS Example 2-4

  • In the example given, the IF function checks whether the value in cell A1 is less than 390. If the value exceeds or equals 390, the function returns the status as “OVERBOOKED” or “BUS BOOKED“, respectively. However, if the value is less than 390, the function returns “SPACE AVAILABLE” as the status. This function is useful for analyzing data and deciding based on specific conditions.
  • Drag down the formula for all the cells so that we will get the below output result:

Result:

Example 2-5

Example #3 – Multiple IFS Using Numeric Value

In this example, we will see how multiple IFS use numeric values to display the status.

Consider the below example that shows MRP and SELLING PRICE, where we need to find out the closing FEE for Amazon titles. Here, we will use the Multiple IFS to get the CLOSING FEE for both the MRP AND SELLING PRICE by following the below steps:

Example 2-6

  • Here’s how we will apply the Multiple IF condition statement for MRP Closing Fee:
      • Check if the MRP is less than $250. If it is, set the Closing Fee as Zero.
      • The MRP is greater than or equal to $250, then check if it is less than 500 and if it is, set the closing fee as five (5).
      • If the MRP is greater than or equal to 500, set the closing fee as ten (10).
  • Here’s how we will apply the Multiple IF condition for the SELLING PRICE Closing Fee:
      • Check if the Selling Price is less than $250 and if it is, the Closing Fee should be zero.
      • If the Selling Price is between $250 and $499, the Closing Fee should be $5
      • If the Selling Price is $500 or greater, the Closing Fee should be $10.
  • We will apply the above two conditions by using the Multiple IFS in both columns.
  • First, insert the IF statement in F3.
  • Begin by typing an opening bracket and selecting cell D3.
  • Apply the condition: “D3<250” (indicating that the MRP is less than $250). Then, display the Closing Fee as zero and insert a comma.
  • Next, insert another IF condition and open the brackets. Type the condition “D3<500” (indicating that the MRP is between $250 and $499).
  • If this condition is met, display the Closing Fee as 5, and if not, display it as 10.
  • Lastly, if we combine the above IF conditions, we will get the Multiple IFS statement shown as:

Example 2-7

  • Once we apply the Multiple IFS, we will get the below output status:

Multiple IFS Example 2-8

The screenshot above shows that the closing fee for MRP is 10.

How did we arrive at this value?

  • First, Excel evaluated the first IF condition, which checks if 500 is less than 250. Since this is false, the condition does not apply, and Excel moves on to the next IF condition.
  • The second IF condition checks if the MRP value is less than 500. In this case, the MRP is 500, which is not less than 500, so Excel moves to the last part of the IF statement, which displays the closing fee as 10.

To calculate the closing fee for the Selling Price column, we can apply another Multiple IF statement as follows: =IF(E3<250,0,IF(E3<500,5,10)).

We can then drag this formula down to all cells in the column to obtain the corresponding closing fees

The resulting table is shown below.

Multiple IFS Example 2-9

Multiple IFS Example 2-10

Things to Remember

  • When we use a string in excel, Multiple IFS makes sure that we always enter the text in double quotes, or else the statement will throw the error as #NAME?
  • While using Multiple IFS, make sure that we have closed multiple opening and closing parenthesis, or else we will get the error alert stating that the formula applied is wrong.
  • One can include up to 127 pairs of conditions and values in the Multiple IFS function.
  • Conditions in the Multiple IFS function are evaluated in their order of listing. One must arrange the conditions in accordance with the order required. In case of multiple simultaneously true conditions, the first condition in the list will take precedence.
  • Multiple IFS function is only available in Excel 2016 and later versions, including Excel for Microsoft 365. In the case of an earlier version of Excel, one can use the nested IF function as an alternative.

Frequently Asked Questions(FAQs)

Q1. What is the syntax for multiple IFS functions in Excel?
Answer: The syntax for multiple IFS functions in Excel is:
=IFS(condition1,result1,condition2,result2,…,condition_n,result_n)

Q2. Can I use multiple IFS functions within a single formula in Excel? 
Answer: Yes, it is possible to use multiple IFS functions within a single formula in Excel to test for different conditions and return different results.
Example:
=IFS(A1<10, “Low”, A1<20, “Medium”, A1<30, “High”, A1<40, “Very High”)
In this example, the formula checks the value in cell A1 and gives different results based on the value. If A1 is less than 10, the formula returns “Low” and if A1 is between 10 and 19, it shows “Medium“. If A1 is between 20 and 29, it shows “High“. And if A1 is between 30 and 39, it returns “Very High“. The formula shows an empty cell if none of the conditions are true.

Q3. What is the difference between multiple IFS functions and nested IF functions in Excel?
Answer: The main difference between multiple IFS functions and nested IF functions in Excel is that multiple IFS functions allow you to test for multiple conditions in a single function, while nested IF functions only allow you to test for one condition at a time.
Example of a formula using the multiple IFS function:
=IFS(A1<10,”Low”,A1<20,”Medium”,A1<30,”High”,A1>=30,”Very High”)
Example of a formula using the nested IF function:
=IF(A1<10,”Low”,IF(A1<20,”Medium”,IF(A1<30,”High”,”Very High”)))

Recommended Articles

This article has been a guide to Multiple IFS in Excel. Here we discuss the usage of Multiple IFS in Excel, practical examples, and a downloadable excel template. You can also go through our other suggested articles:

  1. IFERROR with VLOOKUP in Excel
  2. Fill Handle in Excel
  3. Remove Duplicates in Excel
  4. Excel Data Validation
MICROSOFT POWER BI Training
48+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science
128+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All in One Data Science Bundle (360+ Courses, 50+ projects)
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course
 223+ Hours of HD Videos
35 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download Multiple IFS Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Multiple IFS Excel Template

EDUCBA

डाउनलोड Multiple IFS Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more