Excel FILTER Function — Crash Course

Purpose: Return all rows that match a condition.
Unlike VLOOKUP/XLOOKUP, FILTER returns multiple results by design.

Syntax

FILTER(array, include, [if_empty])

Step 1 — Select the Data to Filter (array)

This is the table or range you want results pulled from.

You can:

• Highlight a range like A2:F200
• Or use a table like Table1 (recommended)

Step 2 — Define the Condition (include)

This is the rule Excel uses to decide which rows to return.

Think: Which rows do I want to keep?

Example 1 — Basic Filter

Return all rows where the product is 85% Dark Bars:

=FILTER(Table1, Table1[Product]="85% Dark Bars")

Excel returns every matching row.

Example 2 — Dynamic Filter (Better)

Make the filter value changeable from a cell.

If cell R2 contains a product name:

=FILTER(Table1, Table1[Product]=R2)

Now you can change R2 to instantly update the results.

Why FILTER is Powerful

• Returns all matches, not just the first
• Replaces complex combinations of VLOOKUP/XLOOKUP workarounds
• Works great with Excel Tables
• Fully dynamic when tied to a cell reference
• Cleaner and easier than advanced filters or pivot tables for simple needs

Optional: Handle Empty Results

Avoid errors if no matches exist:

=FILTER(Table1, Table1[Product]=R2, "No results found")

When to Use FILTER

Use FILTER when:

• You need every instance of a value
• You want results to auto-update based on a cell
• You want a fast, clean way to subset a table

If you only need one value → use XLOOKUP.
If you need all values → use FILTER.