How to Use Multiple IF Statements in Excel Data Validation (3 Examples)

What Are Multiple IF Statements in Excel?

In Excel, when a formula contains more than one IF statement nested within another IF statement, it creates what we call “multiple IF statements.” This technique is also known as the Nested IF Function. These multiple IF statements allow you to evaluate multiple conditions simultaneously and return corresponding values based on those conditions.

Dataset Overview

We’ll work with a sample dataset containing country names from three continents: Asia, Africa, and Europe.

Dataset for Multiple IF Statements in Excel Data Validation

Example 1 – Using 2 IF Statements for Data Validation

Select the cell range B5:B9 and name it Asia using the Name Box.

Named Range for Using 2 IF Statements

Similarly, name the cell ranges C5:C9 and D5:D9 as Africa and Europe, respectively.
Create another table where we’ll insert the data validation based on the continent and country names.

Output Table for 2 IF Statements

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.
Set the source to the cell range B4:D4:

=$B$4:$D$4

Press OK.

Applying Data Validation settings

Similarly, for cell C12, insert this formula in the Source box:

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

Press OK.

Using 2 IF Statements in Data Validation

You can now choose any continent name from the list in cell C11.

The first output of Data Validation

The respective country names will appear in the following list.

The second output of Data Validation

Note: We used named ranges to simplify the formula, but you can provide cell ranges directly, if needed.

Example 2 – Data Validation with 3 IF Statements

Insert a new column beside the dataset and name it NorthAmerica.

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

Apply data validation for the titles as before (using cell C11).

Data Validation settings for Header cells

Enter the following 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)))

Data Validation settings for 3 IF Statements in Excel

Press OK.
You’ll get a data validation list for each continent.

Selecting the first output from Data Validation in Excel

Choose the country of the respective continent based on the IF statements.

Selecting the second output from Data Validation in Excel

Similar Readings

Example 3 – Applying 4 IF Statements to Excel Data Validation

Apply the same settings for cell C11 in the Data Validation window.

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

Insert 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))))

Multiple IF Statements in Excel Data Validation

You’ll get a drop-down list based on 4 IF statements.

The final output of 4 IF Statements to Excel Data Validation

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

The INDIRECT function is a powerful alternative to the IF function in Data Validation. Even when no specific value needs to be looked up, the combination of these two functions produces excellent results.

Scenario:
- Suppose we have a dataset with country names from the Asia and Africa columns.
- Additionally, we’ve added a new column named World containing countries not present in the previous dataset.

Dataset for Combining IF & INDIRECT Functions

Start by applying the following setting for the Category in cell C11.

Data Validation settings for Category

In cell C12, enter the following formula in the Source box of the Data Validation window:

=INDIRECT($C$11)

Applying only the INDIRECT function

Here, we’re using the INDIRECT function as a substitute for the IF function. It returns a logical value based on the given cell reference (C11).