Excel: List All Matched Instances of Value


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:

Excel list all instances of value

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 here.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Compare Two Lists in Excel Using VLOOKUP
How to Find Unique Values from Multiple Columns in Excel
How to Filter Multiple Columns in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *