You can use the following formula in Excel to find all values that match specific criteria:
=FILTER(A2:A13,B2:B13=E1,"")
This particular formula returns all of the values in the range A2:A13 where the corresponding value in the range B2:B13 is equal to the value in cell E1.
The following example shows how to use this formula in practice.
Example: Find All Values that Match Criteria in Excel
Suppose we have the following dataset in Excel that contains information about the sales made by various employees at some company:
Suppose we would like to return the names of every employee that has exactly 10 sales.
We can type the following formula into cell D1 to do so:
=FILTER(A2:A13,B2:B13=E1,"")
The following screenshot shows how to use this formula in practice:
The formula returns the names Andy, Henry and Luke, which are all of the names of the employees that have exactly 10 sales.
Note that we could also use the following formula to return the names of all employees that have more than 20 sales:
=FILTER(A2:A13,B2:B13>E1,"")
The following screenshot shows how to use this formula in practice:
The formula now returns all of the names of the employees that have more than 20 sales.
Note: You can find the complete documentation for the FILTER function in Excel here.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use MAX IF with Multiple Criteria
Excel: How to Pull Data from Another Sheet Based on Criteria
Excel: Use COUNTIF with Multiple Criteria in Same Column
