How to filter visible cells by formula after applying filter to a dataset.

I have a dataset from A1:B11 (in real case many more). I apply a filter on dataset, for example filter data for Retailer1 and Retailer3. I want on a separate sheet to filter these visible cells using...

excel

Formulas and Functions

office 365

SergeiBaklan

Oct 10, 2025

Or with exactly same idea

= LAMBDA(range,
    LET(
        IsVisible, LAMBDA(v, AGGREGATE(3,5,v)),
        FILTER( range, TAKE( MAP(range, IsVisible ),,1) )
) )

Riny_van_Eekelen

Platinum Contributor

Oct 10, 2025

Alternatively, without OFFSET:

=LAMBDA(tbl,
    LET(
        arr, tbl,
        viz, BYROW(
            INDEX(arr, , 1),
            LAMBDA(r, SUBTOTAL(103, r))
        ),
        flt, FILTER(arr, viz),
        flt
    )
)

Harun24HR

Bronze Contributor

Oct 10, 2025

Fantastic! This seems better than earlier.

Forum Discussion

How to filter visible cells by formula after applying filter to a dataset.

Resources