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.

Example 1 – Using 2 IF Statements for Data Validation

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

• 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.

• Select cell C11 and choose Data Validation from the Data tab.

• In the Data Validation window, choose List from the Allow box.
• Set the source to the cell range B4:D4:

• Press OK.

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

• Press OK.

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

• The respective country names will appear in the following list.

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

Example 2 – Data Validation with 3 IF Statements

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

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

• Enter the following formula in the Source box of the Data Validation window for cell C12:

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

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

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

Similar Readings

• Use IF Function with OR and AND Statement in Excel
• How to Use IF Statement with Yes or No in Excel (3 Examples)
• How to Use If Statement Based on Cell Color in Excel (3 Examples)
• Use IF Statement with Not Equal To Operator in Excel

Example 3 – Applying 4 IF Statements to Excel Data Validation

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

• Insert this formula in the Source box for cell C12:

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

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

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.

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

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

• You’ll get the same output for the data validation as before.

• If you haven’t selected any name from the Category list, enter this formula in cell C12:

• Despite a blank Category selection, you’ll still get country names based on the INDIRECT function.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Use Conditional Formatting If Statement Is Another Cell
• Excel IF Statement Between Two Numbers (4 Ideal Examples)
• Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
• How to Use Wildcard with If Statement in Excel (5 Methods)
• Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)