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 more 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 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.
” title=”
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.