FInd only the first cell in a list that meets a criterion?

[kt13968]

I have what I thought would be a very simple problem. It can easily be done manually but would be very time consuming with large data sets.

In a vertical list, I want to return the number of items between the item at the top of the list and the first item in the list with a value greater than the top item.

The data is daily time series. I want to know how many days ago today’s value was last matched or exceeded. I have attached a screenshot with an example.

I cannot find a solution. I have tried the MATCH function but to no avail.

Please help!

[JohnSUN]

What about =SUMPRODUCT(MATCH(1;B3:B15>B2;0))?

[kt13968]

Thank you, sir!

This seems to work.

[Vrolik]

AGGREGATE function

=AGGREGAT(15;6;B3:B10/(B3:B10>B2);1)

or

=MINIFS(B3:B10;B3:B10;">"&B2)

[kt13968]

Thanks for the very quick suggestions!

However, they do not look specifically for the first instance in the list that meets the rule.

For example, if you change one of the cell values in B3:B8 and make it >B2, these formulas still return B9.