MD Tanvir Rahman
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.
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.
🔨 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.
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.
🔨 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.
Similarly inputting regions in G7, G8, and G9 we get sales persons for every region.
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.
- 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.
- finally, get the entire data for July from the previous sheet.
If we need to change data from the previous sheet, data will change in the new sheet too.- For example, we imputed 500 in E6.
If we need to change data from the previous sheet, data will change in the new sheet too.
- In the new sheet, 500 is showing at D6.
Linking to another sheet we can also populate data to a new one.
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.
🔨 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.
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.
🔨 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.
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.
- Finally, you will get a drop-down box at the side of the G6 box where you may find the headings of the dataset.
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.
- 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.
- Finally, A drop-down box will appear beside the cell. Clicking the dropdown box you will get the options for Region.
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.
🔨 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.
- Now, we need to Fill the Handle.
- Finally, we get the Sales Persons that work in regions B and C.
📄 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.
