Automatic Interpolation and Range Finder

OK, you totally lost me there

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi -

So I simplified this by skipping the interpolation part. You are really looking for the row of the value in column A equal to or less than T1 and the row of the value in column A equal to or greater than T2.

So, I used SUMPRODUCT to AND the conditions to find the row that meets the first criteria, and likewise for the second criteria. The formula to find the first row is:

=SUMPRODUCT((--($A$2:$A$31<=$D$2))*(--($A$3:$A$32>$D$2))*ROW($A$2:$A$31))+1

The upper end row is:

=SUMPRODUCT((--($A$2:$A$31<=$E$2))*(--($A$3:$A$32>$E$2))*ROW($A$2:$A$31))+1

Finally, I use INDIRECT to build the address range to search and finally MAX to search that range for the maximum value. That formula looks like:

=MAX(INDIRECT("B"&TEXT(D5,"###")&":B"&TEXT(E5,"###"),1))

I have attached your worksheet with these modifications.

Hope this helps.