FILTER function: extract rows matching multiple conditions

Extract dispatch-ready parts without hiding source rows

A bicycle workshop tracks five spare-part bins by part name (column A), inspection status (column B), and quantity on hand (column C). The bench lead wants a live list of parts whose status is Ready and whose quantity is at least 10.

Multiply boolean tests for AND criteria

Put Ready in E2 and 10 in F2. Inside FILTER, logical functions such as AND(B2:B6=E2, C2:C6>=F2) collapse an entire array to a single TRUE or FALSE, which will not filter row by row. Instead, wrap each comparison in parentheses and multiply them: TRUE × TRUE = 1, while any FALSE produces 0.

In a worksheet grid with empty cells below, the formula below spills both matching part names—Brake pad and Tire tube—downward automatically.

=FILTER(A2:A6,(B2:B6=E2)*(C2:C6>=F2),"No matching parts")

Summarize or inspect spilled results in a single cell

When you want a compact comma-separated list on a summary card (or inside the single-cell practice preview below), wrap FILTER in TEXTJOIN. Only rows 2 and 5 pass both tests: Spoke kit is Ready but has only 6 units, and Chain link has 25 units but is on Hold.

=TEXTJOIN(", ",TRUE,FILTER(A2:A6,(B2:B6=E2)*(C2:C6>=F2),"No matching parts"))

Handle empty results cleanly instead of triggering #CALC!

Raise the minimum quantity in F2 from 10 to 30 so no Ready rows qualify. Without FILTER’s optional third argument (if_empty), an empty dynamic filter returns #CALC!. Supplying "No matching parts" turns an empty result into a clear status message.

Keep spill ranges clear on the worksheet

When FILTER spills across multiple rows and columns on a worksheet, every destination cell must be empty; a stray note in the path blocks the output with #SPILL! in Excel. You can also pass FILTER into ROWS or INDEX—for example, INDEX(FILTER(A2:C6, ...), 2, 3) reads the quantity of the second matched row (14).

Further reading

• Microsoft: FILTER function