Table of Contents
To create a list based on criteria in Excel, you must first create a table with the data you wish to analyze. Then, you must set up the criteria that you would like to use to filter the results. Once the criteria have been established, you can use the FILTER or advanced filter functions to narrow down the results to the specific criteria you have chosen. Finally, you can copy and paste the filtered results to another worksheet to create the list you are looking for.
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.