How to interpolate in Excel without getting the wrong answer

Excel has no INTERPOLATE function. It has pieces you can assemble into one, and it has FORECAST.LINEAR, which looks like the answer and usually is not.

Three methods below, each on the same table, each with the number it returns. Copy whichever fits your version of Excel.

The quick answer

If you have Excel 365, this one formula does the whole job. Put your target x in D2.

=LET(k, MATCH(D2,$A$2:$A$6,1), x1, INDEX($A$2:$A$6,k), y1, INDEX($B$2:$B$6,k), x2, INDEX($A$2:$A$6,k+1), y2, INDEX($B$2:$B$6,k+1), y1 + (D2-x1)*(y2-y1)/(x2-x1))

It finds the two table rows around your target and applies the standard formula to them. Older Excel needs the longer version further down.

The example table

Water viscosity against temperature, one row per 10 degrees, sitting in cells A1:B6. Temperature in column A, viscosity in column B.

Target temperature goes in D2. Use 35 °C.

Work out the right answer by hand first, so you have something to check the formulas against. 35 sits between the 30 and 40 rows, halfway:

0.798 + 0.5 × (0.653 − 0.798) = 0.798 − 0.0725 = 0.7255 cP

Hold on to 0.7255. Any method that gives you something else is answering a different question.

Method 1: INDEX and MATCH

Use this when you want to see what is happening. MATCH with a third argument of 1 finds the last row whose value is at or below your target, which is exactly the lower of your two rows.

=MATCH(D2, $A$2:$A$6, 1)

With 35 in D2 this returns 2, meaning the second item in the range, which is the 30 row. Not the row number on the sheet, the position within the range you gave it. That distinction trips people up constantly.

Put that in a helper cell and pull out the four numbers you need:

E2: =MATCH(D2, $A$2:$A$6, 1)  → 2
F2: =INDEX($A$2:$A$6, E2) → x1 = 30
G2: =INDEX($B$2:$B$6, E2) → y1 = 0.798
H2: =INDEX($A$2:$A$6, E2+1) → x2 = 40
I2: =INDEX($B$2:$B$6, E2+1) → y2 = 0.653

The E2+1 is what grabs the row above your target. Then the formula itself:

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

Result: 0.7255. Correct.

To do it in one cell without helpers, substitute each MATCH back in:

=INDEX($B$2:$B$6, MATCH(D2,$A$2:$A$6,1))
+ (D2 − INDEX($A$2:$A$6, MATCH(D2,$A$2:$A$6,1)))
* (INDEX($B$2:$B$6, MATCH(D2,$A$2:$A$6,1)+1) − INDEX($B$2:$B$6, MATCH(D2,$A$2:$A$6,1)))
/ (INDEX($A$2:$A$6, MATCH(D2,$A$2:$A$6,1)+1) − INDEX($A$2:$A$6, MATCH(D2,$A$2:$A$6,1)))

Long, but it works in every Excel back to 2007 and in Google Sheets with no changes. On Excel 365, the LET version at the top of this page does the same thing and only runs MATCH once instead of four times.

Method 2: FORECAST.LINEAR on two rows

FORECAST.LINEAR draws a best fit straight line through whatever points you give it, then reads a value off that line. Give it exactly two points and there is only one line that fits, so it returns the same thing as interpolation.

The work is in feeding it only your two rows, which OFFSET does:

=FORECAST.LINEAR(D2,
  OFFSET($B$1, MATCH(D2,$A$2:$A$6,1), 0, 2, 1),
  OFFSET($A$1, MATCH(D2,$A$2:$A$6,1), 0, 2, 1))

MATCH gives 2, so each OFFSET starts two rows below row 1, landing on row 3, and takes 2 rows. That is A3:A4 and B3:B4, your bracketing pair. Result: 0.7255. Correct.

One performance note. OFFSET recalculates every time anything anywhere in the workbook changes. With a few of these you will not notice. With tens of thousands you will. Swapping in INDEX($B$2:$B$6,k):INDEX($B$2:$B$6,k+1) builds the same two-cell range without that cost.

The version that returns a wrong answer

Here is what most people write first:

=FORECAST.LINEAR(D2, $B$2:$B$6, $A$2:$A$6)

That fits one straight line through all five rows and reads 35 off it. The best fit slope is −0.01323 cP per degree, and the line passes through the average point (40, 0.6932), giving:

0.6932 + (35 − 40) × (−0.01323) = 0.6932 + 0.06615 = 0.7594 cP

0.7594 instead of 0.7255. That is 4.7 percent out, with no error, no warning, nothing red on the screen.

For reference, the true viscosity at 35 °C is about 0.719 cP. Interpolating between two rows is 0.9 percent off. Fitting a line through the whole table is 5.6 percent off. Viscosity drops steeply and then flattens, and one straight line cannot follow that shape.

Whole-table FORECAST.LINEAR is the right tool only when you actually believe the relationship is a straight line across the entire range and your measurements are noisy. That is fitting a trend, not interpolating.

Method 3: XLOOKUP, for Excel 365 and 2021

XLOOKUP with a match mode of −1 gives you the next smaller entry, and 1 gives the next larger. Four lookups get you all four numbers:

=LET(
  x1, XLOOKUP(D2, $A$2:$A$6, $A$2:$A$6, , -1),
  y1, XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6, , -1),
  x2, XLOOKUP(D2, $A$2:$A$6, $A$2:$A$6, , 1),
  y2, XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6, , 1),
  IF(x2=x1, y1, y1 + (D2-x1)*(y2-y1)/(x2-x1)))

The IF at the end is not decoration. When your target lands exactly on a table value, both lookups return the same row, x2 minus x1 is zero, and you get a #DIV/0! error on the easiest input you could possibly give it.

Things that will bite you

An unsorted x column. MATCH with type 1 and XLOOKUP with modes 1 or −1 both assume the column is ascending. On unsorted data they return confident nonsense rather than an error. Sort first.

A target above your last row. MATCH returns the final position, then INDEX with k+1 walks off the end and gives #REF!. Wrap the formula in IFERROR, or clamp k with MIN(MATCH(...), COUNT($A$2:$A$6)-1) if you deliberately want to project past the end. Be aware that projecting past the end is extrapolation, which is a much weaker claim than interpolation.

A target below your first row. Same problem from the other side. MATCH returns #N/A.

Numbers stored as text. Imported data often arrives as text that looks numeric. It sorts differently and never matches properly. Test a cell with =ISNUMBER(A2) if results look strange.

Blank cells inside the range. MATCH reads a blank as zero, which breaks the ascending order it depends on. Take empty rows out of the lookup range.

Tables with two variables

For a table with one variable across the top and another down the side, like a compressibility chart, you need bilinear interpolation: interpolate along two rows, then interpolate between those two results. Excel has no built-in for it, but three applications of the Method 1 formula get you there.

What Excel cannot do

There is no cubic spline function. TREND or LINEST with powers of x will fit a polynomial, which is a different thing and behaves badly once you have more than a few points, as Runge's phenomenon shows.

If you genuinely need a spline, either build it from the step-by-step method or paste your points into the interpolation calculator and read the value off. For the maths behind the formula used throughout this page, see the linear interpolation tutorial.