Shakil Ahmed
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 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.
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),"")
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.
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),"")
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.
Write the formula for the rest of the values.
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)),"") 
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.
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
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.
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.
