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

Create a Dependent Drop-Down List in Excel

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.

AutoFill a List Based upon a Cell Value

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

=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")
Formula Explanation
  • Here MATCH($C$16,$B$3:$D$3,0) this portion is matching the entered Project Name with the Dataset and it is considered only exact matching.
  • ROW(B2:D11) is counting the rows number of the dataset.
  • INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)) this portion of the formula is finding the matched project workers names. If the data is not found in the given dataset then it will through #NA error.
  • Lastly, IFERROR is to handle any kind of error.

Excel Populate a List Based on Cell Value

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

Now from the drop-down list select any project name

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

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:

Populate Rows Based on Specific Cell Value with Formula

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

=FILTER(B4:B16, G5=C4:C16)

Formula Explanation

  • In the FILTER function, B4:B16 this is the range from where we will extract the data.
  • G5 in this cell will give the input name and will compare with the name range C4:C16
  • To explore more about the FILTER function, you can check this link

Formula with FILTER Function

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

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

Block Changes in First Drop Down

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

Block Changes in First Drop Down

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

Select the Food Items cells and open Data Validation

Step 2: Then in Source write the following formula

=IF(B4="",Foods, INDIRECT("FakeRange"))

Formula with IF and INDIRECT functions

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

A warning will pop up. Click on the Yes button

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

Now select Food Type and then select the ItemsNow 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

Excel Populate a List Based on Cell Value

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.

Create a Unique List in Excel based on Criteria

Step 1: Enter the following formula in cell G6

=UNIQUE(FILTER(B4:B22,C4:C22=G5))

Formula Explanation
  • FILTER(B4:B22, C4:C22=G5) this function is the same as method 2. It extracts all the matched names from the dataset. If there are any duplicate matches, the FILTER function also counts them.
  • To remove the duplicate values returned by the FILTER function, we have used the UNIQUE function. This function will remove all the duplicate values from the matched data. To explore more about the function, you can visit this link

Excel Populate a List Based on Cell Value

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

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.

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

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

=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3

Excel Populate a List Based on Cell Value

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

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.

Create a Dependent Drop-Down List in Excel

Step 1: Open Data Validation option

Open Data Validation option

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

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

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

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

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

=INDIRECT(B14)

Excel Populate a List Based on Cell Value

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

Excel Populate a List Based on Cell Value

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

Excel Populate a List Based on Cell Value

Step 8: Final output will be like this:

Excel Populate a List Based on Cell Value

Things to Remember

Common Errors When they show
#VALUE! Error In FILTER The include argument must have dimensions compatible with the array argument, otherwise, FILTER will return #VALUE!
#NA! Error If the formula does not find any data from the dataset, then it will return this error. We need to take the help of the IFERROR function to handle this error.
Naming in List In terms of giving the name of the list, we cannot use any space. If there is a space in a name, then we could use “_”.

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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo