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:

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:

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