How to Use Multiple IF Statements in Excel Data Validation

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, the IF function is one of the widely used ones for conditional calculation. In Excel Data Validation, we can even use the IF function more than one time based on the dataset. It, therefore, helps to create a dynamic drop-down list. In this article, we will learn through 3 suitable examples how to use multiple IF statements in Excel Data Validation.

Download Practice Workbook

Download this sample file to practice by yourself.

What Do Multiple IF Statements Mean in Excel?

  • In a formula with the IF function, when the condition contains more than one IF statement within another IF statement, it creates Multiple IF Statements in Excel. It is also called the Nested IF Function.
  • These are required to justify multiple conditions at a time and return respective values.
  • We can end the overall IF statement by determining the value_if_true and value_if_false arguments.
  • In Excel365, you can add up to 64 IF functions in a single formula.

3 Suitable Examples of Multiple IF Statements in Excel Data Validation

So far, we got a brief idea about the term multiple IF statements. Now, let us learn using it in Data Validation with some examples. For this, here is a sample dataset that contains countries names of 3 continents- Asia, Africa and Europe.

Dataset for Multiple IF Statements in Excel Data Validation

Following are 3 examples of using multiple IF statements in Excel Data Validation.

Example 1: Use 2 IF Statements in Excel Data Validation

In this first example, we will consider 2 IF statements for Data Validation. Let’s see how it works.

  • First, select the Cell range B5:B9 and name it Asia in the Name Box.

Named Range for Using 2 IF Statements

  • Similarly, name the Cell range C5:C9 and D5:D9 as Africa and Europe respectively.
  • Then, create another table where we will insert Data Validation based on Continent and Country names.

Output Table for 2 IF Statements

  • Next, select Cell C11 and choose Data Validation from the Data tab.

Selecting Data Validation from Home tab

  • In the Data Validation window, choose List from the Allow box.
  • Along with it, provide the Cell range B4:D4 as the Source and press OK.

Applying Data Validation settings

  • Similarly, open the Data Validation window for Cell C12 as well.
  • Here, insert this formula in the Source box.

Using 2 IF Statements in Data Validation

  • Lastly, press OK.
  • As a result, you can choose any Continent name from the list like this.

The first output of Data Validation

  • Accordingly, you will get the respective Country names in the following list.

The second output of Data Validation

Note: Here we worked with Named Ranges for making the formula easy. You can provide cell ranges instead of named ranges as well.

Read More: Dynamic Data Validation List in Excel with IF Statement Condition

Example 2: Data Validation with 3 IF Statements in Excel

We can imply Data Validation for 3 IF statements as well. For this, follow the steps below.

  • Initially, we inserted a new column beside the dataset and named it NorthAmerica.

Additional Named Range for Data Validation with 3 IF Statements in Excel

  • Now, apply Data Validation for the titles as before with the following settings in Cell C11.

Data Validation settings for Header cells

  • Then, apply this formula in the Source box of the Data Validation window for Cell C12.

Data Validation settings for 3 IF Statements in Excel

  • Lastly, hit on OK.
  • As a result, you will get the Data Validation list for each Continent as follows.

Selecting the first output from Data Validation in Excel

  • Therefore, you can choose the Country of that respective Continent from the following list based on the IF statements.

Selecting the second output from Data Validation in Excel

Read More: How to Check If Cell Contains One of Several Values in Excel

Similar Readings

Example 3: Apply 4 IF Statements to Excel Data Validation

We can also apply 4 IF statements in a single formula to generate Data Validation for our dataset. To do the task, follow the process below.

  • Initially, apply the following settings for Cell C11 in the Data Validation window like before.

Continent category settings for drop-down list in Excel Data Validation

  • Next, apply this formula in the Source box for Cell C12.

Multiple IF Statements in Excel Data Validation

  • Finally, you will be able to get the drop-down list based on 4 IF statements like this.

The final output of 4 IF Statements to Excel Data Validation

Read More: How to Prepare IF Statement Contains Multiple Words in Excel

How to Use INDIRECT Function in Excel Data Validation Instead of Multiple IF Statements

The INDIRECT function is a very effective tool that works instead of the IF function in Data Validation. Even when there is no signified value to lookup in Data Validation, the combination of these 2 functions returns an outstanding output. To illustrate the process, we have taken the values of Asia and Africa columns and added a new one named World. The World column has the countries’ names that are not present in the previous dataset that we used above.

Dataset for Combining IF & INDIRECT Functions

  • Initially, apply this setting for the Category in Cell C11.

Data Validation settings for Category

  • Then, apply this function in the Source box for Cell C12.

Applying only the INDIRECT function

Here, we used the INDIRECT function as a substitute for the IF function. It returns a logical value based on the given cell reference C11.
  • As a result, you will get the same output of Data Validation as follows.

The output of applying only the INDIRECT function

  • Now, if you haven’t selected any name from the list of the Category then imply this formula for Cell C12.

 Combining IF & INDIRECT Functions

Here, the IF function states the condition where C11 is Blank (“”) and therefore it will return the names from the World list.

  • Finally, you will still get Country names despite of blank Category selection.

Results of Combining IF & INDIRECT Functions


Finally, we are at the conclusion of our article. Here we learned 3 suitable examples of how to use multiple IF statements in Excel Data Validation. Let us know your feedback on this. Follow ExcelDemy for more tutorials like this.

Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF