When you use multiple IF statements (functions) inside the same Google Sheets function they are called nested IF statements because each one is inside the next. Nesting enables multiple conditional checks in a single Google Sheets formula.
Using Multiple IF Statements in Google Sheets (Nested)
Syntax
IF(expression1, value_if_true1, IF(expression2, value_if_true2, IF(expression3, value_if_true3, value_if_false)))
Examples
You can see all of the examples presented below in our sample Google Sheets spreadsheet here.
Use nested IF statements (functions) in Google Sheets to identify which competitors in a marathon deserve a medal.
- Click on cell C2 and enter the formula: =IF(ISNUMBER(TIMEVALUE(B2)),IF(B2<E$2,"YES","NO"),"Invalid Data")
The first IF function tests the validity of Elapsed_Time data and returns “Invalid Data” if a value is out of range or the wrong format.
The second IF function checks whether the competitor deserves a medal and returns either “YES” or “NO”.
- Copy the formula from C2 down column C for each row of data.
A simple combination of 2 IF statements cannot award the appropriate medal for each competitor.
However it can identify medal winners and screen the data for validity.
- Sort the data by column C to group the records for further processing
Use a multiple IF statement in Google Sheets to select medal winners and allocate the correct one.
- Click on cell C2 and enter the formula:
=IF(B2<E$2, "Gold", IF(B2<E$3, "Silver", IF(B2<E$4, "Bronze/Silver", IF(B2<E$5, "Titanium", IF(B2<E$6, "Bronze", IF(B2<E$7, "Copper", IF(B2>=E$7, "NO MEDAL")))))))
For simplicity, valid data is assumed in this example.
Correct allocation from 6 different medals requires an IF statement for each possibility.
- Note that the final argument is returned only when none of the IF statements is true.
The formula checks each of multiple IF statements until a TRUE one is found.
- Be careful to apply correct Syntax because misplaced brackets or commas will cause errors.
The complexity of a multiple IF statement can easily result in undetected errors.
Avoiding “#N/A” When Using the IFS Function for Multiple IF Functions (Statements) in Google Sheets.
- Insert a final logical test TRUE in multiple IF Functions with a return value.
- If none of the previous conditions is true then this value is returned. e.g. =IFS(A1>150, "High", A1<100, "Low", TRUE, "OK") The value “OK” is returned if 100<=A1<=150
Multiple IF Statements Using the IFS Function in Google Sheets
It can be better to use the Google Sheets IFS function when you want to combine multiple IF statements. The syntax is simpler to apply which makes finding errors much easier.
You can read our full IFS Function tutorial by clicking here:
Syntax
=IFS(condition1, value1, [condition2, value2, …])
Examples
You can see all of the examples presented below in our sample spreadsheet here.
Use the IFS function to awards medals
- Click cell C2 and type: =IFS(B2<E$2,F$2,B2<E$3,F$3,B2<E$4,F$4,B2<E$5,F$5,B2<E$6,F$6,B2<E$7,F$7,B2>E$7,"NO MEDAL")
For simplicity valid data is assumed in this example.
The entire formula is enclosed between a single pair of brackets.
- The IFS function returns the same result and is far simpler.
Use the IFS function in Google Sheets to check inventory and create a shopping list
- Click on cell D2 and type: =IFS(B2<G2,I2,TRUE,"")
- Copy the formula down column D for each row of data.
The IFS function lists the quantities of only ingredients to be purchased for baking bread.
Avoiding “#N/A” When Using the IFS Function for Multiple IF Functions (Statements) in Google Sheets
The IFS function in Google Sheets yields an "#N/A" error if none of the conditions is true.
- Prevent these errors by setting conditions that account for all scenarios.
Say for instance you have this function: =IFS(A1>100, "Yes", A1<100, "No")
- Add an additional condition: =IFS(A1>100, "Yes", A1<100, "No", A1=100, "Undecided")
More info
Google Sheets does not limit the number of Nested IF functions, but in practice they become unmanageable long before the 4000 character limit per cell is reached.
Use multiple IF statements to sort data into several categories in Google Sheets in a single step.
Multiple IF statements appear deceptively simple to apply and are widely used in Google Sheets, but the risk of logic errors is proportional to the number of conditions being tested.
Be very careful to use the correct Syntax when Nesting IF statements in Google Sheets, because serious errors can occur and go undetected in large datasets
Supercharge Your Spreadsheets with Lido
Related IF Functions
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria
COUNTIFS: Count data that fulfils two or more criteria.
COUNT IF Not Null: Count cells if they contain data
IFERROR: Allows you to output values when an Error in a formula occurs
IF THEN: Allows you tor write statements that use IF X Then Y Logic
IF AND: Combines the functionality of the IF and AND functions
IF Else: Set conditions that give an output depending on whether a given condition is fulfilled or not
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Recommended articles
More resources
