For most workbooks, the best way to interpolate in Excel is piecewise linear interpolation: use the two data points immediately below and above your target, then calculate the value between them. In Microsoft 365, XLOOKUP can find those bracketing points automatically; for a single interval, a short formula is simpler.
What to Look For
Interpolation estimates a value inside the range of known observations. If a table gives a result at x = 20 and x = 30, for example, interpolation estimates the result at x = 25. It is not the same as extrapolation, which estimates beyond the known range and is usually less dependable. Excel does not have a built-in INTERPOLATE function, so the right approach depends on the shape and layout of your data.
- Whether the relationship is locally linear. Straight-line interpolation assumes the rate of change between the two selected points is constant. It is often a sensible estimate for lookup tables, measured data, and values between nearby readings. It will not capture a sharp curve unless your observations are close enough to represent that curve.
- How many known points you should use. Piecewise interpolation uses the neighboring points around the target. A regression method uses a wider set of observations to find an overall trend. Those methods answer different questions: regression may smooth noisy data, but generally will not pass through each supplied point.
- How your data is arranged. A one-dimensional table needs an x column and a corresponding y column. A two-variable grid needs a value at each corner of the surrounding cell. Your method must match the structure rather than just the formula you find easiest to copy.
- Excel version and maintenance. XLOOKUP and LET are available in Microsoft 365 and Excel 2021 or later. INDEX and MATCH work in older desktop versions too. For a workbook shared with others, favor a formula their Excel versions can calculate and that is easy to audit.
- Data quality and boundaries. Lookup x-values should be numeric, sorted when the method requires it, and free of duplicates. Decide what should happen at the first and last data points, and do not silently use interpolation formulas to make unsupported estimates outside the range.
For every method, keep units consistent and check that x-values really represent the input variable you intend to use. If the x-axis is time, for example, Excel dates are stored as numbers, so the same formulas can work, but the result should be interpreted in the date or time units appropriate to your data.
1. Two-Point Linear Interpolation for a Single Estimate
Use this when you already know the two points that surround the target. It is the clearest approach for a one-off calculation because the formula shows exactly how the estimate is formed. The calculation takes the fraction of the distance from the lower x-value to the upper x-value, then applies that same fraction to the change in y.
With the lower x-value in A2, the upper x-value in B2, their corresponding y-values in C2 and D2, and the target x-value in F2, enter:
=C2+(F2-A2)*(D2-C2)/(B2-A2)
For example, if A2 is 10, B2 is 20, C2 is 100, D2 is 160, and F2 is 14, the result is 124. The target is 40% of the way from 10 to 20, so the estimate is 40% of the way from 100 to 160. The formula assumes A2 and B2 are different; equal endpoints would divide by zero.
Strengths: It works in old and current Excel versions, is easy to explain, and makes the selected interval visible. Trade-offs: You must choose the correct surrounding points yourself, which can become tedious for a large table or repeated estimates. Best for: a quick calculation, a small table, or a workbook where clarity matters more than automation.
2. XLOOKUP and LET for a Sorted Data Table
For repeated estimates from one long table, this is the most convenient general-purpose choice in Microsoft 365 or Excel 2021 and later. XLOOKUP finds the nearest known x-value on each side of the target, and the formula interpolates between their y-values. Keep the x-values in ascending order and use one y-value for each x-value.
With x-values in A2:A6, matching y-values in B2:B6, and a target within the range in D2, use:
=LET(target,D2,lowX,XLOOKUP(target,A2:A6,A2:A6,,-1),highX,XLOOKUP(target,A2:A6,A2:A6,,1),lowY,XLOOKUP(lowX,A2:A6,B2:B6),highY,XLOOKUP(highX,A2:A6,B2:B6),IF(lowX=highX,lowY,lowY+(target-lowX)*(highY-lowY)/(highX-lowX)))
The -1 match mode asks XLOOKUP for an exact match or the next smaller item; 1 asks for an exact match or the next larger item. The IF check returns the recorded y-value directly when the target matches a known x-value, avoiding a zero denominator. With A2:A6 containing 0, 10, 20, 30, 40; B2:B6 containing 0, 100, 150, 230, 300; and D2 equal to 25, the formula returns 190. It uses the points (20, 150) and (30, 230).
Strengths: It handles many targets without manual interval selection and returns exact observations unchanged. Trade-offs: It is longer than the two-point formula, requires a sorted x-range for these approximate match modes, and returns an error for targets outside the table. Duplicate x-values can also make the chosen match ambiguous. Best for: current Excel users who regularly estimate values from a clean, one-dimensional lookup table. If your data is unsorted, sort a copy or use another method rather than assuming this formula will find the intended neighbors.
3. INDEX and MATCH for Older Excel Versions
If you need to support older Excel versions that lack XLOOKUP and LET, use MATCH to locate the lower bracket and INDEX to retrieve the neighboring values. This approach is more transparent when split across a helper cell and a calculation cell. MATCH with a match type of 1 expects the lookup values to be sorted in ascending order.
With x-values in A2:A6, corresponding y-values in B2:B6, and a target in D2 that is at least the first x-value but below the last, put this in C2 to find the lower point’s position:
=MATCH(D2,A2:A6,1)
For a target of 25 in the example table, C2 returns 3, because 20 is the largest x-value less than or equal to 25. With that position in C2 and the same table and target cells, calculate the interpolated y-value with:
=INDEX($B$2:$B$6,C2)+(D2-INDEX($A$2:$A$6,C2))*(INDEX($B$2:$B$6,C2+1)-INDEX($B$2:$B$6,C2))/(INDEX($A$2:$A$6,C2+1)-INDEX($A$2:$A$6,C2))
For the example values, this returns 190. An exact interior match also returns its recorded y-value. The stated target boundary matters: at the final x-value, MATCH returns the last position and the formula tries to read the nonexistent next row. Add a boundary check or use XLOOKUP with the exact-match handling above if targets may include the final value.
Strengths: INDEX and MATCH are broadly compatible and let you inspect the bracket position separately. Trade-offs: The table must be sorted, the target must be within the supported boundaries, and the formula is harder to read than the two-point version. Best for: organizations maintaining workbooks in older Excel installations or users who prefer a visible helper cell.
4. Bilinear Interpolation for a Two-Variable Grid
When the input depends on two variables, such as temperature and pressure, a single x/y lookup is not enough. Bilinear interpolation estimates between the four values at the corners of the grid cell surrounding the target. It first interpolates across one axis at each end of the other axis, then interpolates between those two results. It assumes a rectangular grid and a locally smooth, approximately linear change along each axis.
Suppose x-coordinates are in A2:A3, y-coordinates are in B1:C1, and the four measured values are in B2:C3. Let E2 contain the target x and F2 the target y. With A2 = 0, A3 = 10, B1 = 0, C1 = 10, and grid values B2 = 0, C2 = 100, B3 = 20, C3 = 140, target x = 4 and target y = 6, calculate the value at the lower x-row in G2 with:
=B2+($F$2-$B$1)*(C2-B2)/($C$1-$B$1)
G2 returns 60. With the same layout and target cells, calculate the value at the upper x-row in H2 with:
=B3+($F$2-$B$1)*(C3-B3)/($C$1-$B$1)
H2 returns 92. With G2 and H2 holding those intermediate results, interpolate across x in I2 with:
=G2+($E$2-$A$2)*(H2-G2)/($A$3-$A$2)
I2 returns 72.8. This method does not require the grid values themselves to rise or fall in a particular direction, but each coordinate pair must describe the correct cell and its corners. Strengths: It adapts the familiar straight-line calculation to two inputs and is easy to audit in stages. Trade-offs: It needs four surrounding observations, a correctly organized grid, and more helper calculations. Best for: engineering, scientific, or rate tables where two independent inputs determine one output.
5. FORECAST.LINEAR for an Overall Straight-Line Trend
Sometimes the goal is not to follow the nearest interval, but to estimate from an overall straight-line relationship across many observations. FORECAST.LINEAR uses linear regression, choosing the line that best fits the supplied data. This can be useful when measurements contain noise and an overall trend is more meaningful than each individual segment. It is a trend estimate, not strict point-to-point interpolation: the fitted line may not pass through the known observations.
With x-values in A2:A6, y-values in B2:B6, and the target x in D2, enter:
=FORECAST.LINEAR(D2,B2:B6,A2:A6)
For x-values 0, 10, 20, 30, 40 and y-values 0, 100, 150, 230, 300, a target of 25 returns 192.5. The least-squares line for those observations is y = 10 + 7.3x, so the estimate is 10 + 7.3 × 25 = 192.5. By contrast, local interpolation between (20, 150) and (30, 230) returns 190. Neither result is automatically wrong; each answers a different question.
Strengths: It uses all supplied points and can summarize a roughly linear trend rather than tracking every local fluctuation. Trade-offs: It can miss local changes and does not guarantee exact values at known x-points. It also should not be treated as a way to extend a trend far beyond the observations without considering whether that trend remains plausible. Best for: trend estimation from several observations when a straight-line fit is the intended model, not for a lookup table that must honor neighboring readings.
How to Choose
Start with the shape of the problem. If you have one input and a table of known input/output pairs, choose local linear interpolation when your target lies between two points. For one estimate, use the two-point formula. For many estimates from a sorted table, use XLOOKUP and LET in Microsoft 365 or Excel 2021 and later. If the target depends on two inputs and the data is a rectangular grid, use bilinear interpolation. If the purpose is to fit a general straight-line trend through noisy observations, use FORECAST.LINEAR instead.
Then check your Excel version and sharing needs. Excel formulas do not require a paid interpolation add-in. XLOOKUP and LET make the modern formula more compact, but people opening the workbook in older Excel may see an unsupported-function error. INDEX and MATCH are a practical compatibility choice, provided the data is sorted and you handle the end boundaries. If coworkers use different versions, test the workbook in the oldest version they need to support.
Think about the cost of a wrong estimate. For a casual estimate between close readings, linear interpolation may be sufficient. For safety-critical, scientific, or operational decisions, verify that the chosen interpolation model is appropriate and validate it against known results. A spreadsheet formula performs the calculation you specify; it cannot confirm that a straight line is a sound assumption for the underlying process.
Finally, make the workbook easy to review. Label the input and output units, retain the original observations, and show the target and bracket points where practical. Check for blank cells, text stored as numbers, duplicate x-values, and targets outside the observed range. If a result must never extrapolate, make that a deliberate condition in the workbook rather than accepting an accidental estimate beyond the endpoints.
Frequently Asked Questions
Does Excel have an INTERPOLATE function?
No. Excel does not provide a built-in function with that name. Use a formula such as the two-point calculation, an XLOOKUP-based formula, or INDEX and MATCH to implement the method that fits your data.
Should I use linear interpolation or FORECAST.LINEAR?
Use linear interpolation to estimate between the nearest known points and preserve local values. Use FORECAST.LINEAR when you want a regression line fitted across the full set of observations. Its estimate can differ from local interpolation, as the example above shows.
Do my x-values need to be evenly spaced?
No. The formulas calculate the fraction of the actual distance between the surrounding x-values, so uneven spacing is fine. For approximate-match lookup methods, however, the x-values must be sorted as specified.
What happens if the target is outside my data?
That is extrapolation, not interpolation. The XLOOKUP formula above returns an error outside the range, which helps avoid an unintended estimate. If extrapolation is required, choose and document a model deliberately rather than assuming the last interval remains valid.
Can I interpolate missing values in a column?
Yes, if the missing entries are between known values and the rows provide the x-coordinates, such as dates or numeric measurements. Apply the appropriate formula to each gap and review the results. Do not treat a blank at the start or end of a series as an interpolation problem, because it has no known point on both sides.
Bottom Line
For a single estimate, use the two-point linear formula: it is short, compatible, and easy to check. For a reusable one-dimensional table in modern Excel, the XLOOKUP and LET approach is the best all-around choice because it finds the surrounding points and handles exact matches. Choose INDEX and MATCH when older-version compatibility matters, bilinear interpolation for a two-input grid, and FORECAST.LINEAR only when an overall regression trend is what you actually need.
Whichever method you choose, keep estimates inside the observed range unless you have a separate, justified reason to extrapolate. The most reliable spreadsheet is not just one with a working formula; it is one whose assumptions and boundaries are clear to the next person who uses it.
Related
- Excel Historical Sales Analysis: Find Trends and Patterns
- Excel Financial Analysis: Methods, Models, and Metrics
- Excel Export CSV With Headers: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- COUNTIF in Excel: How It Works and How to Use ItExcel’s COUNTIF function counts cells in a range that meet one condition. Give it a range to inspect…
- Excel MIN IF: Find the Smallest Value That Meets a TestTo find the smallest value in Excel that meets a condition, use MINIFS in Microsoft 365: with regions…
- Excel Formula for Checking If a Cell Is EmptyThe simplest Excel formula for checking if a cell is empty is =ISBLANK(A1), which returns TRUE if A1…
- Excel Conditional Formatting Formula if Zero?To highlight cells that equal zero in Excel, use the formula =A1=0 in a conditional formatting rule. Select…
- Excel Formula to Highlight Every Other Row AutomaticallyTo highlight every other row in Excel, select the range, open Home > Conditional Formatting > New Rule,…
- COUNT Function in Excel: Examples and Common UsesTo count numeric values in Excel, use =COUNT(B2:B10) when the values are in B2:B10. COUNT includes numbers and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.