How to Use IF Statement in Data Validation Formula in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for the easiest ways to use the IF statement in data validation formula in Excel, then you will find this article useful. Data validation can be useful for making a dropdown list or entering only specified data in a range.
To know more about the usage of the IF statement in data validation formula, let’s start our main article.

Download Workbook


6 Ways to Use IF Statement in Data Validation Formula in Excel

Here, we have some records of some of the products and their corresponding salesperson’s names of a company. By using this dataset, we will try to demonstrate the ways to use the IF statement in the data validation formula in Excel.

Excel data validation formula if statement

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using IF Statement to Create a Conditional List with the Help of Data Validation Formula

For creating a conditional list, here we have arranged the full names of the employees under the header First Name and for the first name of the employees, we have used the header Full Name. Using the IF function in the data validation formula we will make the conditional list in the right-side table.

Excel data validation formula if statement

Steps:
➤ Select the range E3:E12 and then go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

conditional list

Then, the Data Validation dialog box will appear.
➤ Select the List option in the Allow box and write the following formula in the Source box and click OK.

=IF($E$3=$B$3,$C$3:$C$12,$B$3:$B$12)

Here, $E$3 is the cell where we want to select the header from the dropdown list, $B$3 is the header name of the first column. When these two values will be equal IF will return the list of the range $C$3:$C$12, otherwise the list will contain the values of the range $B$3:$B$12.

Excel data validation formula if statement

➤ So, when we click on the drop-down symbol of cell E3, we will get the header First Name and selected it.

conditional list

➤ Select the first names from the list of the first names of cell E4.

conditional list

In this way, we are getting the first names of the salesperson’s names along with the header First Name.

conditional list

➤ You can change the header name from First Name to Full Name in cell E3.

Excel data validation formula if statement

➤ Select the full names from the list for the rest of the cells.

conditional list

Finally, you will get the full names of the employees with the corresponding header.

conditional list

Read More: How to Make a Data Validation List from Table in Excel (3 Methods)


Method-2: Creating a Dependent Dropdown List by Using IF Statement in Data Validation Formula

In this section, we will create a dependent dropdown list where the Item list will be dependent on the Category list.

Excel data validation formula if statement

Steps:
➤ Select the cell E5, and then, go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

dependent dropdown list

After that, the Data Validation dialog box will appear.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=$B$3:$C$3

Here, $B$3 is the header Fruits and $C$3 is the header Vegetables.

➤ Press OK.

Excel data validation formula if statement

➤ After clicking the dropdown symbol of cell E5, you will get the header names on the list, and let’s select Fruits from this list.

dependent dropdown list

Now, we will make the items list in cell F5.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=IF($E$5="Fruits",$B$4:$B$12,$C$4:$C$12)

When the value of the cell $E$5 will be equal to “Fruits”, IF will return the range $B$4:$B$12 as a list otherwise the list will contain the range $C$4:$C$12.

➤ Press OK.

dependent dropdown list

Now, for selecting a fruit item like Apple, click on the dropdown list of cell F5 and select this item from the list.

dependent dropdown list

Then, you will get your desired item Apple for the category Fruits.

Excel data validation formula if statement

➤ You can select the Category as Vegetables also from the list.

dependent dropdown list

Then, you will get the list of vegetables in the item list and select the first one (anyone which you prefer) Cabbage from here.

dependent dropdown list

Finally, we are getting the Item Cabbage for the corresponding Category Vegetables.

dependent dropdown list

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


Method-3: Using IF Statement and Named Range in Data Validation Formula in Excel

Here, we will use the named ranges along with the IF function in the data validation formula to make a dropdown list.
We have named the range of fruits as Fruits and the range of vegetables as Vegetables.

Excel data validation formula if statement

Excel data validation formula if statement

Steps:
➤ Select the cell E5, and then, go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

Named Range

Afterward, the Data Validation dialog box will appear.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=$B$3:$C$3

Here, $B$3 is the header Fruits and $C$3 is the header Vegetables.

➤ Press OK.

Named Range

➤ Now, click on the dropdown symbol of cell E5, you will get the header names on the list, and select Fruits from this list.

Excel data validation formula if statement

Now, it’s time to make the items list in cell F5.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=IF($E$5="Fruits",Fruits,Vegetables)

When the value of the cell $E$5 will be equal to “Fruits”, IF will return the named range Fruits as a list otherwise the list will contain the named range Vegetables.

➤ Press OK.

Named Range

➤ Click on the dropdown list of cell F5, and select the item Apple from the list.

Named Range

Then, you will get your desired item Apple for the category Fruits.

Excel data validation formula if statement

➤ Select the item Cabbage from the list for the category Vegetables.

Named Range

Eventually, we are getting the Item Cabbage for the corresponding Category Vegetables.

Named Range

Read More: How to Use Named Range for Data Validation List with VBA in Excel


Similar Readings:


Method-4: Using the IF and INDIRECT Functions in Data Validation Formula in Excel

Here, we will be using the INDIRECT function along with the IF function to create a data validation formula. And we have the following named ranges Fruits and Vegetables for the fruits range and vegetables range respectively.

Excel data validation formula if statement

Excel data validation formula if statement

Steps:
➤ Select the cell F5, and then, go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

IF+INDIRECT Function

After that, the Data Validation dialog box will appear.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=IF($E$5="",Fruits,INDIRECT($E$5))

When the value of the cell $E$5 will be equal to Blank, IF will return the named range Fruits as a list otherwise INDIRECT($E$5) will check the value in the cell $E$5 and then link the value as a reference to the corresponding named range.

➤ Press OK.

IF+INDIRECT Function

➤ Here, we have a blank in cell E5, and for this blank, we are having the list of fruits in the dropdown list of cell F5, and then select the first one Apple from the list.

Excel data validation formula if statement

For the blank as a Category, we are having the Item as a fruit Apple.

IF+INDIRECT Function

Now, you can write down the Category as Vegetables, and then you will get the list of vegetables in cell F5.|
➤ Select Cabbage from the vegetable list of the Item column.

IF+INDIRECT Function

Eventually, we are getting the Item Cabbage for the corresponding Category Vegetables.

Excel data validation formula if statement

Read More: How to Create Excel Drop Down List for Data Validation (8 Ways)


Method-5: Using Nested IF Functions in Data Validation Formula

Here, we are going to use nested IF functions for multiple conditions in a Data Validation formula to create a dropdown list for the Fruits, Vegetables, and Fruits.

Excel data validation formula if statement

Steps:
➤ Select the cell E5, and then, go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

Nested IF functions

Then, the Data Validation dialog box will appear.
➤ Select the List option in the Allow box, and write the following formula in the Source box

=$B$3:$C$3

Here, $B$3 is the header Fruits and $C$3 is the header Vegetables.

➤ Press OK.

Excel data validation formula if statement

➤ Now, click on the dropdown symbol of cell E5, you will get the header names on the list, and select Fruits from this list.

Nested IF functions

We will make the items list in cell F5 now.
➤ Select the List option in the Allow box and write the following formula in the Source box

=IF($E$5="Fruits",$B$4:$B$12,IF($E$5="Vegetables",$C$4:$C$12,$D$4:$D$12))

When the value of the cell $E$5 will be equal to “Fruits”, IF will return the range $B$4:$B$12 as a list, otherwise it will go to the next IF function which will check for the value “Vegetables”.
If the condition of this function is fulfilled, then it will return the range $C$4:$C$12 as a list otherwise $D$4:$D$12 will be used in the list.

➤ Press OK.

Nested IF functions

➤ Click on the dropdown list of cell F5, and select the item Apple from the list.

Excel data validation formula if statement

Then, you will get your desired item Apple for the category Fruits.

Nested IF functions

➤ Select the item Cabbage from the list for the category Vegetables.

Nested IF functions

Then, you will have the Item Cabbage for the Category Vegetables.

Nested IF functions

For selecting the Category as Fish you will have the list of the fishes in cell F5 of the Item column.
➤ Select the first Item Salmon from the list or any other item.

Nested IF functions

Eventually, we are getting the Item Salmon for the Category Fish after selecting from the list.

Excel data validation formula if statement

Related Content: Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)


Method-6: Using IF Statement in Data Validation Formula for Dates

Here, we will try to restrict the entries for the dates of the Delivery Date column in a way that the cells of this column will only accept the dates previous to today’s date (3/21/2022 as m/dd/yyyy format), and for entering dates greater than today’s date we will get an error message. For this purpose, we will be using the TODAY function along with the IF function.

Excel data validation formula if statement

Steps:
➤ Select the range E4:E12, and then, go to the Data Tab >> Data Tools Group >> Data Validation Dropdown >> Data Validation Option.

Data validation for dates

Then, the Data Validation dialog box will appear.
➤ Select the Custom option in the Allow box, and write the following formula in the Source box

=IF($D$4:$D$12="*",$E$4:$E$12<=TODAY(),$E$4:$E$12="")

If the cells of the range $D$4:$D$12 contains any text string then the cells of the range $E$4:$E$12 will only allow the dates smaller than today’s date or 3/21/2022.

➤ Press OK.

Data validation for dates

We can enter any dates without any problem except for the dates greater than today’s date as we can see from the following figure.

Excel data validation formula if statement

But when we try to enter a date 3/28/2022 which is not either less than or equal to today’s date,

Data validation for dates

we are having the following error message due to the data validation formula we had set previously.

Data validation for dates

So, we have filled the cells of the Delivery Date column with dates less than today’s date.

Excel data validation formula if statement

Related Content: How to Use Data Validation in Excel with Color (4 Ways)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to cover the ways to use the IF statement in a Data Validation formula in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo