@dimexi, thanks for the feedback. Of the formula offerings, I think Jack's approach is the preferred one too. I'm glad you got something that works well for your application.
You expressed interest in the AGGREGATE function, so I'll offer some explanation of its use here because it might be handy to use for other purposes in the future.
Given two known pairs of (x,y) data...let's say (x1,y1) and (x3,y3), you'd like an interpolated value of y2 for some input of x2, where x1<=x2<=x3. The traditional point-slope formula can be derived by considering that all three points would lie on the same line, therefore any line segments connecting two of the points would have equal slopes, leading to an expression like this:
(y3-y1)/(x3-x1) = (y2-y1)/(x2-x1)
Then solving for y2, we get ===> y2 = (y3-y1)(x2-x1)/(x3-x1) + y1
My suggestion uses this explicit formula. Jack's and your original idea both make use of the more compact built-in FORECAST function, which essentially reduces to this same formula internally if only two pairs of (x,y) points are specified.
A common approach to finding a value in a list is to use INDEX/MATCH, which is found in Jack's solution: INDEX($B$3:$C$9, MATCH(G11,$B$3:$B$9,1)
The MATCH function uses the input value in cell G11 (treated as x2 in this example) and the "1" option will find the position of the largest x value in B3:B9 that is less than or equal to it. My implementation of AGGREGATE does something similar. Consider AGGREGATE(14,6,(ROW($A$2:$A$8)-ROW($A$1))/($A$2:$A$8<=E2),1) as an example:
ROW($A$2:$A$8) establishes an array of row numbers where the x-y values are found. This is equivalent to {2;3;4;5;6;7;8}, and since the idea is to use this construction as a list index, the starting position of the list must be taken into account by subtracting the row number just above the list, so (ROW($A$2:$A$8)-ROW($A$1)) does nothing more than create an array of {1;2;3;4;5;6;7}. This "index" array is divided by the array ($A$2:$A$8<=E2), where the logical comparison will create an array consisting of TRUEs and FALSEs, depending on whether the known x values are less than or equal to the input x2 value in cell E2 (in this case). The division operation will coerce the TRUEs and FALSEs to be treated as 1's and 0's, and the result will be an array that might look like this: {1;2;3;4;#DIV/0!;#DIV/0!;#DIV/0!}, where FALSE's converted to 0's give a division-by-zero error. That's okay, since the AGGREGATE function has invoked the "6" option which means that errors will be ignored. The correct interpretation of this hypothetical resultant array is that the first four items in the list are less than or equal to x2. We want the largest item in this list, and I assumed that the values would be monotonically increasing, so the 4th x-value would be taken as x1 in the equation above, and this would establish the beginning of the x range to use for the interpolation. In order to specify that the largest array value should be returned, the 14 option is used in AGGREGATE, which will return the nth largest value in the array ("n" is given by the last argument...in this case a 1). So AGGREGATE(14,6,{array},1) returns the largest array value while ignoring any error terms in the array. In some applications, this feature is very handy---to intentionally generate errors in the array to exclude certain values and then rely on the "ignore errors" option to prevent the function from crashing with an error message. Also, in some applications, you might want the 2nd largest value, so the number of the last argument would be changed to a 2, and so on, as is done with the LARGE function. Since the array values are row numbers converted to act as list indices, they work well within the INDEX function to return the desired x and y values for the interpolation. In cases where the next list item should be returned, I've added a 1 to the index value given by AGGREGATE. The biggest issue with my approach is that each x and y value is extracted from the table using a formula resembling this INDEX/AGGREGATE construction, which adds a lot of redundancy and doesn't take advantage of the more direct route to specify the ranges of interest. Incidentally, another common use of AGGREGATE is to return the nth smallest value in an array, which is done using 15 as the first argument.
One thing that I would caution with any of these approaches is to confirm that your x values are in ascending order. If not, then some adjustment to the formulas would be necessary.
