How to Use Multiple IF Statements in Excel Data Validation

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.

Multiple IF Statements in Data Validation.xlsx

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.

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.

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.

Next, select Cell C11 and choose Data Validation from the Data 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.

=$B$4:$D$4

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

=IF($C$11="Asia",Asia,IF($C$11="Africa",Africa,Europe))

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

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

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

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.

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

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

=IF($C$11="Asia",Asia,IF(C11="Africa",Africa,IF(C11="Europe",Europe,NorthAmerica)))

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

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

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.

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

=IF($C$11="Asia",Asia,IF(C11="Africa",Africa,IF(C11="Europe",Europe,IF(C11="NorthAmerica",NorthAmerica))))

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

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.

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

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

=INDIRECT($C$11)

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.

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

=IF($C$11="",World,INDIRECT($C$11))

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.

Conclusion

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.

Get FREE Advanced Excel Exercises with Solutions!