Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Populate a List Based on Cell Value in Excel

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.

Excel Populate List Based on Cell Value


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.

AutoFill List Based upon Cell Value

  • 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.

Excel Populate List Based on Cell Value

  • 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.

Excel Populate List Based on Cell Value

  • You can change the club’s name from the list in cell C14 and the players’ names will auto-update.

Excel Populate List Based on Cell Value


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.

Apply FILTER Function to Populate a List Based on Cell Value

  • Now, press ENTER to have the desired output.


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.

Use INDIRECT Function for Dependent Drop Down

  • 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.

Use INDIRECT Function for Dependent Drop Down

  • 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)

Excel Populate List Based on Cell Value

  • 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.

Restrict Changes in First Drop Down

  • 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.

Excel Populate List Based on Cell Value

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))

Adopt UNIQUE Function


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))

Use Array Formula to Populate List

  • 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.


Further Readings

Md. Abdullah Al Murad

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