Excel Interpolation: How to Estimate Values Between Points

How-to basics · Updated November 19, 2026

Yes. Excel can interpolate between known points with a formula that assumes a straight-line relationship between the two surrounding data points. In Microsoft 365, you can use XMATCH to find those points and calculate the estimate; for example, if x-values are in A2:A6, corresponding y-values are in B2:B6, and the target x-value is in D2, the formula below returns the linearly interpolated y-value.

What interpolation means

Interpolation estimates a value inside the range covered by known data. Suppose a table records a machine’s output at selected temperatures, or the distance a vehicle has traveled at specific times. If you know the value at two surrounding measurements, interpolation estimates the value between them.

Linear interpolation treats the two surrounding points as endpoints of a straight line. If the points are (x₁, y₁) and (x₂, y₂), and the desired x-value is x, the estimate is:

y₁ + (x - x₁) * (y₂ - y₁) / (x₂ - x₁)

The fraction (x – x₁) / (x₂ – x₁) indicates how far the target lies between the endpoints. The formula moves the same fraction of the way from y₁ to y₂. This is a calculation based on an assumption, not a measurement of the unknown value.

Interpolate with an Excel formula

For Microsoft 365 on Windows, put the known x-values in A2:A6 in ascending order, their corresponding y-values in B2:B6, and the target x-value in D2. With no duplicate x-values or blank cells in the data, enter this formula in E2:

=IF(OR(D2<MIN(A2:A6),D2>MAX(A2:A6)),NA(),LET(k,XMATCH(D2,A2:A6,1),IF(INDEX(A2:A6,k)=D2,INDEX(B2:B6,k),LET(x1,INDEX(A2:A6,k-1),x2,INDEX(A2:A6,k),y1,INDEX(B2:B6,k-1),y2,INDEX(B2:B6,k),y1+(D2-x1)*(y2-y1)/(x2-x1)))))

This formula uses XMATCH with match mode 1 to find an exact match or the next larger x-value. It then either returns the recorded y-value for an exact match or calculates between the adjacent points. The outer check returns #N/A when the target is outside the known range rather than silently extrapolating. XMATCH and LET are available in Microsoft 365 and Excel 2021 or later.

For a concrete example, suppose A2:A6 contains 0, 10, 20, 30, and 40, while B2:B6 contains 10, 30, 50, 55, and 60. If D2 is 15, the formula uses the points (10, 30) and (20, 50). The target is halfway between 10 and 20, so the estimated y-value is 30 + 0.5 × (50 – 30) = 40. The result in E2 is 40.

Prepare the data before calculating

The lookup formula depends on the structure of the data. Check the table before relying on the estimate:

  • Keep each x-value paired with its correct y-value on the same row. Sorting only one column breaks those pairings.
  • Sort x-values from smallest to largest. The approximate search used by XMATCH assumes ascending order.
  • Use numeric values for both columns. Dates and times are stored as numbers in Excel, so they can be used as x-values if they are genuine Excel dates or times.
  • Remove or resolve duplicate x-values. A vertical pair of points at the same x-value does not define a single y-value for ordinary linear interpolation.
  • Check for blank cells, text-formatted numbers, and errors in the lookup ranges. These can prevent a match or cause a misleading result.

If you have more than five known points, expand both ranges in the formula so that they cover the same rows. For example, if x-values are in A2:A100 and y-values are in B2:B100, replace A2:A6 and B2:B6 throughout. For a changing table, Excel Tables can help keep formulas aligned with added rows, but make sure the x-values remain sorted.

What the formula does at boundaries

An exact input point does not need estimating: the formula returns the corresponding recorded y-value. Between two points, it uses the neighboring pair. At the smallest known x-value it returns that point’s y-value, and at the largest known x-value it returns the last recorded y-value. A target below the smallest x-value or above the largest produces #N/A.

This boundary behavior matters because interpolation and extrapolation are different. Interpolation estimates inside the observed x-range. Extrapolation extends a trend beyond that range, where the relationship may change and the estimate may be unreliable. The formula deliberately avoids extrapolating. If an out-of-range result is possible in a report, you can leave the error visible, or handle it explicitly with a separate message or decision rule rather than treating it as a normal estimate.

The formula also assumes that x-values are unique and that adjacent points have different x-values. If those requirements are not met, there may be no unambiguous straight-line segment to calculate. Resolve the source data first instead of masking the error.

Check whether a straight line is appropriate

Linear interpolation is easy to audit, but it is only as reasonable as its straight-line assumption. If the measured relationship curves sharply, changes at a threshold, or follows a repeating pattern, a straight line between two observations may not represent what happens in between. For example, a sensor’s output might change gradually over one interval but jump after a control setting changes.

Look at the surrounding values, plot the data, and use subject-matter knowledge to decide whether a local straight-line estimate makes sense. A scatter chart can reveal broad curvature or irregularities; use Insert > Scatter (X, Y) to create one in Excel for Windows. A chart does not validate the model by itself, but it can make the assumptions easier to inspect. If the relationship is nonlinear, consider an appropriate model or collect measurements closer to the target instead of assuming that linear interpolation is accurate.

Interpolation versus Excel’s FORECAST.LINEAR

FORECAST.LINEAR is an Excel function for predicting a y-value from known x- and y-values using linear regression. It is not the same as piecewise linear interpolation. Interpolation uses the two neighboring points around the target and draws a segment between them. Linear regression fits one overall straight-line relationship to a set of points, so its result may not pass through either of the two nearest observations.

Choose interpolation when the value should be estimated locally between known measurements and a straight segment is a reasonable assumption. Consider linear regression when you want one overall linear trend across a set of observations. Neither method guarantees a physically or practically correct answer; the choice depends on how the data were generated and what the estimate will be used for.

Common problems and fixes

  • The formula returns #N/A. Check whether D2 is outside the x-range, or whether the lookup data contain an unexpected value. The formula intentionally returns this error for out-of-range targets.
  • The answer seems to use the wrong points. Confirm that the x-values are sorted ascending and that the y-values were not sorted separately. Each x-value must remain paired with its original y-value.
  • The formula returns a strange estimate. Inspect for text-formatted numbers, incorrect units, outliers, or a relationship that is not close to linear between the two points.
  • Excel rejects the formula. Check that the formula was copied with ordinary straight quotation marks if you edited it, and that the parentheses and commas are intact. Some regional Excel settings use semicolons as argument separators; the formula shown here uses commas.
  • The result is an error when x-values repeat. Remove duplicates or decide how repeated measurements should be summarized. A duplicate x-value can make the interval ambiguous or lead to division by zero.

Cell formatting affects how a result appears, not the calculation itself. If your x-values represent dates, for instance, the output y-value may need a suitable number format such as a decimal, percentage, or date depending on what it represents. Format the result cell with Home > Number Format; do not change the underlying values just to make the display look right.

Other ways to calculate an estimate

If you do not have XMATCH or prefer to see each calculation step, find the two bracketing rows manually and place x₁, x₂, y₁, and y₂ in separate cells. With x₁ in F2, x₂ in G2, y₁ in H2, y₂ in I2, and the target in D2, the interpolation calculation is:

=H2+(D2-F2)*(I2-H2)/(G2-F2)

This version uses ordinary arithmetic and works in older Excel versions too, provided that you identify the correct adjacent points yourself. For the example points (10, 30) and (20, 50) with D2 equal to 15, it returns 40. Keeping the endpoints visible can make a workbook easier to review, but it also means the user must select the right interval for each target.

Excel does not have a built-in worksheet function named INTERPOLATE. A chart’s trendline or a regression function can help analyze a broader pattern, but those are not substitutes for this adjacent-point calculation when the goal is piecewise linear interpolation.

Frequently Asked Questions

Does Excel have an interpolation function?
No dedicated worksheet function named INTERPOLATE is built into Excel. You can calculate linear interpolation with a formula, using arithmetic and lookup functions such as XMATCH and INDEX.

Can Excel interpolate between two points only?
Yes. If you already know the two endpoints, use y1+(x-x1)*(y2-y1)/(x2-x1), replacing the labels with cell references. The target x-value should lie between x1 and x2 for interpolation.

Can I interpolate dates or times?
Yes, if Excel recognizes the dates or times as numeric values. Put them in the x-value column in ascending order, and use a target date or time in the same units. The y-values still need to represent a quantity that can reasonably be estimated linearly.

Should I sort the data before interpolating?
For the XMATCH formula shown here, yes: sort the x-values in ascending order and keep each y-value on its matching row. An unsorted range can produce an incorrect bracket or an error.

What is the difference between interpolation and extrapolation?
Interpolation estimates within the range of known x-values. Extrapolation estimates beyond that range. The formula in this article returns #N/A outside the range to make that distinction visible rather than extending the line without warning.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.