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.

**Contents**hide

## 📁 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.

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

**🔨 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.

**🔨 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.

**🔨 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.

**🔨 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.** **