How to Generate List Based on Criteria in Excel

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.

Data - Excel Generate List Based On Criteria

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 complex dataset.

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.

Data - Criteria - Excel Generate List Based On Criteria

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

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),"")

INDEX-SMALL formula - Excel Generate List Based On Criteria

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.

First list - Excel Generate List Based On Criteria

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

Complete the lists - Excel Generate List Based On Criteria

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.

INDEX-SMALL formula 2 - Excel Generate List Based On Criteria

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.

Complete lists 2 - Excel Generate List Based On Criteria

2. Using the AGGREGATE Function

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),"")

INDEX-AGGREGATE formula - Excel Generate List Based On Criteria

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

INDEX-AGGREGATE - Excel Generate List Based On Criteria

Write the formula for the rest of the values.

INDEX-AGGREGATE complete lists - Excel Generate List Based On Criteria

3. 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)),"") 

INDEX-MATCH formula - Excel Generate List Based On Criteria

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

INDEX-MATCH formula drag down - Excel Generate List Based On Criteria

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

Complete list (INDEX-MATCH) - Excel Generate List Based On Criteria

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

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)

FILTER - Excel Generate List Based On Criteria

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.

FILTER - Complete list - Excel Generate List Based On Criteria

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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo