Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. For achieving an organized dataset, you may feel the need to generate a list. Addressing the data manually is not only tiresome but also inefficient. So, in cases of extensive datasets, you should use Excel formulas to generate a list based on criteria in Excel. This article will show you four ways to generate an effective list based on criteria from an extensive dataset in Excel. It is applicable for multiple cells and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Generate List Based on Criteria in Excel with These 4 Approaches
The article is going to introduce four approaches in Excel to use formulas to generate lists based on criteria. The methods will help you understand the uses elaborately. The following discussion will cover the uses of the FILTER function. The INDEX function can generate list based on criteria as well with the help of the AGGREGATE function, the SMALL function and the COUNTIF function. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.
1. Using FILTER Function
The FILTER function lets you filter a range of data based on specified criteria. This method is the easiest and most convenient in case of extensive datasets.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell ID G6.
- Secondly, enter the following formula on cell G6.
- Thirdly, hit the Enter key.
- Fourthly, you will find the entire column of the table from the source data automatically.
- Fifthly, use the Fill Handle icon to copy the formula to the next column.
- Finally, you will find the entire table with the listed data automatically.
🔨 Formula Breakdown
FILTER($B$6:$B$9,$C$6:$C$9=G$5)
👉 Here, FILTER includes cells that are the same as cell G5, from the absolute array B6:B9.
Finally, you will generate the entire list.
2. Applying INDEX and COUNTIF Functions
The INDEX function is a versatile function that you can customize with the COUNTIF function and the MATCH function. You can generate a list with the INDEX function. Prior to that Excel needs to match the data according to the requirement of the IF and COUNTIF functions.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell ID G6.
- Now, enter the following formula on the cell G6.
- Next, hit the Enter key.
- Then, use the Fill Handle icon to copy the formula down the cells.
- Finally, you will get the table listed according to the criteria.
🔨 Formula Breakdown
INDEX($B$6:$B$9, MATCH(0, IF(G$5=$C$6:$C$9, COUNTIF($G$5:$G5, $B$6:$B$9), “”), 0))
👉 Here, COUNTIF counts absolute cell range B2:B9 from absolute cell range G5.
👉 Also, IF returns value from C6:C9 if it is same to cell G5. The rest formula depends on the COUNTIF return.
👉 Again, MATCH gets position of 0 from the range retrieved from the IF function. It gets the exact position output.
👉 So, INDEX returns the reference value from cell range B6:B9, depending on the returns of inner functions.
Finally, you can see class varied students listed accordingly.
3. Utilizing INDEX and AGGREGATE Functions
The AGGREGATE function is an important function. You can combine it with the INDEX, IF, and ROW functions to generate a list based on criteria in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell ID G6.
- Now, enter the following formula on cell G6.
- Hit the Enter key.
- Use the Fill Handle icon to copy the formula for the remaining cells.
- You will find the listed data gradually.
🔨 Formula Breakdown
INDEX($B$6:$B$9,AGGREGATE(15,6,IF($C$6:$C$9=G$5,ROW($B$6:$B$9)-5),ROW(1:1)),1)
👉 Here, ROW gets the row number of the given ranges separately. The return acts as an input of next function.
👉 Then, IF returns value from C6:C9 if it is same to cell G5.
👉 Again, AGGREGATE uses SMALL and PRODUCT functions by function number 15 and 6. Using these functions, you refer the data to a database or list.
👉 Also, INDEX returns the reference value from cell range B6:B9, depending on the returns of inner functions.
Finally, you can get data of each class as the output.
4. Merging INDEX and SMALL Functions
Like the aforementioned methods, this method also uses the INDEX function. Here, the SMALL function is incorporated with the IF and ROW functions. Merging all these functions, you can finally generate a list based on criteria in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell ID G6.
- Secondly, enter the following formula on cell G6.
- Hit the Enter key.
- Use the Fill Handle icon to copy the formula to the remaining cells.
- You will get the list based on the criteria finally.
🔨 Formula Breakdown
INDEX($B$6:$B$9,SMALL(IF($C$6:$C$9=G$5,ROW($B$6:$B$9)),ROW(1:1))-5,1)
👉 Here, ROW gets the row number of the given ranges separately. The return acts as an input of the next function.
👉 Here, IF returns value from C6:C9 if it is same to cell G5.
👉 Here, SMALL looks for the position of the ROW function. The source is from the cell range followed by the IF function.
👉 Here, INDEX returns the reference value from cell range B6:B9, depending on the returns of inner functions.
Finally, you can see class-varied students listed accordingly.
📄 Important Notes
🖊️ In few cases, you might get “Not Applicable(#N/A)” output for no results. You can add the IFERROR function in front of the formulas. It will show blank cells instead of warnings.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to generate a list based on criteria in Excel.
📌 Primarily, you can use the FILTER function easily. All you need to do is click on the ranges and copy.
📌 You can combine the INDEX and the AGGREGATE functions to work on list generation. The IF function confirms the criteria.
📌 You can merge the INDEX and the SMALL functions, along with the IF function as well to generate a list based on criteria.
📌 Finally, you can get the desired result by using the COUNTIF function and the MATCH function. The whole action completes with the help of the INDEX function.
Conclusion
To generate a list based on criteria in Excel, four methods are useful. You can use the FILTER function. You can add the INDEX function with the AGGREGATE function, the SMALL function, and the COUNTIF function to get the result as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
