Md. Abdullah Al Murad
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
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")- 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.
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
=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
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
=IF(B4="",Foods, INDIRECT("FakeRange"))
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
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- 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
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.
=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
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
=INDIRECT(B14)
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
| 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.
