Skip to main content
3 of 3
The "invented" function was causing confusion. But looking at the example and the prior efforts using MATCH/LOOKUP makes it unlikely this is a custom function question..
Blindspots
  • 7.5k
  • 5
  • 20
  • 38

Formula to calculate difference in rows between the current row and the first row above whose value matches a condition

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:

  1. FIRST_VALUE takes two inputs:
    1. A row number:
      ROW() is used in the example, the current row 61
    2. A rule:
      In the example it is ">655"
  2. My invented function FIRST_VALUE would return 5 because the value 655,79 is the first value in a row above row 61 that is greater than 655 and it is found in row 56 which is 5 rows above.

I have tried combinations of MATCH, LOOKUP, etc., but none have worked.

example showing 'invented function' detailed above

This question has also been posted on the Google community help forum: Special function in Google Sheet