This post will guide you how to create a drop down list and how to auto fill cells in Excel based on a dependent drop down list. When selecting a value from the drop down list, then it should be able to populate the value in another corresponding cell.

For examples, you have created a drop down list based on the values in range B2:B4, and when you select any value from the drop down list, the corresponding value in range c2:C4 should be populated in another cell automatically.

1. Auto Populate Cells Based on Dropdown Box Selection

You can use the Data Validation feature in Excel to create a dropdown box, and then use a combination of the IF and VLOOKUP functions to populate cells based on the selected value in the dropdown box.

Here are the steps:

Step1: select one cell that will be holding the drop down list. Such as: B6

Step2: Go to DATA tab, click Data Validation command under Data Tools group.

Step3: the Data Validation window will appear.

Step4: change the Allow: value to “List” from the drop-down list under Validation criteria section. Then select range B2:B4 as Source data.

Step5: the drop down list has been created.

Step6: type the below formula in another cell D6, press Enter key.

=IF(B6="","",VLOOKUP(B6,B2:C4,2,FALSE))

Step7: select another value “Word” from the drop-down list in Cell B6, you will see that the corresponding value in Cell D6 will be populated automatically.

Now when you select a value from the dropdown box, the corresponding value will be populated in the cells you specified using the VLOOKUP function. If no value is selected in the dropdown box, the cells will remain blank.

2. Video: Auto Populate Cells Based on Dropdown Box Selection

This video provides step-by-step instructions on how to auto-populate cells based on dropdown box selection based on Data Validation feature and a combination with IF function and VLOOKUP function in Excel.