The MATCH formula in my picture returns a number (10), which is the distance between the top of the list and the next cell down that meets the criteria (in the example, the value in the 10th cell down is smaller than the first cell, so it returns “10”). That is what I want it to do. But what I am also looking for is…
…a formula that returns the VALUE of that cell (in the picture, that would be “1888”).
What formula would work for this?
Thanks in advance!
As you already know where the value is found via MATCH() you can feed the result to INDEX() to get the value for further processing. See the following tutorial:
Sorry, to clarify, the MATCH formula in my picture returns a number (10 in the above example) which is the distance between cell A2 and the first one that meets the criteria. That is what I want it to do. It does not return not a cell reference (e.g. A12 or something like that).
I know about the match and index functions already. The tutorial did not help me put the pieces I need together. Thanks for your response though.
Then use ROW() to find out where you are and add the distance. There is also INDIRECT to create your references…
Good luck
.
PS: I assumed A2 was choosen as starting point “nearest the top”. For other cells you need to repeat MATCH/lookup from top. But this you obviously know…
Hallo
=INDEX(A$3:A$99;MATCH(1;A$3:A$99<A2;0))
Perfect! Thank you very much.