Excel Interpolate Function: How to Calculate Values

Formulas & functions · Updated November 20, 2026

Microsoft Excel does not have a built-in function named INTERPOLATE. To estimate a value between two known points, use a linear interpolation formula: with x-values in A2 and A3, corresponding y-values in B2 and B3, and the x-value to estimate in D2, enter =B2+(D2-A2)*(B3-B2)/(A3-A2). The formula finds the proportional distance between the known x-values and applies that proportion to the y-values.

What interpolation means in Excel

Interpolation estimates an unknown value that falls between known data points. For example, if a measurement is 10 at an x-value of 0 and 30 at an x-value of 10, linear interpolation estimates the measurement at x = 4 as 18. It assumes the value changes at a steady rate between those two points.

The word “linear” matters: this method draws a straight line between the points. It is often useful for measurements, schedules, lookup tables, and other data where a straight-line approximation is reasonable. It does not reveal the true value between observations; it calculates an estimate based on an assumption about how the values change.

Excel’s lack of a dedicated INTERPOLATE function does not prevent you from doing the calculation. A basic arithmetic formula works for a pair of points, while a longer formula can locate the surrounding points in a data table. Excel also has functions for fitting a line to a larger dataset, but that is a related, different calculation.

The two-point interpolation formula

Suppose A2 contains the first x-value, B2 its corresponding y-value, A3 the second x-value, and B3 its corresponding y-value. Put the target x-value in D2. This formula returns the linearly interpolated y-value:

=B2+(D2-A2)*(B3-B2)/(A3-A2)

For example, let A2 be 0, B2 be 10, A3 be 10, B3 be 30, and D2 be 4. The change in y is 20 over a change in x of 10, or 2 y-units per x-unit. The target is 4 units after the first point, so the result is 10 + 4 × 2 = 18. Excel returns 18.

The same calculation can be written using the slope between the points: find the change in y divided by the change in x, multiply by the distance from the first x-value to the target, and add the first y-value. That form makes the assumptions visible and is usually easier to audit than a formula assembled from several lookup functions.

Interpolate within a table of points

When a table contains several observations, first identify the two rows that bracket the target. For an automatic version, assume x-values are in ascending order in A2:A6, matching y-values are in B2:B6, and the target x-value is in D2. In Microsoft 365 or Excel 2021, this LET formula finds the interval and calculates the estimate:

=LET(xs,A2:A6,ys,B2:B6,target,D2,IF(OR(target<MIN(xs),target>MAX(xs)),NA(),IF(target=MAX(xs),INDEX(ys,ROWS(ys)),LET(pos,MATCH(target,xs,1),lowX,INDEX(xs,pos),highX,INDEX(xs,pos+1),lowY,INDEX(ys,pos),highY,INDEX(ys,pos+1),lowY+(target-lowX)*(highY-lowY)/(highX-lowX)))))

The formula assumes every x-value is numeric, the x-values are sorted from smallest to largest, each x-value is unique, and the two ranges have the same number of rows. It returns #N/A when the target is outside the table’s x-range rather than silently extrapolating. If the target equals the largest x-value, it returns that row’s y-value. LET names the ranges and intermediate values so the calculation is easier to read; LET is available in Excel 2021 and Microsoft 365.

For a small table, a manual method may be clearer: find the row immediately below the target and the next row, then use the two-point formula with those cells. This is also useful in older Excel versions. If the target is exactly one of the known x-values, the estimate should equal its paired y-value.

How to set up the worksheet

A consistent layout helps prevent mismatched values. Put the independent variable—the quantity you know or choose—in one column, and its corresponding measurement in the next. Keep each pair on the same row.

  • Enter x-values in column A and their corresponding y-values in column B.
  • Sort the x-values in ascending order if you plan to use approximate MATCH in the table formula.
  • Put the target x-value in a separate cell, such as D2, and label the result cell.
  • Use the two-point formula when you have already identified the bracketing rows; use the table formula when you need Excel to locate them.

To enter a formula, select the intended result cell, type an equals sign followed by the formula, and press Enter. Excel recalculates the result when the referenced inputs change. Give the columns clear headers and include units, such as time in minutes or distance in kilometres, so that the estimated result is not mistaken for a different measurement.

Interpolation versus FORECAST.LINEAR

Excel’s FORECAST.LINEAR function does not perform the same kind of lookup as piecewise interpolation. It fits a least-squares straight line through a set of known observations, then estimates a value from that overall trend. With x-values in A2:A6, corresponding y-values in B2:B6, and a target x-value in D2, the formula is:

=FORECAST.LINEAR(D2,B2:B6,A2:A6)

This returns the estimate from the best-fitting line across all five points. It may differ from linear interpolation between the two observations surrounding D2, particularly when the data are curved or noisy. Use piecewise interpolation when the local segment between adjacent observations is the intended basis for the estimate; use FORECAST.LINEAR when a single straight-line trend across the dataset is what you want.

FORECAST.LINEAR can also return estimates beyond the observed x-values, which is extrapolation rather than interpolation. Neither a function name nor a plausible-looking result guarantees that the chosen model suits the data. Select the method based on what the observations represent and what assumption is defensible.

Dates, units, and other data considerations

Excel stores dates as serial numbers, so the same arithmetic can interpolate between dates if the x-values are genuine Excel dates. For example, the x-column might contain dates and the y-column daily readings. A target date between two observations can then be used as the target x-value. Format the input cells as dates for readability; formatting changes how a value is displayed, not the underlying arithmetic.

Interpolation also works with numeric units, provided the x-values use a consistent unit and the y-values are comparable. Do not mix, for example, hours and minutes in the same x-column without converting them first. The calculation returns a y-value in the units of the y-column.

Consider whether a straight line is appropriate. A quantity that changes in steps, follows a curve, or is constrained by a physical minimum may not be well represented by a straight segment. For a curved relationship, a domain-specific model or a suitable curve-fitting method may be more appropriate. Interpolation is a calculation, not evidence that the underlying process actually changes linearly.

Common errors and how to avoid them

  • Dividing by zero: The two known x-values must differ. If A2 equals A3, the denominator in the basic formula is zero, so the calculation cannot define a slope.
  • Unsorted x-values: Approximate MATCH expects ascending data. An unsorted x-column can identify the wrong interval and produce an incorrect result.
  • Using mismatched pairs: Each x-value must remain beside the y-value measured at that x. Sorting only one column breaks the relationship between observations.
  • Target outside the data range: A target below the minimum or above the maximum is extrapolation. The table formula above flags that case with #N/A; decide deliberately whether extrapolation is acceptable before using another method.
  • Blank or text entries: Check that the inputs are numeric and that there are no gaps in the part of the table used to find the interval.

If the result looks wrong, check the two bracketing rows by hand and calculate the slope. Also confirm that the formula references the intended columns and that the cell containing the target is not text that merely looks like a number or date.

Frequently Asked Questions

Does Excel have an INTERPOLATE function?
No. Excel does not include a built-in function named INTERPOLATE. You can calculate linear interpolation with an arithmetic formula, or use a longer formula to find the surrounding points in a table.

What formula interpolates between two cells?
If the known x-values are in A2 and A3, their y-values are in B2 and B3, and the target x-value is D2, use =B2+(D2-A2)*(B3-B2)/(A3-A2). The x-values must be different, and the target should be between them for interpolation.

Can FORECAST.LINEAR be used for interpolation?
It can estimate a value from a straight-line model, but it fits one trend across the supplied observations. It does not necessarily calculate the segment between the two nearest points. Use the method that matches the relationship you intend to model.

What if the target is outside the known values?
That is extrapolation, not interpolation. A formula may still return a number, but the estimate extends the assumed pattern beyond the observed range and can be less reliable. The table formula in this article returns #N/A for an out-of-range target.

Can I interpolate between dates in Excel?
Yes, if the dates are valid Excel date values and the paired measurements are numeric. Excel performs the calculation on the date serial values; format the cells as dates so they display clearly.

Why does my interpolation formula return an error?
Common causes include identical x-values, text or blank inputs, a target outside the table range, or unsorted x-values in the approximate-match formula. Check the input pairs and verify the interval before changing the formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.