Guides

How to interpolate in Excel and Google Sheets

Five ways to interpolate in a spreadsheet, from a one-line formula to a full table lookup, with formulas you can copy.

Updated 19 October 2026

Excel has no single INTERPOLATE function, but every interpolation you'll need takes one formula. All examples below also work in Google Sheets unless noted. The examples assume known x values in A2:A20 (sorted ascending), y values in B2:B20, and the x you want in E2.

1. Between two points: the plain formula

With x₁, y₁ in A2:B2 and x₂, y₂ in A3:B3:

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

This is the linear interpolation formula written out. It's transparent and works in every spreadsheet ever made.

2. Between two points: FORECAST.LINEAR

=FORECAST.LINEAR(E2, B2:B3, A2:A3)

Given exactly two points, the least-squares line passes through both, so this equals linear interpolation. Note the argument order: x first, then known y's, then known x's. In Excel 2013 and older the function is called FORECAST. Google Sheets accepts both names.

Pitfall: given more than two points, FORECAST.LINEAR fits a regression line through all of them. That is not interpolation. Only pass the two bracketing points.

3. In a table: INDEX and MATCH

To interpolate anywhere in a sorted table, find the row just below x with MATCH(x, range, 1), then read that row and the next:

=INDEX(B2:B20,MATCH(E2,A2:A20,1)) + (E2-INDEX(A2:A20,MATCH(E2,A2:A20,1))) * (INDEX(B2:B20,MATCH(E2,A2:A20,1)+1)-INDEX(B2:B20,MATCH(E2,A2:A20,1))) / (INDEX(A2:A20,MATCH(E2,A2:A20,1)+1)-INDEX(A2:A20,MATCH(E2,A2:A20,1)))

It's long but works in every version. If x equals the last x in the table, it returns an error, because there is no next row. Handle that with IFERROR or by extending the table.

4. In a table: LET and XLOOKUP (Excel 365, Google Sheets)

Readable, and it handles exact matches including the last row:

=LET(x,E2, xs,A2:A20, ys,B2:B20,
  x1,XLOOKUP(x,xs,xs,,-1), y1,XLOOKUP(x,xs,ys,,-1),
  x2,XLOOKUP(x,xs,xs,,1), y2,XLOOKUP(x,xs,ys,,1),
  IF(x1=x2, y1, y1+(x-x1)*(y2-y1)/(x2-x1)))

Match mode -1 returns the exact match or the next smaller value, and 1 returns the exact match or the next larger one. If x is outside the table, one lookup returns #N/A, which is a useful signal that you would be extrapolating.

5. Many x values at once: TREND for straight-line data

=TREND(B2:B3, A2:A3, E2:E50)

With the two bracketing points as known values, TREND returns the interpolated y for every x in E2:E50 in one formula (it spills in Excel 365). As with FORECAST, give it only two points. With more, it fits a regression line.

Two-dimensional (double) interpolation

For a grid with x values across row 1 (B1:K1), y values down column A (A2:A20) and data in B2:K20, with the target x in N1 and y in N2:

  1. Column index: =MATCH(N1,B1:K1,1), and row index: =MATCH(N2,A2:A20,1)
  2. Read the four corners with INDEX(B2:K20, row, col), INDEX(B2:K20, row, col+1), INDEX(B2:K20, row+1, col) and INDEX(B2:K20, row+1, col+1).
  3. Interpolate along x on both rows, then along y between the two results.

Putting each step in its own cell is much easier to debug than one giant formula. The free interpolation template has this built and laid out like a steam table.

Common mistakes

  • Unsorted x values. MATCH with type 1 and XLOOKUP's approximate modes assume ascending order. Sort the table first.
  • Feeding a whole table to FORECAST or TREND. That gives a regression line, not an interpolation.
  • Relative references drifting when you copy formulas. Lock the table ranges with $ (for example $A$2:$A$20).
  • Text that looks like numbers (common when pasting from PDFs). MATCH silently fails. Use VALUE or Paste Special → Values first.
Can Excel do spline or polynomial interpolation?

Not with a built-in function. A chart trendline can display a polynomial fit, but that is regression, not interpolation through the points. For a true cubic spline you need VBA or an add-in. Alternatively, use the cubic spline calculator, which gives every piece's coefficients, and paste them into a formula.

More guides