Drop Down Populates Another Cell in Excel & Google Sheets

This tutorial demonstrates how changing the item chosen from a drop-down list can populate a different cell in Excel and Google Sheets.

VLOOKUP to Populate Another Cell

1. Create a drop-down list using data validation in Excel.

2. Now, use the VLOOKUP Function in a different cell to look up the value you choose from the drop-down box.
   Select the cell where you wish the VLOOKUP formula to go, and then type in the following formula:

=VLOOKUP(F2,B3:C9,2,FALSE)

The table (database) values are in B3:C9.The VLOOKUP Function takes the value in F2 and matches it to an equal value B3:B9. Then, it returns the corresponding value from Column C (Total Orders).

3. Choose a different Salesperson from the drop-down list, and the figure returned in F3 changes accordingly.

VBA to Populate Another Cell

You can also use VBA to populate another cell in Excel by using a change event when the value in a drop-down box is updated. You would first need to create a drop-down box through the Developer Ribbon instead of data validation. If you don’t see the Developer tab, you’ll need to enable it.

1. In the Ribbon, go to Developer > Insert > ActiveX Controls, and then choose the drop-down control.

2. Drag and drop to create a drop-down control in your worksheet. Click Properties in the Developer tab to switch on the properties for the drop down. Select the source for the drop-down list items and the linked cell for the drop down to go.

3. Use the ComboBox_Change event to create some VBA code that runs any time the value in the combo box is changed.

Drop Down to Populate Another Cell in Google Sheets

Google Sheets is also able to use the VLOOKUP Function to change the value in another cell based on the item chosen from a drop-down list.