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
XMATCHassumes 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
- Excel Dependent Drop-Down Lists: Setup and Fixes
- Excel DOS Keyboard Shortcuts: What Actually Works
- Excel Direct Access vs Link: What’s the Difference?
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Styles in Microsoft Word: How They Work and When to Use ThemStyles in Microsoft Word are named sets of formatting that you can apply consistently to text, paragraphs, tables,…
- Excel Rows Keep Hiding? How to Find and Unhide ThemExcel rows that seem to keep hiding are usually filtered, grouped, set to zero height, or hidden manually.…
- Excel File Data: What It Contains and How to Use ItExcel file data is the information stored in a workbook: values, text, dates, formulas, tables, and sometimes charts,…
- How to Remove Merged Cells in Google SheetsTo remove merged cells in Google Sheets, select a merged cell or range, then choose Format > Merge…
- Blocking a Sender in Outlook: Steps and What to KnowTo block a sender in Outlook, select one of their messages and choose Block or Block Sender from…
- Delete a Next Page Section Break in Microsoft WordTo delete a Next Page section break in Word, show formatting marks with Home > ¶, locate the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.