Table of Contents
You can use the following formula to list all matched instance of a value in Excel:
=FILTER(B2:B11, F2=A2:A11)
This particular formula looks up the value in cell F2 in the range A2:A11 and returns all matched values from the range B2:B11.
The following example shows how to use this syntax in practice.
Example: How to List All Matched Instances of Value in Excel
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to look up the name “Mavs” in the Team column and return the value in the Points column for all matched instances of this team name.
We can type the following formula into cell E2 to do so:
=FILTER(B2:B11, F2=A2:A11)
The following screenshot shows how to use this formula in practice:
Notice that the formula returns the points values for all rows that contain “Mavs” in the Team column.
We can verify this formula returned all matched values by manually identifying each row that contained “Mavs” in the Team column:
Note that in this example we returned all matched numerical values, but the FILTER function can also be used to return all matched text values as well.
Note: You can find the complete documentation for the Excel FILTER function .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: