How to use NESTED IF in Excel ? – with Examples

Table of Contents

INTRODUCTION

A few functions are used extensively when we work in any spreadsheet application like Excel or google Sheets. In this article, we’ll learn to use NESTED IF in Excel.

Of these functions, IF function is the one which we need to use over and over again to put conditions to our data. But what if we need to apply multiple conditions?

Well, in that case, we can nest our IF functions to get the double or triple conditions handling.

IF FUNCTION is one of the very important functions and allows us to make the choices.

Normal IF Function in Excel

Before discussing Nested If, let us revise IF Function in Excel.

IF FUNCTION in Excel can be used quite easily.

IF FUNCTION IS USED TO DECIDE THE OUTCOME ON THE BASIS OF A GIVEN CONDITION.

We can use IF FUNCTION in the following way.

=IF(CONDITION CHECK, VALUE/RESULT IF THE CONDITION IS TRUE, VALUE/RESULT IF THE CONDITION IS FALSE)

For example, if we want to check if the value contained in cell A1 is greater than 10 or not; we’ll make use of the following formula.

=(A1>10,TRUE,FALSE)

If A1=5, the result will be FALSE.

If A1=15, the result will be TRUE.

We can also display any other values in place of TRUE or FALSE.

For example, if we want to display “Yes” in place of TRUE and “No” in place of FALSE, the formula will become

=(A1>10,”Yes”,”No”)

This is the way to use IF FUNCTION in Excel. Let us now complicate this a bit further and learn to use nested IF Function.

WHAT IS NESTING A FUNCTION?

When we use a function within a function, it is known as NESTING A FUNCTION.

In the previous section, we learnt the way to use IF FUNCTION in the standard way. You must have noticed that we can put only one condition using the standard IF FUNCTION.

What if we want to use two conditions and display or find out the different outcomes of the three conditions?

In such cases, we need to nest a function.

How to nest IF FUNCTION in Excel?

We can nest a function using the following way.

=IF( FIRST CONDITION, Value/Result if the first condition is true, IF( SECOND CONDITION, Value if the Second condition is True, Value if Second condition is false/ Value if third or remaining condition is true))

The above shown generalized formula is used to test three different conditions using the NESTED IF function. The same can be extended up to many variables.

EXAMPLE 1: Check if cell H10 contains 1,2 or 3 and display ONE, TWO or THREE in words in cell I10

This kind of cases can’t be dealt easily in Excel but we can make use of nested functions to get the things done.

Let us try to solve the given situations using Nested IF.

In this example, we need to deal with 3 different conditions for which we’ll be needing to use the nested if function in Excel.

Enter the following formula in cell I10.

=IF(H10=1,”ONE”,IF(H10=2,”TWO”,”THREE”)) as shown in the picture below.

FORMULA USED

This formula compares the first condition as H10=1 and put the result as ONE if this condition is true else it’ll go to step 2 where it finds the nested if function which again checks if H10=2 and results in TWO.

If H10 is not equal to 2, the control goes to the next step and shows the result as THREE.

Let us try the solution.

The following animated picture shows the usage of the said example.

RESULT

The picture above shows the usage of the formula.

When we enter 1,2 or 3 in cell H10, the text ONE, TWO or THREE appears in cell I10 as we intended.

EXAMPLE 2: Check if a number in A1 is less than, equal or greater than a number in A2 and show the status in cell A3

You can use a nested IF function in Excel to check if a number is “less,” “equal,” or “greater” compared to a specific value.

In our example, let us put a formula in the cell A3 where status is needed to be displayed.

The formula will be

=IF(A1 < A2, “Lesser”, IF(A1 = A2, “Equal”, “Greater”))

Nested if in excel
Nested If example in Excel

Explanation:

  1. First IF statement: Checks if A1 is less than A2. If true, it returns “Lesser”.
  2. Nested IF statement: If the first condition is false, it checks if A1 is equal to A2. If true, it returns “Equal”.
  3. If both conditions are false (i.e., A1 is greater than A2), it returns “Greater”.

Any number can be put in cell A2.