Hi Excel Experts!
I just can't seem to figure out formulas that look at a range of cells (say A1:A50) and find 1) the value of that cell, and 2) the cell address of the first cell that meets Condition X (in this case, is equal or smaller than A1-Y, where Y is a constant).
The final result would be three columns. Column A = data. Column B = value of the first cell within the next 50 cells that is equal or less than X. Column B = address of the first cell within the next 50 cells that is equal or less than X.
I have a large data set, so there are tens of thousands of rows than need to be populated with these formulas!
I've been struggling with INDEX and MAX and COUNTIF functions, but I just can't get things to work.
EDIT: added clarification about Condition X