IFS function

What you will build

Classify orders into three amount bands without stacking many IF expressions. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Current-audit Google Sheets table showing the complete sample dataset in the IFS result workbook.

Open the sample workbook in Google Drive (access required) ↗

Work through the example

Step 1: Enter the IFS formula in H2 in the Formula result area; keep the complete A1:F6 source table visible for comparison.

Step 2: Evaluate the thresholds from highest to lowest. With E2 = 120, the first two tests are false, so the TRUE fallback returns Low.

Formula to try

=IFS(E2>=300,"High",E2>=150,"Medium",TRUE,"Low")

Current-audit Google Sheets view with H2 selected, the IFS formula visible, and the source table available for comparison.

How this formula works=IFS(E2>=300,"High",E2>=150,"Medium",TRUE,"Low")

Overall meaning of the whole formula
- Classify E2 by thresholds: 300 or more is "High", 150 to under 300 is "Medium", and everything else is "Low".
IFS(condition1, value1, [condition2, value2, ...])
- `condition1` is required; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `E2>=300`.
- `value1` is required; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"High"`.
- `condition2` is optional; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `E2>=150`.
- `value2` is optional; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"Medium"`.
- `condition3` is optional; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `TRUE`.
- `value3` is optional; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"Low"`.
Whole-expression evaluation
- IFS checks condition/result pairs from left to right.
- E2>=300 returns High for amounts of 300 or more.
- E2>=150 returns Medium for amounts from 150 up to below 300 because the >=300 branch is tested first.
- TRUE is the fallback branch, so all remaining amounts return Low.
Why the displayed final result appears
- TRUE is the fallback branch, so all remaining amounts return Low.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Amount	Expected IFS result
120	Low
240	Medium
80	Low
320	High
160	Medium

Verified live in the _result workbook by changing E2 and restoring the sample: 120→Low, 240→Medium, 80→Low, 320→High, 160→Medium.

Common mistake and correction

Correction: select H2, confirm the full formula in the formula bar, and compare its E2 reference with the Amount value in the same row before editing any source value.

Independent practice

After following the example, complete this variation without editing the source data: Raise the High threshold and inspect which rows move to Medium.

Key takeaways

✓IFS evaluates condition/result pairs from left to right and returns the first matching result.
✓Order thresholds from highest to lowest when broader lower thresholds would otherwise match first.
✓In this sample, amounts >=300 return High, amounts >=150 and below 300 return Medium, and the TRUE fallback returns Low.
✓Keep the source Amount in column E aligned with the row referenced by the formula in H2.
✓When troubleshooting, inspect the selected result cell and the full formula bar before changing the dataset.

Continue learning

Previous lesson: IF function; Next lesson: IFERROR function.

What you will build

Classify orders into three amount bands without stacking many IF expressions. This lesson uses fictional sample data so you can reproduce each action.

Learning objective

Complete the steps using the dataset below, explain why the feature or formula is used, and recognize issues when inputs change.

Prepare the sample data

Paste this example as tab-separated values beginning at A1 on a new worksheet:

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Open the sample workbook in Google Drive (access required) ↗

Work through the example

Step 1: Enter the IFS formula in H2 in the Formula result area; keep the complete A1:F6 source table visible for comparison.

Step 2: Evaluate the thresholds from highest to lowest. With E2 = 120, the first two tests are false, so the TRUE fallback returns Low.

Formula to try

=IFS(E2>=300,"High",E2>=150,"Medium",TRUE,"Low")

How this formula works=IFS(E2>=300,"High",E2>=150,"Medium",TRUE,"Low")

Overall meaning of the whole formula
- Classify E2 by thresholds: 300 or more is "High", 150 to under 300 is "Medium", and everything else is "Low".
IFS(condition1, value1, [condition2, value2, ...])
- `condition1` is required; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `E2>=300`.
- `value1` is required; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"High"`.
- `condition2` is optional; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `E2>=150`.
- `value2` is optional; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"Medium"`.
- `condition3` is optional; it accepts an expression evaluated as TRUE or FALSE. In this lesson the argument passed is `TRUE`.
- `value3` is optional; it accepts the value returned when the preceding condition is the first TRUE condition. In this lesson the argument passed is `"Low"`.
Whole-expression evaluation
- IFS checks condition/result pairs from left to right.
- E2>=300 returns High for amounts of 300 or more.
- E2>=150 returns Medium for amounts from 150 up to below 300 because the >=300 branch is tested first.
- TRUE is the fallback branch, so all remaining amounts return Low.
Why the displayed final result appears
- TRUE is the fallback branch, so all remaining amounts return Low.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

Amount	Expected IFS result
120	Low
240	Medium
80	Low
320	High
160	Medium

Verified live in the _result workbook by changing E2 and restoring the sample: 120→Low, 240→Medium, 80→Low, 320→High, 160→Medium.

Common mistake and correction

Correction: select H2, confirm the full formula in the formula bar, and compare its E2 reference with the Amount value in the same row before editing any source value.

Independent practice

After following the example, complete this variation without editing the source data: Raise the High threshold and inspect which rows move to Medium.

Key takeaways

✓IFS evaluates condition/result pairs from left to right and returns the first matching result.
✓Order thresholds from highest to lowest when broader lower thresholds would otherwise match first.
✓In this sample, amounts >=300 return High, amounts >=150 and below 300 return Medium, and the TRUE fallback returns Low.
✓Keep the source Amount in column E aligned with the row referenced by the formula in H2.
✓When troubleshooting, inspect the selected result cell and the full formula bar before changing the dataset.

Continue learning

Previous lesson: IF function; Next lesson: IFERROR function.

IFS function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

IFS function

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Work through the example

Formula to try

Checkpoint

Common mistake and correction

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments