Forum Discussion

Marc_Kim's avatar
Marc_Kim
Copper Contributor
Sep 25, 2023

How can get a value of the first row according to filter selection?

Hi all,   I would like to get first row of text every time I filter row.    e.g. when Update Period selected 2023-06, then capture the first row# 22. when Update Period selected 2023-07, then ca...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 26, 2023

Marc_Kim 

Slightly depends on Excel version you use, practically for any one:

Also with Helper column with formula as

=AGGREGATE(3,5,B3)

It returns zero for hidden rows and 1 for visible one.

With that in F2

=INDEX( data, MATCH(1, helper, 0), 1)

and similar in G2, etc. Here main range is named as data and helper accordingly.