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

Populating a list based on cell value in Excel means automatically adding information that meets certain criteria.

In this Excel tutorial, we will learn how to populate a list based on a cell value in Excel.

For example, in the following GIF, we have the employee names of the respective departments. Here, we populated the list of employees based on a department in 2 different ways by using formulas.

Populating a list based on cell value in Excel can be done by using Excel formulas and the Data Validation feature.

Learning how to populate a list based on cell value can help us to get the subset of a large dataset that meets a particular condition.

1. Populating Data Validation Drop-Down List Based on Cell Value in Excel

In this part, we’ll populate a data validation drop-down list based on cell value using the Data Validation feature and Excel functions.

Let’s see the dataset first. The following image presents the respective employees of 3 different departments. Here, we’ll show how to 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.

1.1 Creating Dependent Drop-Down List Using INDIRECT Function

In this section, we’ll create a dependent drop-down list that depends on a cell value using the Data Validation and the INDIRECT function.

We’ll first make a drop-down list for selecting the departments as well. So learn the steps below.

Steps:

• Select the range B7:D13 => Type Marketing in the Name Box to name the cell range.

• Similarly, name the respective cell ranges for the Sales and Finance departments.
• Next, select the range B16:B22 => 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.

In this way, we create the drop-down list of departments. Click the drop-down icon and you’ll see the available options.

Now, we’ll create a drop-down list of employee names based on the department.

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

• In the Data Tools group, select Data Validation >> choose List in the Allow field.
• Write the below formula in the Source field:

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

In this way, you can populate the list by selecting from the drop-down options. Here, I’ve filled a portion of the list in the following picture.

Now let’s change the department in cell B19 from Finance to Marketing. However, the corresponding employee name in cell C19 still stays there without getting cleared. And this is not desired.

We want to clear the corresponding employee name as soon as we change a department. To achieve this outcome, we’ll apply Excel VBA.

• First, go to the Developer tab => select Visual Basic.

• In the VBA window, double-click on Sheet2 (Dependent Drop-Down List) from the Microsoft Excel Objects.
• In the popped-out 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.
• Now try changing a department. The corresponding employee’s name will be cleared immediately.

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

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

In this section, we’ll use the IF and INDIRECT functions in the Data Validation to restrict the changes in the first drop-down list of the previous method. The first drop-down list was of department names. So, after restricting, we cannot change the department anymore.

The steps are exactly the same as the previous method to open the Data Validation after selecting the cell range for departments. After choosing List in the Allow field, insert the below formula in the Source field:

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

• Now 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

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

In this section, we’ll combine the INDEX, ROW, and MATCH functions to automatically populate the list based on cell value in Excel. However, we’ll make a drop-down list for selecting the cell value first by using the Data Validation feature. In this way, we don’t have to type the value manually.

Follow the steps below to perform the task.

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.

Hence, the department drop-down list appears in cell C15. You can choose any of them.

• Now, select cell C18 and write the formula:

• Press Enter.

It’ll spill the employees’ list of the department mentioned in cell C15.

• Let’s 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.

Moreover, we can apply Conditional Formatting to make the 0 values invisible by making the font color white wherever we find them. Here, the formula we 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, follow the steps:

• 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 => 0.

• Next click Format.
• In the Format Cells dialog box, go to Font => choose White as Color.

• Press OK to close the Format Cells dialog box.

• Again press OK to close the New Formatting Rule dialog box.

Thus we get the desired output.

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

Read More: Excel Formula Based on Drop-Down List

3. Inserting FILTER Function for Populating List Based on Cell Value in Excel

In this part, we’ll use the FILTER function for populating a list based on cell value in Excel. This method shows how to populate a list with employee names based on the department we choose. The department selection is done by using the Data Validation drop-down as shown in the previous methods.

Learn the steps below to perform the task.

Steps:

• Select cell C17 => Insert the following formula:

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

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

In this section, we’ll combine the UNIQUE and FILTER functions to populate the list with non-repeating entries in Excel.

Sometimes, the list may accidentally contain an employee’s name multiple times which is not desired. Follow the steps below to avoid getting repetitive names. Here, the department selection is done by using the Data Validation drop-down as shown in the previous methods.

Steps:

• Select cell C17=> Write the below formula:

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

In the following image, notice that Ramiro Harper was found twice in the Sales department in the main dataset. However, in the spilled result, the name occurs only once.

5. Populating List Based on Cell Value by Using Excel Array Formula

In this method, we’ll use an Excel array formula to populate a list based on cell value.

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

Follow the steps below to accomplish the task.

Steps:

• Select cell B19 => Insert the below formula:

• Press Enter => Apply AutoFill to the right side and also to the cells below.

Thus you’ll get a list of all the information about the mobile phones for that brand only.
The following GIF shows how to apply the AutoFill accurately.

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

6. Joining FILTER and INDIRECT Functions to Populate List Based on Cell Value

In this method, we’ll combine FILTER and INDIRECT functions to populate a list based on cell value in Excel.

Let us show you the dataset first. Usually, customers first get a token based on the service they need whenever they go to a bank. Each of the bank employees provides a particular service based on their expertise and experience.

In the following dataset, you’ll see different token numbers are assigned to different employees (denoted by x). We want to get the list of token numbers assigned to a particular employee we select.

Follow the steps below to perform the task.

Steps:

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

• In the Tables group, select Table.

The Create Table dialog box will pop out.

• Press OK.

This will create an Excel table.

• Now, select the range B7:E16 => type Table1 in the Name Box for naming the selected range.

• Select cell H8 => Type the following formula:

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

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

7. Populating List Based on Multiple Cell Values in Excel

In this section, we’ll combine multiple functions to populate a list based on multiple cell values in Excel.

Let’s understand the dataset first. In the following image, we have IDs, Brands, Model names, and Unit Prices of several mobile phones. Here, 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 the AutoFill correctly.

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

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

In this last method, we’ll combine the IFERROR, VLOOKUP, and COLUMN functions to populate the list based on cell value.

Suppose, our case is rather simple where 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.

Follow the steps below to accomplish the task.

Steps:

• Select cell B19 => Type the formula:

• Press Enter => Drag the AutoFill to the right.

Hence you’ll get the list of information of the respective employee selected in cell C16.

Download Practice Workbook

In this article, we have shown how to populate a list based on cell value in Excel. This will help us to get the desired information quickly without manually looking for it in a large dataset. We have explained different scenarios using 8 different methods. The methods require several Excel functions and also the Data Validation feature. Scenarios include: automatically populating the list; populating the list by selecting from the drop-down options, etc. Leave a comment for any further queries.

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