How do you write an if then formula how to#
How do you write an if then formula full#

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. ➤ Select the cell F5, and then, go to the Data Tab > Data Tools Group > Data Validation Dropdown > Data Validation Option. And we have the following named ranges Fruits and Vegetables for the fruits range and vegetables range respectively. Here, we will be using the INDIRECT function along with the IF function to create a data validation formula. Method-4: Using the IF and INDIRECT Functions in Data Validation Formula in Excel Excel Data Validation Based on Another Cell Value.Excel VBA to Create Data Validation List from Array.Excel Data Validation Alphanumeric Only (Using Custom Formula).

How do you write an if then formula how to#

Read More: How to Use Named Range for Data Validation List with VBA in Excel ➤ Select the item Cabbage from the list for the category Vegetables.Įventually, we are getting the Item Cabbage for the corresponding Category Vegetables. ➤ Click on the dropdown list of cell F5, and select the item Apple from the list. 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. We have named the range of fruits as Fruits and the range of vegetables as Vegetables.Īfterward, the Data Validation dialog box will appear. Here, we will use the named ranges along with the IF function in the data validation formula to make a dropdown list. Method-3: Using IF Statement and Named Range in Data Validation Formula in Excel Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel Then, you will get the list of vegetables in the item list and select the first one (anyone which you prefer) Cabbage from here.įinally, we are getting the Item Cabbage for the corresponding Category Vegetables. ➤ You can select the Category as Vegetables also from the list. Then, you will get your desired item Apple for the category Fruits. Now, for selecting a fruit item like Apple, click on the dropdown list of cell F5 and select this item from the list. 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. ➤ Select the List option in the Allow box, and write the following formula in the Source box ➤ Select the cell E5, and then, go to the Data Tab > Data Tools Group > Data Validation Dropdown > Data Validation Option.Īfter that, the Data Validation dialog box will appear. In this section, we will create a dependent dropdown list where the Item list will be dependent on the Category list. Method-2: Creating a Dependent Dropdown List by Using IF Statement in Data Validation Formula Read More: How to Make a Data Validation List from Table in Excel (3 Methods)

How do you write an if then formula full#

➤ Select the full names from the list for the rest of the cells.įinally, you will get the full names of the employees with the corresponding header. ➤ You can change the header name from First Name to Full Name in cell E3. In this way, we are getting the first names of the salesperson’s names along with the header First Name. ➤ Select the first names from the list of the first names of cell E4. ➤ So, when we click on the drop-down symbol of cell E3, we will get the header First Name and selected it. 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. 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. ➤ Select the List option in the Allow box and write the following formula in the Source box and click OK. Then, the Data Validation dialog box will appear.

➤ Select the range E3:E12 and then go to the Data Tab > Data Tools Group > Data Validation Dropdown > Data Validation Option. Using the IF function in the data validation formula we will make the conditional list in the right-side table. Method-1: Using IF Statement to Create a Conditional List with the Help of Data Validation Formulaįor 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. We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience. By using this dataset, we will try to demonstrate the ways to use the IF statement in the data validation formula in Excel.

Here, we have some records of some of the products and their corresponding salesperson’s names of a company. 6 Ways to Use IF Statement in Data Validation Formula in Excel