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) )
) )

SergeiBaklan

Diamond Contributor

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) )
) )

Harun24HR

Bronze Contributor

Oct 10, 2025

Yes, AGGREGATE() has capability to handle errors and hidden cells. Thank for another response. Simpler version of formula is

=LET(arr,A2:B11,FILTER(arr,MAP(TAKE(arr,,1),LAMBDA(x,AGGREGATE(3,5,x)))))

Forum Discussion

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

Resources