How to Populate List Based on Cell Value in Excel (8 Methods)

We have the employee names of the respective departments. We’ll populate the list of employees based on a department.

Method 1 – Populating a Data Validation Drop-Down List Based on Cell Value in Excel

The sample dataset contains employees of 3 different departments. We’ll populate a drop-down list with employee names based on the department we select. Then, we can select employee names from the drop-down options to fill the list when needed.

Part 1.1 – Creating a Dependent Drop-Down List Using the INDIRECT Function

Steps:

• Select the range B7:D13.
• Type Marketing in the Name Box to name the cell range. Note that the named range doesn’t include the header.

• Name the respective cell ranges for the Sales and Finance departments.
• Select the range B16:B22 and go to the Data tab.

• Select Data Validation from the Data Tools group.
• Choose List in the Allow field.
• Set B6:D6 as the Source.

• Press OK to close the dialog box.
• We created the drop-down list of departments. Click the drop-down icon and you’ll see the available options.

• Select the range C16:C22.
• Go to the Data tab.

• In the Data Tools group, select Data Validation.
• Choose List in the Allow field.
• Insert the following formula in the Source field:

• Press OK to close the dialog box.
• Select Sales from the department drop-down. The corresponding drop-down list in C16 will display the employee names from the Sales department only.

• You can populate the list by selecting from the drop-down options. We’ve filled a portion of the list in the following picture.

• Change the department in cell B19 from Finance to Marketing. However, the corresponding employee name in cell C19 still stays, which needs to be fixed.

• Go to the Developer tab and select Visual Basic.

• In the VBA window, double-click on Sheet2 (Dependent Drop-Down List) from the Microsoft Excel Objects.
• In the code window, paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
 If Target.Validation.Type = 3 Then
 Application.EnableEvents = False
 Target.Offset(0, 1).ClearContents
 End If
End If
exitHandler:
 Application.EnableEvents = True
 Exit Sub
End Sub

• Save the file and close the VBA window.
• Change a department in the drop-down list on the left. The corresponding employee’s name will be cleared immediately.

Read More: How to Create Dynamic Dependent Drop Down List in Excel

Part 1.2 – Restricting Changes in the First Drop-Down List Using IF and INDIRECT Functions

• Open the Data Validation after selecting the cell range for departments.
• Choose List in the Allow field and insert the following formula in the Source field:

• Press OK to close the dialog box.
• A warning message box will pop out. Click Yes.

• Click the drop-down of the already inserted department. It’ll have no effect.

Read More: How to Use IF Statement to Create Drop-Down List in Excel

Method 2 – Combining INDEX, ROW, and MATCH Functions to Automatically Populate a List Based on Cell Value

Steps:

• Select cell C15.
• Go to the Data tab.

• Select Data Validation from the Data Tools group. The Data Validation dialog box appears.
• In the Allow field, choose List from the drop-down.
• Select B6:D6 as the Source.

• Press OK.
• The department drop-down list appears in cell C15. You can choose any of them.

• Select cell C18 and insert the following formula:

• Press Enter.
• It’ll spill the employee list of the department from cell C15.

• Change the department to Finance. The list will get updated automatically.

• We can also use the below formula with the IF function to return blank cells instead of 0 when auto-populating a list based on cell value.

• We can apply Conditional Formatting to make the 0 values invisible by making the font color white wherever we find them. The formula used is:

It’s pretty much the same formula as we used earlier, the additional IFERROR function here will return blanks if any error is encountered.

To apply Conditional Formatting:

• Select the desired range.
• Select Conditional Formatting drop-down.

• Click New Rule. The New Formatting Rule dialog appears.
• Choose Format only cells that contain rule type.
• In the Format only cells with fields, select Cell Value, equal to, and 0 for the three boxes, respectively.

• Click Format.
• In the Format Cells dialog box, go to Font and choose White as Color.

• Press OK to close the Format Cells dialog box.

• Press OK to close the New Formatting Rule dialog box.

The following GIF displays the dynamic list generation whenever we change the department.

Read More: Excel Formula Based on Drop-Down List

Method 3 – Inserting the FILTER Function for Populating a List Based on Cell Value in Excel

Steps:

• The department selection in C15 is created via Data Validation drop-down as shown in the previous methods.
• Select cell C17.
• Insert the following formula:

• Press Enter. The respective employee list will get spilled depending on the department in cell C15.

Method 4 – Combining UNIQUE and FILTER Functions to Populate the List with Non-repeating Entries

Steps:

• Put a Data Validation drop-down in C15 for the department (see Method 1).
• Select cell C17.
• Insert the following formula:

• Press Enter and the formula will spill the employee list of the respective department.

• Ramiro Harper was found twice in the Sales department in the main dataset. In the spilled result, the name occurs only once.

Method 5 – Populating a List Based on Cell Value by Using an Array Formula

In the following image, we have some IDs, Brands, Model names, and the Unit Prices of different mobile phones. We’ll select a brand name in cell C16 from the drop-down and want to get all the information about the mobile phones available for that brand only.

Steps:

• Select cell B19.
• Insert the following formula:

• Press Enter.
• Apply AutoFill to the right side and also to the cells below.
• You’ll get a list of all the information about the mobile phones for that brand only.

• You can change the cell formatting for the Unit Prices from General to Accounting through the Number group in the Home tab.

Method 6 – Joining the FILTER and INDIRECT Functions to Populate a List Based on Cell Value

Customers get a token based on the service they need whenever they go to a bank. Each of the bank employees provides a particular service, so different token types are assigned to different employees (denoted by x). We want to get the list of token numbers assigned to a particular employee we select.

Steps:

• Select the range B6:E16.
• Go to the Insert tab.

• In the Tables group, select Table.
• The Create Table dialog box will appear.

• Press OK.
• This will create an Excel table.

• Select the range B7:E16.
• Type Table1 in the Name Box to name the selected range.

• Select cell H8.
• Insert the following formula:

• Press Enter. It’ll spill the list of token numbers assigned to the employee from cell H6.

Read More: Create Excel Filter Using Drop-Down List Based on Cell Value

Method 7 – Populating a List Based on Multiple Cell Values in Excel

We have IDs, Brands, Model names, and Unit Prices of several mobile phones. We’ll populate a list of phones by Model names based on the Brand Name and the Unit Price.

Steps:

• Select cell E17.
• Insert the formula:

• Press Enter.
• Apply AutoFill.

The following GIF shows how to apply AutoFill.

Read More: How to Create Dependent Drop Down List with Multiple Words in Excel

Method 8 – Combining IFERROR, VLOOKUP, and COLUMN Functions to Populate the List Based on Cell Value

We have the Full Name of the employees, their Department, Designation, and Annual Salary. We want to extract the list of info based on their Full Name. Since we have the names in the first column of the dataset, we can use the VLOOKUP function.

Steps:

• Select cell B19.
• Insert the following formula:

• Press Enter.
• Drag the AutoFill icon to the right.

Download the Practice Workbook

Further Readings

• Conditional Drop Down List in Excel
• How to Make Dependent Drop Down List with Spaces in Excel
• How to Extract Data Based on a Drop Down List Selection in Excel
• How to Change Drop Down List Based on Cell Value in Excel

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel