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

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 demonstrate how to use the IF statement in the Data Validation formula in Excel.

Excel data validation formula if statement


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

For this method 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.
  • Go to the Data tab and the Data Tools group.
  • Select the Data Validation dropdown.
  • Choose the Data Validation option.

conditional list

Then, the Data Validation dialog box will appear.

  • Select the List option in the Allow box.
  • 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 and $B$3 is the header name of the first column. When these two values are equal, the IF function 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

  • When we click on the drop-down symbol of cell E3, we will get the header First Name.

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

You will get the full names of the employees with the corresponding header.

conditional list

Read More: How to Use Custom VLOOKUP Formula in Excel Data Validation


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 go to the Data tab.
  • In the Data Tools group, go to the Data Validation dropdown and select the Data Validation option.

dependent dropdown list

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.
  • Let’s select Fruits from this list.

dependent dropdown list

  • Select cell F5.
  • Choose Data Validation again as for the E5 cell.
  • 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

  • To select a fruit item like Apple, click on the dropdown list of cell F5 and select it from the list.

dependent dropdown list

You will get your desired item Apple for the category Fruits.

Excel data validation formula if statement

  • You can select the Category as Vegetables from the list.

dependent dropdown list

  • You will get the list of vegetables in the item list, where you can select one.

dependent dropdown list

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

dependent dropdown list

Read More: Data Validation Based on Another Cell in Excel


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

For this, 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.
  • Go to the Data tab and, in the Data Tools Group, select the Data Validation dropdown and choose the 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

  • Click on the dropdown symbol of cell E5 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 F5.
  • Go to the Data tab and, in the Data Tools Group, select the Data Validation dropdown and choose the Data Validation option.
  • 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 is 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 will get the Item Cabbage for the corresponding Category Vegetables.

Named Range


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

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.
  • Go to the Data tab and, in the Data Tools Group, select the Data Validation dropdown and choose the 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 is 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 have the list of fruits in the dropdown list of cell F5, and then select the first Apple from the list.

Excel data validation formula if statement

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

IF+INDIRECT Function

  • Write down Vegetables in E5 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

We will get the Item Cabbage for the corresponding Category Vegetables.

Excel data validation formula if statement


Method 5 – Using Nested IF Functions in Data Validation Formula

Let’s expand the dataset with Fish.

Excel data validation formula if statement

Steps:

  • Select the cell E5.
  • In the Data tab, go to the Data Tools Group, select the Data Validation dropdown, and choose the Data Validation option.

Nested IF functions

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:$D$3

Here, $B$3 is the header Fruits and $D$3 is the header Fish.

  • Press OK.

Excel data validation formula if statement

  • Click on the dropdown symbol of cell E5 and select Fruits from this list.

Nested IF functions

  • Select the cell F5.
  • In the Data tab, go to the Data Tools Group, select the Data Validation dropdown, and choose the Data Validation option.
  • 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 is 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

You will get 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 functionsSelect Fish as the category for E5, and then choose an option.

Nested IF functions

Here’s how you can get Salmon.

Excel data validation formula if statement

Read More: Apply Custom Data Validation for Multiple Criteria in Excel


Method 6 – Using IF Statement in Data Validation Formula for Dates

Let’s 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 preceding today’s date (in m/dd/yyyy format), and for entering dates greater than today’s date we will get an error message.

Excel data validation formula if statement

Steps:

  • Select the cell range E4:E12.
  • In the Data tab, go to the Data Tools Group, select the Data Validation dropdown, and choose the Data Validation option.

Data validation for dates

  • 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 contain any text string then the cells of the range $E$4:$E$12 will only allow the dates smaller than today’s date.

  • Press OK.

Data validation for dates

We can enter any date without any problem except for the dates greater than today’s date (written on 2/28/2022).

Excel data validation formula if statement

  • Try to enter a date that’s “in the future.”

Data validation for dates

Excel throws an error message due to the data validation formula set previously.

Data validation for dates

You can manually fill the cells of the Delivery Date column with dates preceding today.

Excel data validation formula if statement


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


Download Practice Workbook


Related Articles


<< Go Back to Excel Custom Data Validation | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

2 Comments
  1. im amazed to see if instead of xlookup.
    but i was looking how to force related selection after change accedently the first (fish/salmon –>fruit/salmon)

    • Hello Ano Victory,

      I’m glad you found it interesting! To force related selections when the first selection is changed (e.g., Fish/Salmon -> Fruit/Salmon), you could use dependent data validation. This way, the second selection will reset or limit options based on the first choice. You’ll need to set up a named range for each category and then apply data validation rules for the second dropdown accordingly.

      You can use a Custom Data Validation with an IF statement to restrict invalid subcategory selections. For example, let’s say “Fish” is selected in the first dropdown, and you want to force the user to only choose “Salmon” or “Tuna” in the second dropdown. You can apply a formula like this in the second dropdown:
      =IF(E1=”Fish”,OR(F1=”Salmon”,F1=”Tuna”),OR(F1=”Apple”,F1=”Orange”))
      This will ensure that the second selection matches the first dropdown category.

      You can adjust this formula based on your category-subcategory structure.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo