# 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:

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:

## One Reply to “Excel: How to Create a List Based on Criteria”

1. StanStrong says:

Great – had to look elsewhere to find out that I needed CRTL-SHIFT ENTER because it’s an array formula. I often forget that.