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
forgot to say, only works with late excel!!!
We are getting this functin is not valid In the formula help also there is no such function, Is it available only in latest version of excel?
Hi again Zach
I’ve just realised why I get the 6 random rows of data when I cleared the query (it’s because there are blanks in the column in the data table I am using). However, I would still be interested to know how to show a blank page when the query box is cleared.
Thanks. Richard
Hi Richard…In Excel, if you’re using a search or filter box (like a cell used for querying/filtering a data table), and clearing it causes random rows (like non-blank rows) to appear, you can prevent this by tweaking your formula to only show results when the query box is not empty.
Here’s a simple example using a helper formula with `FILTER`:
Let’s say:
* Your data is in columns A to D.
* Your query (like a search keyword) is in cell G1.
You might be using something like this:
“`excel
=FILTER(A2:D100, A2:A100=G1)
“`
To make it return a blank when G1 is empty, wrap it in an `IF`:
“`excel
=IF(G1=””, “”, FILTER(A2:D100, A2:A100=G1))
“`
This tells Excel:
“If G1 is empty, show nothing. Otherwise, run the filter.”
This should stop those random rows from showing when the query box is cleared.