Interpolate on lookup table

[eastwynd]

I have a table of values that I want to lookup on but need the returned values to be interpolated if they're not exact matches, e.g.

Age Comp
0 0.01
3 0.02
6 0.03
9 0.04
12 0.05
24 0.10
36 0.13
48 0.12
60 0.10
90 0.09

So, if a person were, say, 65 years old, Excel will return a VLOOKUP value of 0.10. I want it to perform a simple linear interpolation between 0.10 and 0.09 based on the difference between 60 and 90 in the Age column.

Props and thanks in advance!

 

[Norie]

This appears to work.

=VLOOKUP(C1,A2:B11,2)

C1 has 65 and A2:B11 the ages/comps.

 

[Aladin Akyurek]

I have a table of values that I want to lookup on but need the returned values to be interpolated if they're not exact matches, e.g.

Age Comp
0 0.01
3 0.02
6 0.03
9 0.04
12 0.05
24 0.10
36 0.13
48 0.12
60 0.10
90 0.09

So, if a person were, say, 65 years old, Excel will return a VLOOKUP value of 0.10. I want it to perform a simple linear interpolation between 0.10 and 0.09 based on the difference between 60 and 90 in the Age column.

Props and thanks in advance!

aaLinearInterpolation eastwynd.xls
	A	B	C	D	E	F	G	H	I
1	Age	Comp
2	0	0.01		65	60	0.1	90	0.09	0.098333333
3	3	0.02		9	9	0.04	9	0.04	0.04
4	6	0.03		9.7	9	0.04	12	0.05	0.042333333
5	9	0.04
6	12	0.05
7	24	0.1
8	36	0.13
9	48	0.12
10	60	0.1
11	90	0.09
Sheet1

E2:

=LOOKUP(D2,$A$2:$A$11)

F2:

=LOOKUP(D2,$A$2:$A$11,$B$2:$B$11)

G2:

=INDEX($A$2:$A$11,MATCH(D2,$A$2:$A$11,1)+IF(D2< MAX($A$2:$A$11),LOOKUP(D2,$A$2:$A$11)<>D2,0))

H2:

=INDEX($B$2:$B$11,MATCH(D2,$A$2:$A$11,1)+IF(D2< MAX($A$2:$A$11),LOOKUP(D2,$A$2:$A$11)<>D2,0))

I2, the result cell:

=F2+IF(E2=G2,0,IF(D2>E2,(D2-E2)/(G2-E2)*(H2-F2),0))

 

[eastwynd]

Thank you for your reply; here's what I came up with shortly before you posted:

[Removed incomplete HTML~VP]