7 Easy Ways to Populate List Based on Cell Value in Excel

In some cases, we need to populate list based on cell value in Excel. Like, suppose you have a dataset of sales of the previous year. Suppose you need to populate a list of data based on some required cell value in Excel i.e. specific seller’s data or salespersons in a region. With suitable criteria or linking cells, it can be populated. In this article, we are going to show you how to populate list based on cell value.


πŸ“ Download Excel File

To practice and get the overview, please download the excel file:


Learn to Populate List Based on Cell Values in Excel with 7 Approaches

To populate list based on cell value in Excel, we are going to discuss 7 easy approaches. For the approaches, we took a dataset containing Sales data for June and July. The dataset includes Sales Person, Region, Sales Unit(June), and Sales Unit (July). Also in the dataset, there are 5 columns as well as 16 rows. In this article, we will learn the use of FILTER, TEXTJOIN, UNIQUE, EXACT, COUNTIF, IFERROR, and INDEX functions while populating list based on cell value.

Dataset of Excel Populate List Based on Cell Value.

Method

1. Populate Lists Based on Specific Cell Values with Formula in Excel

Suppose you need to extract Sales persons who work in region D. Here FILTER with a specific cell containing region D can be a key. Please check out the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially select a blank i.e. H7.
  • Then insert the following formula to get the sales persons of region D.
=FILTER(B6:B16,H6=C6:C16)

πŸ”¨ Formula Breakdown

πŸ‘‰Β  Logical Expression of the formula: =FILTER(range,data=range)

πŸ‘‰Β  H6=C6:C16 indicates to find region H6=D in range C6:C16.

πŸ‘‰Β  Now FILTER gathers the data relating to region D in range B6:B16.

  • Finally, Press Enter and get the salespersons who work in region D.

Use of FILTER function Populate List Based on a Cell Value in Excel

Method

2. Populate Lists in a Cell Based on Multiple Cell Value in Excel

Suppose you need to find every Sales persons who work under a region. Here FILTER along with a TEXTJOIN function with a specific cell containing region D can be a key. Please check out the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially select a blank i.e. H7.
  • Secondly, Insert the below region in G6=A.
  • Then insert the following formula to get the sales persons of every region that is imputed at G6.
=TEXTJOIN(β€œ,”,TRUE,IF($C$6:$C$16=G6,$B$6:$B$16,””))

πŸ”¨ Formula Breakdown

πŸ‘‰Β  Logical Expression of the formula: =TEXTJOIN(β€œ,”,TRUE,IF(range with return=ref cell, returned range,””))

πŸ‘‰Β  $C$6:$C$16=G6 indicates to find region G6=A in range C6:C16 with a return value..

πŸ‘‰Β  IF($C$6:$C$16=G6,$B$6:$B$16,””)= {β€œβ€;”Mike”;””;””;”Steve”;””;””;””;””;”Steve”;””} who works in region A.

πŸ‘‰Β  TEXTJOIN commands the formula to connect these sales persons names by comma(,).

  • Finally, Press Enter and get the sales persons who work in region A in a cell joining comma.

Use of TEXTJOIN function Populate List in a Cell from multiple cells in Excel

Similarly inputting regions in G7, G8, and G9 we get sales persons for every region.

Method

3. Populate List from Another Sheet Based on Cell Value in Excel

To avoid congestion we need to use different sheets for different purposes. But sometimes we need to connect them based on some cell value. Please follow the below procedure to populate the list from another sheet based on cell value.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we need to select the cell which we want to get. Suppose we need the Sales data for July.
  • Then we need to copy the cells. Press Ctrl+C from the previous sheet.

copy multiple cells

  • After that, select a blank cell of the new sheet i.e. D6 to input the values.
  • By Right-clicking on the mouse, get a context menu.
  • Next, Click out Paste Link from Paste Options.

Paste option and linking values.

  • finally, get the entire data for July from the previous sheet.
  • Populate List from Another Sheet Based on Cell Value in ExcelIf we need to change data from the previous sheet, data will change in the new sheet too.
  • For example, we imputed 500 in E6.

Sales data

  • In the new sheet, 500 is showing at D6.

Populate the List from Another Sheet Based on Cell Value.

Linking to another sheet we can also populate data to a new one.

Method

4. Generate List Based on Criteria

To distinct Sales persons who are available on the dataset, you can introduce a combined formula of INDEX, IFERROR, and MATCH functions. Follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select a blank i.e. G6.
  • Then, input a heading that you intend to get.
  • Next, insert the following formula combining IFERROR, INDEX, and MATCH functions.

=IFERROR(INDEX($B$6:$E$16,ROW(B6:E16),MATCH($G$5,$B$5:$E$5,0)),””)

πŸ”¨ Formula Breakdown

πŸ‘‰Β  General logical Expression of the formula: =IFERROR(INDEX(array with return,ROW(array),MATCH(ref_Cell,range,0)),””)

πŸ‘‰Β  MATCH($G$5,$B$5:$E$5 indicates to get a match exactly for G5=Sales Person from B5 to E5 with a return value .

πŸ‘‰Β  MATCH($G$5,$B$5:$E$5,0)= {1} that means Sales Person appears in the 1st column.

πŸ‘‰Β  INDEX function runs the array to get names matching the heading Sales Person.

πŸ‘‰Β  To deal with errors and leave a blank, here the IFERROR function is used.

  • Lastly, Press Enter and get the sales persons who are available in the dataset.

IFERROR, INDEX, MATCH functions to Populate List from Another Sheet Based on column in Excel

Method

5. Create Unique List Based on Criteria in Excel

Suppose you need to extract unique values of Sales persons who work in a region, for example, say A. Here FILTER with a UNIQUE function can be the way. UNIQUE functions eliminate duplication whereas the FILTER function filters the data based on criteria. Please follow the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, select a blank i.e. G6.
  • Secondly, input a region in H6.
  • Thirdly, Then insert the following formula combining FILTER and UNIQUE functions.
=UNIQUE(FILTER(B6:B16, C6:C16=H6))

πŸ”¨ Formula Breakdown

πŸ‘‰Β  Logical Expression of the formula: =UNIQUE(FILTER(range,data=range))

πŸ‘‰Β  H6=C6:C16 indicates to find region H6=D in range C6:C16.

πŸ‘‰Β  FILTER(B6:B16, C6:C16=H6)= {β€œMike”;”Steve”;”Steve”}; FILTER function filters the data but doesn’t eliminate duplications.

πŸ‘‰Β  However, UNIQUE functions avoid repetitive names. Results in {β€œMike”;”Steve”}.

  • Finally, Press Enter and get the sales persons Mike and Steve who work in region A.

Populate unique list from table Based on Cell Value in Excel

Method

6. Create Dependent Drop-Down List with Data Validation Option

Using the Validation DataΒ option from Data of Top Ribbon, you can get your desired data. Please follow the necessary steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, Get a blank cell i.e. G6.
  • Secondly, Select Data for the Top Ribbon.
  • Thirdly, Click on the Data Validation option and a box will appear named Data Validation.
  • After that, select list from the drop-down box from Allow of Validation Criteria.
  • Next, Choose range =$B$5:$E$5 in the source.
  • Then click on the OK button.

Use of Data Validation.

  • Finally, you will get a drop-down box at the side of the G6 box where you may find the headings of the dataset.

Population data with dropdown box.

Now we need to work with data that is linked to G6.

  • Initially, Select the entire dataset.
  • Secondly, click on Create from the Selection option from Formulas Menu.
  • Thirdly, Create Names from the Selection box will appear.
  • Finally, Select only the Top row and click on OK button.

table selection and Create from selection.

  • Now again select cell H6.
  • Select Data Validation from the Data menu of Top Ribbon.
  • Choose List from Allow of Validation Criteria.
  • Write down =INDIRECT(G6) in Source
  • After all, click on OK button.

Use of Data Validation.

  • Finally, A drop-down box will appear beside the cell. Clicking the dropdown box you will get the options for Region.

Population data with dropdown box.

Method

7. Dynamic List Based on Criteria in Excel

Let’s say, we need to get the sales persons data for a specific region. For example say, we need to collect data for regions B and C. What to do in this case? Well, It might be hear scary. But the process is quite interesting. Please check the steps,

⬇️⬇️ STEPS ⬇️⬇️

  • First, select a blank cell i.e. G6.
  • Secondly, input the following formula combining INDEX, COUNTIF, and MATCH functions to get the overall data.
=INDEX($B$6:$E$16, SMALL(IF(COUNTIF($H$14:$H$15,$C$6:$C$16), MATCH(ROW($B$6:$E$16), ROW($B$6:$E$16)), β€œβ€), ROWS(G6:$G$6)), COLUMNS($B$5:B5))

πŸ”¨ Formula Breakdown

πŸ‘‰Β  COUNTIF($H$14:$H$15,$C$6:$C$16)= {1;0;1;0;0;1;1;1;1;0;0} means H14=B, H15=C exist in $C$6:$C$16 with a return value. Here, 1= TRUE and 0= FALSE.

πŸ‘‰Β  MATCH(ROW($B$6:$E$16), ROW($B$6:$E$16)) Here MATCH function is dedicated to finding match values in the range.

πŸ‘‰Β  IF(COUNTIF($H$14:$H$15,$C$6:$C$16), MATCH(ROW($B$6:$E$16), ROW($B$6:$E$16)), β€œβ€), ROWS(G6:$G$6))= {1;””;3;””;””;6;7;8;9;””;””} that dictates that to match the values with 1=Jenny , 3=Ammy , 6=Ammy , 7=Mike , 8=Ammy , 9=Ammy.

πŸ‘‰Β  INDEX value runs the array and looks up the values.

  • After that, by Pressing Enter we get the sales person Jenny at G6.

USe of INDEX, COUNTIF, MATCH functions to populate list based on cell value.

  • Now, we need to Fill the Handle.

  • Finally, we get the Sales Persons that work in regions B and C.

The total output of Populate List Based on Cell Value using INDEX, IFERROR, and EXACT functions in Excel.


πŸ“„ Important Notes

πŸ–ŠοΈΒ  Be careful while using a combination of INDEX, MATCH, and COUNTIF functions.

πŸ–ŠοΈΒ  During writing down the formulas look closely to use with the returned value, or without a return value.


πŸ“ Takeaways from This Article

πŸ“ŒΒ  Formula combination of INDEX, MATCH, and COUNTIF to make dynamic lists.

πŸ“Œ We outlined the use of FILTER and UNIQUE functions to generate lists based on criteria.

πŸ“ŒΒ  We also show you the method to extract data to a new list to get the job easier.


Conclusion

In this article, we tried to demonstrate every possible approach to populate lists based on cell value. We hope you enjoyed your learning. We appreciate your valuable suggestions. Please leave your thoughts in the comment section. For better understanding and new learning don’t forget to visit www.ExelDen.com.

(Visited 36 times, 1 visits today)
MD Tanvir Rahman

MD Tanvir Rahman

Hello, I am Tanvir. Graduated from BUET. Had a year-long Experience at Operation of Vitacan Industries Ltd. I found Excel is the most unique software to reduce both time and paper. Now, I am an Excel enthusiast, love to play with data and publish content.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo