IFS() Function in Google Sheets is used to test multiple conditions and a great replacement of multiple if statements. It is akin to “IF…Then…Else IF…Then” condition statement.
You should use IFS() function if there are not more than ten conditions or when there are many exceptions that you cannot reasonably replace it with VLOOKUP(), SWITCH() or similar functions.
You can make a copy of all the examples below in Google Sheet.
IFS() Function has the following syntax:
=IFS(Condition1, Value1, [Condition2, Value2],…)
|Condition1||A test of condition that evaluates to Value1 if it turns out to be TRUE;|
|If FALSE, will move on to Condition2|
Condition1 can be boolean, a number, an array, or cells that refer to these, however, the result must evaluate to either TRUE or FALSE.
|Condition2||The second condition to be evaluated if Condition1 is False.|
|#N/A||#N/A error will be thrown if all conditions are FALSE.|
You can add up to 127 conditions and values.
Examples of How to Use IFS() Function in Google Sheets
Use IFS between numbers
This example uses the grading in university to compare scores against actual grade.
Notice that whenever the formula refers to the reference table in blue, dollar signs are used to refer to a fixed cell position. This is important to keep the formulas valid as you copy it to other cells.
Also notice the last condition is an arbitrary TRUE. This forces a catch-all condition when the score is beyond 85. Conditions and values should always be in pairs for the function to work correctly, and hence the “TRUE” as a condition.
Use IFS to compare text
To compare text, use the following logical operators:
|Not equal to||<>|
|Exact case comparison||Exact(string1, string2)|
|Pattern matching / fuzzy match||Search(search_for, text_to_search, [starting at])|
Suppose a store is open Monday to Friday. To determine whether certain day of the week is an “Open” or “Close” day, you can test each weekday in turn.
The formula for the green cell next to Tuesday is:
This may look daunting at first, but it simply compares cell B34 (“Tuesday”) against the table in turn from Monday to Friday, with a catch-all condition that returns “Close”. You can of course change “Close” wording in the formula to a cell reference.
Pattern Matching Example
Here the example is to match a related company name against the official name of a company. This is not a good use of IFS() function since you can have many variations of a company name that could be longer or shorter than the official version, but serves to show how IFS() can be used for pattern matching.
The formula in the green cell is
=IFS( IFERROR(Search(B41,$B$38),FALSE),”Same Company”,TRUE,”Different”)
You would normally wrap Search() function with IFERROR() function just in case it cannot find the pattern in matching string. Note here that if Search() function returns #N/A error, the IFERROR() function will evaluate the whole formula to FALSE, which sends IFS() function to evaluate its second condition. Now since the second condition is an arbitrary TRUE, it will give the word “Different”.
For the use of Search() function and more detailed fuzzy match examples, see Excel Fuzzy Match
Use IFS to compare dates
Just like using IFS() function to compare numbers, you need to order the dates properly in the formula for it to work.
Here the formula in the first green cell is
=IFS(B50<datevalue(“1/4/2021″),”Off-Peak”,B50<=datevalue(“31/5/2021″),”Peak”,B50<datevalue(“25/9/2021”), “Off-Peak”,B50<=datevalue(“31/10/2021”), “Peak”, True, “Off-Peak”)
Datevalue() function allows you to turn a date string into a date that Google Sheets recognise. You can also use Date(year, month, date) function to the same effect.
The formula checks if a date is before April, and if so, it is off-peak. If not, it checks if the date is between April and May; if so, it is peak season and so on for other periods.
Use Google Sheets IFS Returns No Match
If IFS() function does not find a match, it will return a #N/A! error. This can be solved by including in your last condition-value pair the argument “TRUE” and a final catch-all value.
For instance, in the first example for Use IFS Between Numbers, if “TRUE,$C$14” condition-value pair is not included, it will show #N/A! for score 90, as opposed to “High Distinction”.
Instead of TRUE as the last argument, you can also put an IF() function as long as you specify true condition to be TRUE, as such:
=IF(catch-all condition, TRUE)
Here you do not have to specify a FALSE condition since the condition testing should be a catch-all condition.
You would only use IF() statement to replace the last TRUE if you wish to elaborate on what the condition is.
Now that you know how to use Google Sheets IFS() function, let us dive deeper and see how IFS() function works with AND() and OR() logic in Google Sheets.
Use IFS With And() Function For Condition Testing
In the example “Use IFS to compare text” above in Google Sheets, the IFS() formula written need to check if a date is a weekday one by one, making the formula hard to read.
If you have the majority of cases that evaluate to the same answer and only a handful of cases to test for otherwise-condition, you can consider combing IFS() function with AND() function, as they work well together when you have multiple conditions to exclude.
Using the same Shop Open Hours example:
Here we only need to test if a day is not a weekend. However, you cannot do this directly without AND() function. So in AND(), you check that the date is neither a Saturday nor a Sunday with the “Not equal to” (<>) sign.
You do not need to limit yourself with just two arguments in AND().
Use IFS With OR() Function For Condition Testing
In the following fictional example, assume that you are going to buy or sell gold at a price level, and that you would only do so if the price level for one of the two currencies that you hold meets your criteria.
The formula in the green cell is
=IFS(OR(B77<=$B$74,B78<=$C$74),”Buy”, OR(B77<$B$75,B78<$C$75),”Hold”, TRUE, “Sell”)
Here, the OR() function would evaluate to TRUE if any one of the arguments in OR() evaluates to TRUE. In contrast, AND() function would evaluate to TRUE only if all conditions evaluate to TRUE.
When Not To Use the Function
There is no strict rules saying you should definitely not use IFS() Function in other situations. However, it helps to know the merits of other functions that can achieve similar results.
For example, if you are testing a cell against simple discreet cases, you can use a SWITCH() function, which comes with an argument for default value. SWITCH() function evaluates a cell to see what value it has. It can therefore save you some time writing condition expressions that involve “=”, “<>” and other logical operators.
Also, when you need to determine which bracket a number falls in, a VLOOKUP() function or LOOKUP() function could be a better friend to get along with.
Here are two related posts:
You can use IFS() Function to replace simple IF() function or multiple IF() statements in Google Sheets. The important thing is to know what data type you are comparing with, and know when you need to order numbers or dates to achieve the result.