Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Generate List Based on Criteria in Excel (5 Methods)

In this article, we will discuss methods to generate list based on criteria in Excel. This will allow users to get the list of data from a dataset according to their requirements. Excel does not offer any direct method to generate such list. We will combine multiple functions in order to do generate the list.


Practice Workbook

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


5 Ways to Generate List Based on Criteria in Excel

In this article, we will discuss 5 methods to generate list based on criteria in Excel. Firstly, we will combine the INDEX and SMALL functions to do the task. Secondly, we will use the AGGREGATE function to get the job done. Thirdly, we will use the coombination of the INDEX, MATCH, and COUNTIF functions. Fourthly, we will use the FILTER function . Finally, we will apply the TEXTJOIN function to generate list based on criteria in Excel. 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.

excel generate list based on criteria


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.Along with these two, we will need a few helper functions, IF, ROW and IFERROR.

Steps:

  • Firstly, seelct the F5 cell and type,
=IFERROR(INDEX($B$5:$B$12,SMALL(IF($C$5:$C$12=F$4,ROW($B$5:$B$12)),ROW(1:1))-4,1),"")
  • Then, press Enter.

wrting formula to generate list based on criteria excel

  • As a result, we will get the name asscoiated with the region.
  • Now, move the cursor to the righ to autofill the values for the rest of the regions.

autofill formula to generate list based on criteria excel

  • After that, lower the cursor from the F5 cell to autofill for New York region.

  • Again, move the cursor to the right to autofill the values for the rest of the regions.

Formula Breakdown

  • IF($C$5:$C$12=F$4,ROW($B$5:$B$12)):  The ROW($B$5:$B$12) returns an array of values of rows, {5;6;7;8;9;10;11;12}. Again, $C$5:$C$12=F$4 expression returns an arry of TRUE and FALSE the {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. So, finally the IF function returns the values from the {5;6;7;8;9;10;11;12} array which matches the TRUE values from the {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} array and keeps the rest of the values FALSE.
    • Output : {5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
  • SMALL({5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROW(1:1)): It becomes SMALL({5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1}). The SMALL function returns the 1st smallest value from the {5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} array. So, the output will be {5}.
    • Output: {5}.
  • INDEX($B$5:$B$12,{5}-4,1): The INDEX($B$5:$B$12,{5}-4,1) becomes INDEX($B$5:$B$12,1,1). The INDEX function will return the first value in the $B$5:$B$12 range which is Mac.
    • Output: “Mac”
  • IFERROR(INDEX($B$5:$B$12,SMALL(IF($C$5:$C$12=F$4,ROW($B$5:$B$12)),ROW(1:1))-4,1),””): The entire formula becomes IFERROR(“Mac”,””). The IFERROR function will return Mac.
    • Output: Mac

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 AGGREGATE function does several tasks so numbers of functions are predefined within it.

Steps:

  • To begin with, choose the F5 cell and type,
=IFERROR(INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1),"")
  • Then, hit Enter.

using the aggregate function to generate list based on criteria excel

  • Conseqeuntly, we will get the name asscoiated with the region.
  • Next, slide the cursor to the righ to autofill the values for the remaining regions.

  • Then, move the cursor down from the F5 cell to autofill for New York region.

  • Finally, place the cursor to the right to autofill the values for the rest of the regions.

Formula Breakdown

  • IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4): The formula becomes IF({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{5;6;7;8;9;10;11;12}). After deducting 4 from each values of {5;6;7;8;9;10;11;12} the set becomes {1;2;3;4;5;6;7;8}. Finally, the IF function will return {1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
    • Output: {1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
  • AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)): The number 15 in the AGGREGATE denotes the SMALL function. So the expression becomes SMALL({1;2;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1}). And the expression will return {1}.
    • Output: {1}
  • INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1): It will become INDEX($B$5:$B$12,{1},1). The INDEX function will return the first value of the $B$5:$B$12 range which is Mac.
    • Output: “Mac”
  • IFERROR(INDEX($B$5:$B$12,AGGREGATE(15,6,IF($C$5:$C$12=F$4,ROW($B$5:$B$12)-4),ROW(1:1)),1),””): Finally, this expression will become IFERROR(“Mac”,””). Thus the IFERROR function will return Mac.
    • Output: Mac

Similar Readings


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

Steps:

  • To start with, seelct the F5 cell and type,
=IFERROR(INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F5:F5, $B$5:$B$12), ""), 0)),"")
  • Next, press the Enter button.

using index, match and countif functions to generate list based on criteria excel

  • Conseqently , we will find the name asscoiated with the region.
  • Then, place the cursor to the righ to autofill the values.

  • Thereafter, move the cursor down from the F5 cell to autofill for New York .

autofill formula to generate list based on criteria excel

  • At last, slide the cursor to the right to autofill the values for the remaining regions.

Formula Breakdown

  • COUNTIF(F4:F4, $B$5:$B$12): The expression becomes {0;0;0;0;0;0;0;0}. As the COUNTIF function can not find any of the values in the $B$5:$B$12 range from the F4:F4.
    • Output: {0;0;0;0;0;0;0;0}
  • IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”):  The expression becomes {0;0;””;””;””;””;””;””}. As the F$4=$C$5:$C$12 returns an array like this- {TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. 
    • Output: {0;0;””;””;””;””;””;””}.
  • MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12)): This expression becomes MATCH(0, {0;0;””;””;””;””;””;””}). The MATCH function matches with the first value of the {0;0;””;””;””;””;””;””} set and will return 1.
    • Output: 1
  • INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”), 0)): The expressioin will become INDEX($B$5:$B$12,1). The INDEX function will return the first value of the $B$5:$B$12 range that is Mac.
    • Output: “Mac”.
  • IFERROR(INDEX($B$5:$B$12, MATCH(0, IF(F$4=$C$5:$C$12, COUNTIF(F4:F4, $B$5:$B$12), “”), 0)),””): This expression becomes IFERROR(“Mac”,””). So, the IFERROR function will return Mac.
    • Output: Mac

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. In this method, we will use this function to generate list based on criteria in Excel.

Steps:

  • At the beginning, choosse the F5 cell and enter the following,
=FILTER($B$5:$B$12,$C$5:$C$12=F$4)
  • Now, hit the Enter button.

using filter function to generate list based on criteria excel

  • As a result , we will get all the names assigned to that region.
  • Then, move the cursor to the righ to autofill the values for the rest of the regions.


5. Applying TEXTJOIN Function

In this last method, we will use the TEXTJOIN function to generate list based on criteria. The TEXTJOIN function joins text by using a delimiter. We will take the help of the IF function to get the list from the dataset using the TEXTJOIN function.

Steps:

  • Firstly, select the G5 cell and enter the following,
=TEXTJOIN(", ",TRUE,IF(F5=$C$5:C12,$B$5:$B$12,""))
  • After that, press Enter.

applying textjoin function to generate list based on criteria excel

  • As a result, we will get the names for that particular region separated by comma.
  • Finally, lower the cursor down to the last cell to aurtofill the values.

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


Conclusion

In this article, we have discussed 5 ways to generate list based on criteria in Excel. 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.


Further Readings

Shakil Ahmed

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.

15 Comments
  1. 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.

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

    • Reply
      Naimul Hasan Arif Aug 17, 2022 at 12:16 PM

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

  3. Is it possible to put all the names in one cell?

    • Reply
      Naimul Hasan Arif Aug 17, 2022 at 3:02 PM
      Definitely possible. You just need to concatenate the values in a cell, can use the TEXTJOIN function to do so.

      I have the use the following formula for this case.

      =TEXTJOIN(“, “,TRUE,IF(F5=$C$5:$C$15,$B$5:$B$15,””))

  4. Wow. Very handy. And very nicely done! kudos

    • Reply
      Naimul Hasan Arif Aug 21, 2022 at 12:01 PM

      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.

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

    • Reply
      Naimul Hasan Arif Aug 17, 2022 at 5:48 PM

      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

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

    • Reply
      Naimul Hasan Arif Aug 21, 2022 at 12:05 PM

      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.

  7. Hi,

    When I reference criteria from another worksheet, it generates an error – any thoughts?

    • Hi RAV,
      It would be great if you share your Excel workbook. Because this formula works fine with criteria from other worksheets in our workbook.
      See the screenshot below.

      In Sheet3, we inserted the formula and gave all the arguments from Sheet2.
      =IFERROR(INDEX('INDEX - SMALL Formula'!$B$2:$B$12,SMALL(IF('INDEX - SMALL Formula'!$C$2:$C$12=G$4,ROW('INDEX - SMALL Formula'!$B$2:$B$12)-1),ROW('INDEX - SMALL Formula'!1:1)),1),"")
      And, it’s working without any errors. So, there must be another problem with your workbook. So, please share it with us thus we can solve your issue.
      If there is any other problem regarding Excel, you can let us know. Also, follow our website, ExcelDemy, a one-stop Excel solution provider to explore more. Happy Excelling.

Leave a reply

ExcelDemy
Logo