How to fill a list based on cell value in Excel (2023)

emMicrosoft Excel, sometimes we need to determine or find values ​​from a list based on certain criteria or conditions. It is not an impossible task to accomplish. In fact, we can easily do it using different methods. In this article, I will explain 6 smart waysFill a list based on cell value in Excel. I hope this is helpful if you are looking for an efficient way to do this.

download exercise book

Download this workbook to practice while reading this article.

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

Among many other methods, here I will share 6 clever ways to populate a list based on cell value instanding outwith the help ofINDIRECTLY,SEERRO,INDEX,FIT,SE,FILTER,EXCLUSIVE,and some other functions. The methods are described in the next section.

1. Autocomplete list based on cell value

Using a formula combined with theSEERRO,INDEX, miFITfunctions, we can automatically fill a list based on the cell value. Let's get into the details.

Steps:

• First, create an organized data set. Here I created a dataset with players from different clubs. My goal is to autocomplete the player name with the club name.
• To create the list of club names, select the cellC14and goes toDataAba.
• Then clickvalidity dateLove.

• now chooseListoutsideTo allowunderIdeasAba.
• Then set up the cells (ie.B4:D4) with the name of the club.
• after that clickOK.

• You can now select any club name from the list.

• Now select a cell where you want to have the names of the players and enter the following formula in this cell.

• finally pressureFORBIDDENhave the names of the club's players defined.

• You can change the name of the club from the list in the cellC14and the player names will update automatically.

2. Apply FILTER function to populate a list based on cell value

We can also use theseFILTERFunction to fill a list based on a cell value. See details in the section below.

Steps:

• First create an associated data set. Here I have the name of the project along with the name of the person on that project. I want to find projects based on a person's name.
• Enter a person's name in theC19
• Now paste the following formulaC20to find projects related to that person's name.

• press nowFORBIDDENto get the desired output.

3. Use the INDIRECT function on the dependent dropdown list

To create a bound dropdown based on a cell value, we can use theINDIRECTLYFunction. To do this, simply follow the steps below.

Steps:

• First, organize some lists. Here I created 4 lists.

• In the second step, name these lists. I named the cells belowshopping listcongroceries.

• I also nameddairy,Vegetables, mifruitfor the other three members of the column.

• Now select all the cells below thatkind of foodColumn to create a dropdown list in each selected cell with theshopping listTo share.
• then go tovalidity dateoutsideData.

• now chooseListoutsideTo allowunderIdeasAba.
• Enter the following formulaThoseand pressureOK.

• Now, in the same way, create a dropdown menu in theArticleTo share.

• Here, paste the following formula inThose.

• A warning message is displayed. Pressmi.

• use nowfiller handleForautocompletarlist the remaining cells noArticlecolumns.

• Now select a typekind of foodand the associated options are displayed on theArticle.
• To understand it easily, take a look at the followingGIF.

Keep reading: Data validation autocomplete dropdown in Excel (2 methods)

4. Restrict changes to the first dropdown list

It is almost similar to the one mentioned inMethod 3. The difference, however, is that once you've selected a food type, you can't change it later. It will be restricted. See the section below for more details.

Steps:

• Give the lists a specific name as mentioned inMethod 3.
• Now select all the cells below thatkind of foodColumn to create a dropdown list in each selected cell with theshopping listTo share.
• then go tovalidity dateoutsideData.

• After that, paste the following formula in theThosesection and clickOK.

• A warning message is displayed. then pressMI.

• Now you can see that once you select a food type, you can't change it later. It will be blocked.

Keep reading: How to autocomplete lists in Excel (4 easy ways)

5. Adopt SINGLE function to populate a list based on cell value

There is another clever way to populate a list based on cell value using theEXCLUSIVEfeature with the includedFILTERFunction. Let's get into the details.

Steps:

• First, create a related record. Here I have a project name along with the name of the contributor to that project. I want to find the name of the project based on the name of the contributor.
• Enter the name of an employee in theC25
• Now paste the following formulaC26and pressureFORBIDDENto find the project related to this employee's name.

6. Use array formula to populate list based on cell value

We can also use an array in the combined formula to get a populated list. The defined array iterates through several functions and returns the satisfied ones. To do this, simply follow the steps below.

Steps:

• Create an organized registry. I have a record of phone models with associated specifications. I want to populate a list based on tag names where I define tag names as an array.
• Write the brand names on thebrandTo share.
• Now paste the following formula in the cellC19and pressureFORBIDDENto find the corresponding information in the matrix.

• Now,autocompletarcomfiller handlehorizontally and vertically for other details.

things to remember

Diploma

At the end of this article, I would like to add that I tried to explain 6 smart waysFill a list based on cell value in Excel. I would be very happy if this article could help all Excel users a little bit. If you have any further queries, please comment below. Visit our site for more articles on how to use Excel.

more reading

• Use AutoComplete in Excel (4 easy ways)
• How to Plot an Equation in Excel (6 Easy Ways)
• Enable AutoComplete in Excel (3 Easy Ways)
• How to Edit AutoComplete in Excel (4 Easy Methods)