Skip to content

Show only rows that match

Return every row where a column has a certain value, as a live list.

The formula

Argument separator
Excel · Excel 365 and Excel 2021
=FILTER(A2:D100, C2:C100="Open", "None")
Google Sheets
=IFERROR(FILTER(A2:D100, C2:C100="Open"), "None")

How it works

  1. A2:D100

    The rows and columns to return.

  2. C2:C100="Open"

    Keep rows where the status is Open. Combine tests with * for AND and + for OR.

  3. "None"

    What to show when nothing matches.

Example

A: TicketC: StatusResult (spills)
#101Open#101 … Open
#102Closed#103 … Open
#103Open

Tips

  • Leave enough empty cells below and to the right for the results, or you will see #SPILL!.