Use of Match, Index, and Vlookup to Interpolate

[velocityinc]

I have a 3 column table which looks like the following

A1..5 B1...2.0 C1 8.0
A2..12 B2...3.25 C2 11.0
A3..16 B3...4.65 C3 15.5

I would like to input a value of 7 in cell D1 that does the following:

Output: Looks in column A for 7 between 5 and 12, and then
interpolates between values 2.0 and 3.25 (based on the separation of 7
from 5 and 7 from 12) as the output from column B.

Because the columns are long, all the way to A250, I cannot do this
manually in any efficient way.

Thanks

 

[Guest]

If the numbers in column A are in ascending order, perhaps

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MATCH(D1,A1:A3)+1),INDEX(A1:A3,MATCH(D1,A1:A3)):INDEX(A1:A3,MATCH(D1,A1:A3)+1),A8)

 

[velocityinc]

If the numbers in column A are in ascending order, perhaps

=TREND(INDEX(B1:B3,MATCH(D1,A1:A3)):INDEX(B1:B3,MATCH(D1,A1:A3)+1),INDEX(A1­:A3,MATCH(D1,A1:A3)):INDEX(A1:A3,MATCH(D1,A1:A3)+1),A8)










- Show quoted text -

How does the A8 at the end play a part? Just this question, else it
will work like a charm, thanks to your help

 

[Lori]

Another possibility is:

=PERCENTILE(B1:B3,PERCENTRANK(A1:A3,D1,20))

which works on any ascending dataset as in the example. This works
because the percentile/percentrank functions interpolate for the "in-
between" values.

Or in general use the Trend formula above or this array formula (ctrl
+shift+enter to evaluate):

=FORECAST(D1,IF((MATCH(D1,A:A)-ROW(A1:A3)+0.5)^2<1,B1:B3),A1:A3)

The Forecast and Trend functions use a "best fit" line for estimating
values in the dataset. To intepolate between the points the formula
above just uses the line joining the two nearest points.

 

[Guest]

A8 s/b D1 where D1=7. But I would use one of Lori's suggestions - they're
much shorter.