How to Populate a List Based on Cell Value in Excel (6 Ways)

In MS Excel, sometimes we may need to determine or find values based on some criteria or condition from a list. Assuming we have a task plan with the corresponding person’s name who oversees each task. And now we need to list out all tasks’ names in rows based on the given person. In this way, Excel provides various ways to populate a list based on the cell value. In this article, we will see how we can populate a list based on cell value in Excel.

Download the Practice Workbook

6 Ways to Populate a List Based on Cell Value in Excel

1. AutoFill a List Based upon a Cell Value

Let’s project lists with project workers’ names. In each project, the worker’s names are assigned in this format “Project_Number_Name_Serial”. So, our task is to find all the worker’s names using the project.

Step 1: Enter the following formula in cell D17 and press Enter

Step 2: Now from the drop-down list select any project name

Step 3: All the worker’s names will be shown

2. Populate Rows Based on Specific Cell Value with Formula

Let’s see how we can search a worker’s name in a different approach. In the dataset, one person could be assigned to multiple projects. Now our task is to find out the project names using the worker’s name. Here the main dataset will be like this:

Step 1: Enter the following formula in cell G6 and press ENTER keys

Step 2: Now type any name in cell G5 and press Enter

3. Block Changes in First Drop Down

Let’s say we have multiple lists of different food items. Each list is different from others and the specific food item should be on the valid list

In another worksheet, we will select foods items according to their types.

Our concern is that if we select any food types in column B then in column C (Items) only the items will be available which are under that food type or list.

Step 1: Select the Food Items cells and open Data Validation

Step 2: Then in Source write the following formula

Step 3: A warning will pop up. Click on the Yes button

Step 4: Now select Food Type and then select the Items

Step 5: Once you have entered the Food Type and Items you cannot change any food items. So, there is no chance of error matchmaking

4. Create a Unique List in Excel based on Criteria

In terms of finding unique values lists, Excel provides various ways. Let’s consider the same dataset as method 2 with duplicate values. Now our goal is to find out the unique list using formulas.

Step 1: Enter the following formula in cell G6

Step 2: Now enter any Name in cell G5 and press Enter

5. Extract all Rows from Lists that Meet Criteria in One Column Using Array Formula

Let’s have a product dataset with their ID, Brand, Model, and Unit Price. Now our task is to find out those rows where the Brand name will be matched with our entered Brand Names in cells H5 and H7.

Step 1: Enter the following formula in cell B19 and press CTRL + SHIFT + ENTER and copy the formula in the whole table.

Step 2: Now enter names in cells H5 and H7 and press Enter

6. Create a Dependent Drop-Down List in Excel

In MS Excel, the drop-down list is a useful feature when we are performing data entry forms or Excel Dashboards.

It shows a list of items as a drop-down in a cell, and the user can select from the drop-down. This could be useful when you have a list of names, products, or regions that we often need to enter in a set of cells.

Let’s assume we have three different food item lists, now we will create a dependent drop-down List in Excel using those lists.

Step 1: Open Data Validation option

Step 2: In the Data Validation window, select Allow as List and Select the Source as below

Step 3: You will find a Drop-Down List in the Food Types column

Step 4: Now select all the datasets and click on Create from Selection option under the Formulas tab

Step 5: A pop-up will appear. Click on Top row and then press the Ok button

Step 6: Now go to cell D14 and open Data Validation. Make sure the Allow is set as List and then write the following formula in the Source. Lastly, press the OK button

Step 7: A warning will pop up. Press the Yes button

Step 8: Now select any Food Types from the first Drop Down list and find the associate Items list in the other Drop-Down list

Step 8: Final output will be like this:

Things to Remember

Conclusion

These are some ways to populate a list based on cell value in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.

Further Readings

• Transfer Data from One Excel Worksheet to Another Automatically
• How to Auto Populate from Another Worksheet in Excel
• How to Fill Down to Last Row with Data in Excel (3 Quick Methods)