FILTER

What it does

FILTER(array, include, [if_empty])

FILTER(array, include, [if_empty]) keeps the slices of array for which the corresponding element of include is true, and drops the rest. Order is preserved: the surviving slices come back in their original sequence.

The word doing the work is slices. FILTER does not select individual cells; it selects whole rows or whole columns, and which axis it filters is decided by the shape of include, not by an argument:

• include shaped like a single column with one entry per row of array — rows are filtered;
• include shaped like a single row with one entry per column of array — columns are filtered.

That is the single most important thing to understand about this function, and it is the source of most #VALUE! results people meet with it: the criterion array must be parallel to the axis you mean to filter, and getting it transposed does not filter the other way, it fails.

include is an array of truth values, typically produced by a comparison over a range — (A2:A100="North") — which is itself an elementwise lifted operation (coercion and lifting). Combining criteria is arithmetic on those arrays: * for AND, + for OR. FILTER supplies no criteria language of its own, which is why it composes so freely and why it has no equivalent of the COUNTIF family's criteria strings.

Arguments

Notes on each:

• array is prepared under ValuesOnlyPreAdapter, so a reference is resolved to its values before the function runs; the result is values and carries no reference structure.
• include must have exactly one entry per row (or per column) of array. Non-logical values are interpreted by ordinary to-logical rules — zero is false, non-zero true — but which contexts accept text logicals is per-family policy in the shared model rather than a universal rule, and the Handbook has not established FILTER's policy.
• if_empty is the argument people omit and then wish they had not. Without it, an empty result is an error (below). With it, FILTER(…, …, "") or FILTER(…, …, "none") degrades gracefully. It accepts any scalar; whether it accepts an array is not established here.

Try it

Quirks

Result and edge cases

Return kind: Array — the surviving rows (or columns), spilled. The result's width equals array's width when filtering rows, and its height equals array's height when filtering columns.

• Every row passes — the result equals the input.
• Exactly one row passes — a one-row array, not a scalar. Downstream code that expects a scalar should not assume; use @ or a reducer.
• No row passes — the empty case, handled by if_empty or by #CALC! (below).
• Errors inside include are a different matter: an error in the criterion array has no truth value. What FILTER does with one is not established here, and it is a case real workbooks hit constantly, because criterion arrays are usually computed.
• A FILTER over a whole-column reference carries the whole column's blanks into the criterion, which is why the idiom is usually paired with a non-blank test.

Errors

Microsoft's page documents two conditions:

• #CALC! when no entries in include are true and if_empty is omitted. This is the empty-array case: the value model admits no empty array at the published boundary (value universe), so "nothing matched" has to surface as an error unless you supplied a substitute.
• #VALUE! when include is not of a dimension compatible with array.

The #CALC! case is the one that changes how formulas must be written: any FILTER whose criterion depends on data should either carry an if_empty or be wrapped. Note that FILTER is unusual in the reshape family for offering if_empty at all — DROP and its siblings give you #CALC! with no substitute.

An error value supplied as an argument propagates under the universal rule in coercion and lifting.

How to implement

Notes for implementers

• Axis inference from include's shape is the crux. The rule has to distinguish a column-shaped criterion from a row-shaped one, and to reject anything that is neither — with #VALUE!, per the documentation. A 1×1 criterion is ambiguous for a 1×1 array and needs a declared policy.
• The empty result must be intercepted before the array is constructed, exactly as in DROP: a zero-row array is not representable, so the if_empty-or-#CALC! decision belongs at the point where the survivor count is known.
• Truth-value interpretation of include is per-family policy, not engine law. The shared model is explicit that to-logical behaviour is distributed across functions rather than centralized, so an implementation must declare its rule for numbers, text, blanks and errors in the criterion rather than inheriting one.
• Stability. Preserving input order is part of the semantics, not an implementation accident; any parallel or partitioned implementation must be order-preserving.

Rust code

2 filter_fn "FILTER" (2, 3) [R "array", R "include", R "if_empty"] |s, a, sv| {
    source_error(a)?;
    run(s, a, sv, k::eval_filter_prepared)
}

fn value_from_if_empty_arg(arg: &CalcValue) -> CalcValue {
    match arg.core_ref() {
        CoreRef::Missing | CoreRef::Empty => {
            CalcValue::text(crate::value::ExcelText::from_utf16_code_units(Vec::new()))
        }
        _ => arg.clone(),
    }
}

fn build_filter_mask(
    include: &CalcArray,
    target_shape: ArrayShape,
) -> Result<(bool, Vec<bool>), DynamicArrayReshapeEvalError> {
    if include.shape().cols == 1 && include.shape().rows == target_shape.rows {
        let mask = (0..include.shape().rows)
            .map(|row| {
                include
                    .get(row, 0)
                    .map_or(Ok(false), |cell| match cell.core_ref() {
                        CoreRef::Logical(b) => Ok(b),
                        CoreRef::Number(n) => Ok(n != 0.0),
                        CoreRef::Empty | CoreRef::Missing => Ok(false),
                        CoreRef::Error(code) => Err(DynamicArrayReshapeEvalError::Preparation(
                            CoercionError::WorksheetError(code),
                        )),
                        _ => Err(DynamicArrayReshapeEvalError::InvalidIncludeShape),
                    })
            })
            .collect::<Result<Vec<_>, _>>()?;
        return Ok((false, mask));
    }
    if include.shape().rows == 1 && include.shape().cols == target_shape.cols {
        let mask = (0..include.shape().cols)
            .map(|col| {
                include
                    .get(0, col)
                    .map_or(Ok(false), |cell| match cell.core_ref() {
                        CoreRef::Logical(b) => Ok(b),
                        CoreRef::Number(n) => Ok(n != 0.0),
                        CoreRef::Empty | CoreRef::Missing => Ok(false),
                        CoreRef::Error(code) => Err(DynamicArrayReshapeEvalError::Preparation(
                            CoercionError::WorksheetError(code),
                        )),
                        _ => Err(DynamicArrayReshapeEvalError::InvalidIncludeShape),
                    })
            })
            .collect::<Result<Vec<_>, _>>()?;
        return Ok((true, mask));
    }
    Err(DynamicArrayReshapeEvalError::InvalidIncludeShape)
}

pub fn eval_filter_prepared(args: &[CalcValue]) -> Result<CalcValue, DynamicArrayReshapeEvalError> {
    let array = materialize_array_arg(&args[0]);
    let include = materialize_array_arg(&args[1]);
    let (filter_cols, mask) = build_filter_mask(&include, array.shape())?;

    if !filter_cols {
        let selected_rows: Vec<usize> = mask
            .iter()
            .enumerate()
            .filter_map(|(idx, keep)| if *keep { Some(idx) } else { None })
            .collect();
        if selected_rows.is_empty() {
            return if let Some(if_empty) = args.get(2) {
                Ok(match if_empty.core_ref() {
                    CoreRef::Array(array) => CalcValue::array(array.clone()),
                    _ => value_from_if_empty_arg(if_empty),
                })
            } else {
                Err(DynamicArrayReshapeEvalError::EmptyArrayResult)
            };
        }
        let mut cells = Vec::with_capacity(selected_rows.len() * array.shape().cols);
        for row in selected_rows {
            cells.extend(array.row_slice(row).expect("validated row").iter().cloned());
        }
        return build_array(cells.len() / array.shape().cols, array.shape().cols, cells);
    }

    let selected_cols: Vec<usize> = mask
        .iter()
        .enumerate()
        .filter_map(|(idx, keep)| if *keep { Some(idx) } else { None })
        .collect();
    if selected_cols.is_empty() {
        return if let Some(if_empty) = args.get(2) {
            Ok(match if_empty.core_ref() {
                CoreRef::Array(array) => CalcValue::array(array.clone()),
                _ => value_from_if_empty_arg(if_empty),
            })
        } else {
            Err(DynamicArrayReshapeEvalError::EmptyArrayResult)
        };
    }
    let mut cells = Vec::with_capacity(array.shape().rows * selected_cols.len());
    for row in 0..array.shape().rows {
        for col in &selected_cols {
            cells.push(array.get(row, *col).expect("validated col").clone());
        }
    }
    build_array(array.shape().rows, selected_cols.len(), cells)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SORT and UNIQUE are the other two members of the classic dynamic-array trio; FILTER then SORT then UNIQUE is the canonical query pipeline, and all three live in the same reference-implementation module.
• CHOOSEROWS / CHOOSECOLS select by index where FILTER selects by predicate. When the predicate is really "positions 1, 4 and 9", the index functions say it directly.
• TAKE / DROP select contiguous edge bands, which is the other common special case.
• Advanced Filter and the database functions (DSUM and family) are the pre-dynamic-array answers to the same question, with criteria expressed as a range of criteria rows rather than as a boolean array.
• XLOOKUP answers "the one matching row" where FILTER answers "all matching rows". Reaching for FILTER and then taking the first row is a common but weaker idiom, because it computes everything to use one.
• SUMIFS / COUNTIFS aggregate under criteria without materializing the matching rows; when you only want a total, they do less work and produce no spill.

Named variants

No named variants are recorded yet.