About Us

ExcelBanter AI

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

Code:

MATCH

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

Code:

INDEX

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Interpolate data points in table	pjd33	Excel Worksheet Functions	3	May 24th 07 05:18 PM
How can I interpolate values off a table?	phil	Excel Worksheet Functions	1	January 19th 07 10:07 AM
Interpolate, Interpolation, VlookUp, HlookUp, Read a table	cradino	Excel Worksheet Functions	0	September 3rd 06 12:05 AM
Interpolate from a table?	israelica	Excel Discussion (Misc queries)	1	February 17th 06 04:53 PM
IS THERE A WAY TO INTERPOLATE AUTOMATICALLY WITHIN A DATA TABLE?	Eric S.	New Users to Excel	3	June 16th 05 05:55 AM

Menu

About Us