Table of Contents
To create a list in Excel based on specific criteria, follow these steps:
1. Start by organizing your data into columns and rows.
2. Next, click on the “Data” tab and select “Filter” from the drop-down menu.
3. This will add filter arrows to each column.
4. Click on the filter arrow for the column you want to filter.
5. Select “Text Filters” or “Number Filters” depending on the type of data in the column.
6. Choose the criteria you want to filter by, such as “Greater Than” or “Contains”.
7. Enter the specific value or text you want to filter by.
8. Click “OK” to apply the filter and create a list based on your specific criteria.
9. You can also add multiple filters to further refine your list.
10. Once you have your desired list, you can copy and paste it into a new sheet or save it as a separate file.
Excel: 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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I create a list in Excel based on specific criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-create-a-list-in-excel-based-on-specific-criteria/
stats writer. "How can I create a list in Excel based on specific criteria?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-create-a-list-in-excel-based-on-specific-criteria/.
stats writer. "How can I create a list in Excel based on specific criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-create-a-list-in-excel-based-on-specific-criteria/.
stats writer (2024) 'How can I create a list in Excel based on specific criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-create-a-list-in-excel-based-on-specific-criteria/.
[1] stats writer, "How can I create a list in Excel based on specific criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I create a list in Excel based on specific criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.