Interpolating using the Lookup Function in Excel
1.
- Sort your table in ascending order based on the values in the first column.
2. Determine the two values in the first column that bracket the value you want to interpolate.
3. Use the
function to find the position of the lower bracket value in the first column of the table. For example, if the lower bracket value is 100 and it is in cell A2, the formula would be:
Code:
=MATCH(130,A2:A3,1)
4. Use the
function to retrieve the corresponding values in the second column for the lower and upper bracket values. For example, if the values in the second column are in cells B2 and B3, the formula would be:
Code:
=INDEX(B2:B3,MATCH(130,A2:A3,1),1)
5. Use the following formula to interpolate between the two bracket values:
Code:
=INDEX(B2:B3,MATCH(130,A2:A3,1),1)+((130-INDEX(A2:A3,MATCH(130,A2:A3,1),1))/(INDEX(A3:A4,MATCH(130,A2:A3,1),1)-INDEX(A2:A3,MATCH(130,A2:A3,1),1)))*(INDEX(B3:B4,MATCH(130,A2:A3,1),1)-INDEX(B2:B3,MATCH(130,A2:A3,1),1))
This formula uses linear interpolation to calculate the value for 130 based on the values for 100 and 200 in the first column. You can replace 130 with a cell reference if you want to interpolate for different values.