I can't come up with a combination of functions in Google Sheets that would return the row offset of a cell from another in the same column that matches a given rule.
In the image below is an example using an invented function I named "FIRST_VALUE" to illustrate what I mean:
- FIRST_VALUE takes two inputs:
- A row number:
ROW()
is used in the example, the current row61
- A rule:
In the example it is">655"
- A row number:
- My invented function FIRST_VALUE would return
5
because the value655,79
is the first value in a row above row61
that is greater than655
and it is found in row56
which is5
rows above.
I have tried combinations of MATCH, LOOKUP, etc., but none have worked.
This question has also been posted on the Google community help forum: Special function in Google Sheet