4 Easy Ways to Generate List Based on Criteria in Excel

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.

Dataset - excel list generate based on criteria


approach

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.
=FILTER($B$6:$B$9,$C$6:$C$9=G$5)
  • 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.

FILTER - excel list generate based on criteria

πŸ”¨ 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.


approach

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.

=INDEX($B$6:$B$9, MATCH(0, IF(G$5=$C$6:$C$9, COUNTIF($G$5:$G5, $B$6:$B$9), β€œβ€), 0))
  • 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.

INDEX COUNTIF - excel list generate based on 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.


approach

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.

=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)
  • Hit the Enter key.

  • Use the Fill Handle icon to copy the formula for the remaining cells.
  • You will find the listed data gradually.

INDEX AGGREGATE - excel list generate based on criteria

πŸ”¨ 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.


approach

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.

=INDEX($B$6:$B$9,SMALL(IF($C$6:$C$9=G$5,ROW($B$6:$B$9)),ROW(1:1))-5,1)
  • 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.

INDEX SMALL - excel list generate based on criteria

πŸ”¨ 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.Β 

(Visited 40 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo