In Excel, sometimes you may need to generate a list based on criteria. Today we are going to show you how to generate a list based on criteria. For this session, we are using Excel 365, though it’s recommended to use this version, feel free to use yours.

First things first, let’s get to know about the dataset that is the base of our examples.

Here we have a dataset of several people from different locations along with their vehicles. Using this data, we will form a list based on criteria.

Note that this is a basic table with dummy data to keep things simple. In a practical scenario, you may encounter a much larger and more complex dataset.

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the following link.

## Generate List Based on Criteria

For example, we will create a list of people based on their region.

Since it’s a small dataset we know there are 4 regions. We stored the regions’ names and will find the list based on the region.

### 1. Using INDEX-SMALL Combination to Generate List

Here we need a list, so our formula should be one that will retrieve the multiple values from the table. For that task, we can use a combination of **INDEX **and **SMALL** functions.

To know these functions, check these articles: **INDEX, SMALL.**

Along with these two, we will need a few helper functions, **IF**, **ROW** and **IFERROR**. Check the articles for further information: **IF, ROW, IFERROR.**

Let’s explore the formula

`=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")`

Here every function has its purpose. The **INDEX** function returns the value from the array **B2:B12** (Name column) and the big **SMALL** portion provides the row number, that is to be fetched.

**IF,** within the **SMALL,** checks whether the criteria are matched or not, and the **ROW **function iterates over the cells of the column.

Then the outer **ROW** denotes the k-th value for the **SMALL** function. Together these functions return the row number and **INDEX **returns the result.

**IFERROR** to deal with any error that may arise from the formula.

Drag down you will get all the people from the given region.

Similarly, write the formula for the other regions (the formula is the same, shift the cell only).

#### An Alternative INDEX-SMALL Combination

We can write the formula in an alternative way. The functions used for the formula are going to be the same as previous. Only the presentation will be different.

Let’s see the formula

` =IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")`

Again, you need to press **CTRL + SHIFT + ENTER** for executing the formula.

There is a slight difference between these two formulas, can you differentiate them?

Yes, in our earlier formula, we have subtracted 1 at the very end of the **SMALL **portion, but here we have subtracted 1 within the **IF** portion.

The purpose of subtracting 1 is channelizing to the proper row number. Earlier we have done that at last, here did that earlier and proceed to the further operation.

Write the formula for the other criteria to complete the list.

**Read More: How to Make a List within a Cell in Excel (3 Quick Methods)**

### 2. Using AGGREGATE Function to Generate List

Excel provides you a function called **AGGREGATE **that you can use to perform various tasks. Here we can use the function to generate a list based on criteria.

The **AGGREGATE **function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc.

The syntax for the **AGGREGATE **function is as follows:

`AGGREGATE(function_number,behavior_options, range)`

**function_number: **This number specifies which calculation should be made.

**behavior_options: **Set this using number. This number denotes how the function will behave.

**range: **Range you want to aggregate.

The **AGGREGATE **function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function numbers

Function | Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

To know more about the function, visit the **Microsoft Support** site.

Now let’s see the formula,

`=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")`

Here along with the **AGGREGATE **function, we have used **INDEX**. **INDEX **holds the array that returns values based on matches found at the later part of the formula.

You can see, that we have used **15** as the *function_number* in **AGGREGATE**. From the above table, you can see **15** calls for the **SMALL** function operation. Now can you relate?

Yes, we have executed the **INDEX-SMALL** formula in the manner of the **AGGREGATE** function.

**6** for the behavior option, which denotes **ignore error values**.

Write the formula for the rest of the values.

**Similar Readings**

**How to Make a To Do List in Excel (3 Easy Methods)****Creating a Mailing List in Excel (2 Methods)****How to Make a Numbered List in Excel (8 Methods)**

### 3. Generate Unique List Using INDEX-MATCH-COUNTIF

We can create a unique list based on criteria. For that, we can use the combination of **INDEX**, **MATCH**, and **COUNTIF**.

**COUNTIF **counts cells in a range that meets a single condition. And **MATCH** locates the position of a lookup value in a range. To more about these functions visit these articles:** ****MATCH, COUNTIF. **

Let’s explore the formula

`=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") `

In this formula: **B2:B12** is the column range that contains the unique values you want to extract from, **C2:C12** is the column that contains the criterion you are based on **G2** indicates the criterion.

Within the **MATCH** function, we provided 0 as the *lookup_array,* and for *lookup_range* we have used the **IF** portion containing **COUNTIF**. So, this portion returns the value as long as 0 is found. The value here works as the row number for **INDEX**.

Drag it down and you will find all the unique values.

Don’t forget to utilize the **CTRL+SHIFT + ENTER **to execute the formula.

This was an honorable mention of the approaches to generate a unique list. Follow this article to know about generating a **unique list based on criteria**.

### 4. Using FILTER Function to Generate List Based on Criteria

If you are using Excel 365, then you can perform the task with a single built-in function called **FILTER**.

The **FILTER** function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article: **FILTER**.

Now, our formula will be the following one,

`=FILTER($B$2:$B$12,$C$2:$C$12=G$2)`

**B2:B12** is the array that is to be filtered. Then we have provided the condition, based on what we will generate the list.

Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.

**Read More: ****How to Make Alphabetical List in Excel (3 Ways)**

## Conclusion

That’s all for today. We have listed several ways to generate a list based on criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we have missed here.

Not a single one of these formulas work. I have used them all in the practice workbook, outlined exactly as you present and none of them work.

Did you download the working file? We use Microsoft 365 for making our Excel tutorials. Please download the workbook and let me know whether the system works or not.

Thanks.

For this formula, =IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), “”), 0)),””) is it possible to create the list based on multiple criteria? Eg, names by region based on Motor Vehicles

Thanks for your query.

Yes, it is possible to apply multiple criteria. Not sure how your data looks, but according to your query, I have tried to reorganize it as follows to categorize names by region based on Vehicle.

=INDEX(B5:B15,MATCH(1,(B18=$D$5:$D$15) * (C18=$C$5:$C$15),0))Is it possible to put all the names in one cell?

I have the use the following formula for this case.

=TEXTJOIN(“, “,TRUE,IF(F5=$C$5:$C$15,$B$5:$B$15,””))Wow. Very handy. And very nicely done! kudos

Thanks for your appreciation. It means a lot.

Can I use this if I want to search row 23 for the value “2” in order to obtain the information from Row 6? I would want it to provide a list just as it is above too so no repeat values.

How do you modify the formula if your ranges does not start on row 1?

For example your data start at row 6. Then none of the formulas is working properly.

Unfortunately for some strange reasons, the formulas with the INDEX function don’t behave properly while starting from any other row but row 1. But luckily you have some other alternatives like the FILTER function in case your dataset start from row6

Can you modify the formula for a row search. The value I am searching for is “2” in row 23. The value I want returned is in Row 6. I would want it to create a list just as displayed above each time a 2 is found in row 23 of the values in row 6. Is this possible?

You can apply the INDEX – MATCH functions combination to find out whether the value is matching with “2 ” or not in row 23 and then, use the INDIRECT function to retrieve the matched value with the value in row 6.