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

Question

Hi all,&nbsp;I would like to get first row of text every time I filter row.&nbsp;&nbsp;e.g. when Update Period selected 2023-06, then capture the first row# 22.when Update Period selected 2023-07, then capture the first row# 97.&nbsp;If I use offset function, it always capture row#22 no matter what I filter.&nbsp;Is there any tip for this pls?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

santhoshkunder · Answer

Marc_Kim&nbsp;- How about a helper column with COUNTIF formula?. Filter only 1 gives you the first result from the list of values.&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

Marc_Kim&nbsp;
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.
&nbsp;

patrick2788 · Answer

Marc_Kim&nbsp;A 365 solution.&nbsp;=LET(
    VisibleOnly, LAMBDA(element, IF(SUBTOTAL(3, element) = 1, element, "")),
    visible, MAP(Table1, VisibleOnly),
    filtered, FILTER(visible, TAKE(visible, , 1) &lt;&gt; ""),
    TAKE(filtered, 1)
)&nbsp;

Forum Discussion

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

Share

Resources