In Microsoft Excel, sometimes we may need to determine or find values based on some criteria or condition from a list. It is not an impossible task to perform. In fact, we can do it quite easily with different methods. In this article, I am going to explain 6 smart ways to populate a list based on cell value in Excel. I hope it will be very helpful for you if you are looking for an efficient way to do so.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
6 Smart Ways to Populate a List Based on Cell Value in Excel
Among many other methods, I am going to explain 6 smart ways here to populate a list based on cell value in Excel with the help of the INDIRECT, IFERROR, INDEX, MATCH, IF, FILTER, UNIQUE, and some other functions. The methods are described in the following section.
1. AutoFill List Based upon Cell Value
With the help of a formula combined with the IFERROR, INDEX, and MATCH functions, we can autofill a list based on cell value. Let’s go into detail.
Steps:
- First of all, create an organized dataset. Here, I have created a dataset with players from different clubs. My purpose is to autofill the player’s name just from the club’s name.
- In order to create the club names list, select cell C14 and go to the Data tab.
- Afterward, click on Data Validation from the ribbon.
- Now, pick List from Allow under the Settings tab.
- Followingly, define the cells (i.e. B4:D4) containing club names.
- After that, click on OK.
- Now, you can choose any club name from the list.
- Now, select a cell where you want to have the players’ names and input the following formula in that cell.
=IFERROR(INDEX($B$4:$D$12,ROW(B3:D12),MATCH($C$14,$B$4:$D$4,0)),"")Formula Explanation
- Here MATCH($C$14,$B$4:$D$4,0) this portion is matching the entered Club Name with the Dataset and it is considered only exact matching.
- ROW(B3:D12) is counting the rows number of the dataset.
- INDEX($B$4:$D$12, ROW(B3:D12), MATCH($C$14,$B$4:$D$4,0)) is the portion of the formula which is used to find the matched player’s names. If the data is not found in the given dataset then it will be through #NA error.
- Lastly, IFERROR is to handle any kind of error.
- Finally, press ENTER to have the players’ names from the defined club.
- You can change the club’s name from the list in cell C14 and the players’ names will auto-update.
Read More: How to Use AutoComplete in Excel (4 Easy Ways)
2. Apply FILTER Function to Populate a List Based on Cell Value
We can also use the FILTER Function to populate a list based on a cell value. See details in the below section.
Steps:
- Create a related dataset first. Here, I have project name along with the person name on that project. I want to find the projects based on person’s name.
- Input the name of a person in the C19
- Now, insert the following formula in C20 to find the projects related with that person’s name.
=FILTER(B5:B17, C19=C5:C17)Formula Explanation
- In the FILTER function, B5:B17 is the range from where we will extract the data.
- C19 will give the input name and will compare with the name range C5:C17.
- Now, press ENTER to have the desired output.
Read More: [Fixed!] AutoComplete Not Working in Excel (2 Possible Solutions)
3. Use INDIRECT Function for Dependent Drop Down List
In order to create a dependent drop-down based on a cell value, we can use the INDIRECT function. Just follow the following procedures to do so.
Steps:
- First of all, organize some lists. Here, I have created 4 lists.
- In the second step, name those lists. I have named the cells under Foods List as Foods.
- Similarly, I have named Dairy, Vegetables, and Fruits for the other three column members.
- Now, select all the cells under the Food Type column to create a drop-down list in each selected cell with the Food List column.
- Next, go to Data Validation from Data.
- Now, pick List from Allow under the Settings tab.
- Input the following formula in Sorce and press OK.
=Foods
- Now, similarly, create a drop-down in the Items column.
- Here, insert the following formula in the Source.
=INDIRECT(B14)
- A warning message will appear. Press Yes.
- Now, use Fill Handle to AutoFill the list in the rest of the cells in the Items columns.
- Now, select a type from Food Type and the related options will appear in the Items.
- To understand it easily, have a look at the below GIF .
Read More: Autocomplete Data Validation Drop Down List in Excel (2 Methods)
4. Restrict Changes in First Drop Down List
It is almost similar mentioned in Method 3. But the difference is that if you select a food type once, you can not change it later. It gets restricted. For more details, go through the following section.
Steps:
- Name the lists with a specified name as mentioned in Method 3.
- Now, select all the cells under the Food Type column to create a drop-down list in each selected cell with the Food List column.
- Next, go to Data Validation from Data.
- After that, insert the following formula in the Source section and click OK.
=IF(B14="",Foods, INDIRECT("FakeRange"))
- A warning message will appear. After that, press Yes.
- Now, you can see that if you select a food type once, you can not change it later. It gets blocked.
Read More: How to Autocomplete from List in Excel (4 Easy Ways)
5. Adopt UNIQUE Function to Populate a List Based on Cell Value
There is another smart way to populate a list based on cell value is to use the UNIQUE function comprised with the FILTER function. Let’s go into detail.
Steps:
- First of all, create a related dataset. Here, I have a project name along with the employee name on that project. I want to find the project name based on the employee’s name.
- Input the name of an employee in the C25
- Now, insert the following formula in C26 and press ENTER to find the project related to that employee’s name.
=UNIQUE(FILTER(B5:B23,C5:C23=C25))
6. Use Array Formula to Populate List Based on Cell Value
We can also use an array in the combined formula to have a populated list. The defined array will go through several functions and return the satisfied ones. Just follow the following procedures to do so.
Steps:
- Create an organized dataset. I have a dataset with phone models with related particulars. I want to populate a list based on brand names where I will define brand names as an array.
- Write the brand names in the Brand Name column.
- Now, insert the following formula in cell C19 and press ENTER to find the related particulars in the array.
=INDEX($B$5:$E$16, SMALL(IF(COUNTIF($B$19:$B$20,$C$5:$C$16), MATCH(ROW($B$5:$E$16), ROW($B$5:$E$16)), ""), ROWS(C19:$C$19)), COLUMNS($B$4:B4))
- Now, AutoFill with Fill Handle horizontally and vertically to have other particulars.
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
At the end of this article, I like to add that I have tried to explain 6 smart ways to populate a list based on cell value in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
Thank you much for very useful tools Md. Abdullah!
I do have a question…
On solution #1 the list fill cell formula is :
=IFERROR(INDEX($B$4:$D$12,ROW(B3:D12),MATCH($C$14,$B$4:$D$4,0)),””)
There is no data on Row 3… why does formula use ROW(B3:D12)? Yet INDEX & MATCH use B4?
Just trying to understand how formula works better. Thanks!
Thank you ROB for your comment. Here, ROW function is working as the row index number of the INDEX function. Actually, it should be ROW(B2:D9)=> {2;3;4;5;6;7;8;9} which denotes respectively the 2nd, 3rd, 4th…. up to 9th row number of this ($B$4:$D$12) array of INDEX function. Here, we ignore the 1st row of ($B$4:$D$12) this array as 1st row contains the Club Name’s not any player Name’s.
On the other hand, you have to use B4:D4 row in the MATCH function which will search for the Club Name’s and act as the column index number in the INDEX function.
Say, when you choose Borussia Dortmund or C4 cell from the list of C14 cell then the MATCH function will return 2 and thus the column index number of INDEX function will be 2 so INDEX function will give all the rows (except 1st one) of 2nd column of the given array which means all the player name’s of the Borussia Dortmund club.
So, you can use the formula like the below one. =IFERROR(INDEX($B$4:$D$12,ROW(B2:D9),MATCH($C$14,$B$4:$D$4,0)),””)