Return a value of first & last row matching criterion in Google Sheets-CodePudding

I have 2 columns of numbers, the idea is to find the first & last value in column L that respects a criterion and return the value from the same row in column K. As the criterion is "higher than 99% of the max value in column L", I tried the MINIFS formula, but I cannot use this as a criterion.

I guess the solution will include the MATCH, INDEX formula but I cannot find the right combination

In this specific example, we want to return the value of the first column that has in column L a number higher than 0,99*max(L3:L62)(=3.0879...) so it should return 19 This will be verified for a couple rows until the value goes below the 99% again. This last row is 58.

Link to sheet : https://docs.google.com/spreadsheets/d/1MUkYDPoR1NxB8qWcYr_2Fp91FgUbnUOpfGd7EUuWCOg/edit?usp=sharing

CodePudding user response：

You can also try the following:

For first value

=Index(K3:K62;Min(IFERROR(1/(1/((Row(K3:K62)*(L3:L62>0,99*max(L3:L62)))))))-2)

For last value

=Index(K3:K62;Max(IFERROR(1/(1/((Row(K3:K62)*(L3:L62>0,99*max(L3:L62)))))))-2)

CodePudding user response：

Try

=query({K3:L62};"select Col1 where Col2 > "&substitute(to_text(0,99*MAX(L3:L62));",";".")&" limit 1";0)

query as app script needs US notation in values (dot instead of comma)

to get the last row

=query({K3:L62};"select Col1 where Col2 > "&SUBSTITUTE(to_text(0,99*MAX(L3:L62));",";".")&" order by Col1 desc limit 1";0)