Excel: How to Create a List Based on Criteria


You can use the following basic formula to create a list based on criteria in Excel:

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

This particular formula creates a list of values in the range A2:A12 where the value in the range B2:B12 is equal to the value in cell B2.

The following examples show how to use this formula in practice with the following dataset in Excel:

Example 1: Create List Based on One Criteria in Excel

We can use the following formula to create a list of players who are on the Mavs team:

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

We can type this formula into cell E2 and then drag it down to the remaining cells in column E to create a list of players who are on the Mavs team:

Excel create list based on criteria

The result is a list of three players:

  • Andy
  • Bob
  • Frank

We can look at the original dataset to confirm that all three of these players are on the Mavs team.

Example 2: Create List Based on Multiple Criteria in Excel

We can use the following formula to create a list of players who are on the Mavs team and have a position of Guard:

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

We can type this formula into cell E2 and then drag it down to the remaining cells in column E to create a list of players who are on the Mavs team and have a position of Guard:

Excel create list based on multiple criteria

The result is a list of two players:

  • Andy
  • Frank

We can look at the original dataset to confirm that both of these players are on the Mavs team and have a position of Guard.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Extract Unique Values Based on Criteria
Excel: How to Randomly Select Cells Based on Criteria
Excel: How to Find Top 10 Values Based on Criteria

Leave a Reply

Your email address will not be published.