In this article:

How to Use Multiple IF Statements in Google Sheets (Best Way!)

May 8, 2024

hen 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.

  1. 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”.

Formula checks for missing data and selects medal winners
=IF(ISNUMBER(TIMEVALUE(B2)),IF(B2<E$2,"YES","NO"),"Invalid Data")
  1. 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.

  1. 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.

  1. 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. 

Complicated multiple IF statements are required to select the correct medal from 6 options based on elapsed time data
=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")))))))
  1. 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.

  1. 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.

  1. Insert a final logical test TRUE in multiple IF Functions with a return value. 
  2. 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

  1. 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.

 

Using the IFS function instead of multiple IF statements greatly simplifies the formula
=IFS(B2<E$4,F$4,B2<E$5,F$5,B2<E$6,F$6,B2<E$7,F$7,B2<E$8,F$8,B2<E$9,F$9,B2>E9,"NO MEDAL")
  1. 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

  1. Click on cell D2 and type: =IFS(B2<G2,I2,TRUE,"")

The IFS function compares actual inventory with minimum set quantities to compile a shopping list
=IFS(B2<G2,I2,TRUE,"")

  1. 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.

  1. 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")

  1. 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

🚀 Import data from anywhere and build custom tools and powerful dashboards straight from your spreadsheet. Discover what's possible in our Build Gallery.


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. 

If you want to learn how to send bulk email from Google Sheets, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->