Course / Modern Excel

FILTER: rows that match

Pull out matching rows with one formula, take the condition from a cell, combine tests with * and +, and handle "no results" gracefully. Intermediate · 6 min · FILTER, AND with *, OR with +, if_empty

FILTER returns every row that passes a test, as a live table that spills:

=FILTER(array, include, [if_empty])

• array: the rows to return
• include: a test with one TRUE or FALSE per row, such as C2:C13>=2
• if_empty: what to show when no rows pass

Unlike the filter buttons on a table, the results update the moment the data or the test changes. You'll help a friend search the flat listings in A1:D13, using the settings in G1:G3.

Steps

1. Your friend needs room for a home office. In F6, list every flat with at least 2 bedrooms: =FILTER(A2:D13, C2:C13>=2)

2. Typing the condition into the formula means editing it every time it changes. Edit F6 to show the flats in the area named in G1: =FILTER(A2:D13, B2:B13=G1)

3. Your friend would also consider the area in G2. To keep rows that pass either test, add the tests together with +: =FILTER(A2:D13, (B2:B13=G1)+(B2:B13=G2))

4. Back to one area, but now there is a budget. Rows must be in the G1 area and cost no more than G3. Multiply the tests with : =FILTER(A2:D13, (B2:B13=G1)(D2:D13<=G3))

5. What if nothing fits? Lower the budget in G3 to 900: F6 now shows #CALC! because no rows match. Fix it by adding a third argument, "No matches", to the FILTER in F6.

Remember

• =FILTER(array, include) returns every row where the test is TRUE.
• Put conditions in cells so they can change without editing the formula.
• Multiply tests for AND: (test1)*(test2). Add them for OR: (test1)+(test2).
• The third argument, if_empty, replaces #CALC! when nothing matches.

Previous: Spilling arrays: UNIQUE and SORT · Next: SORTBY, SEQUENCE and LET