Lookup Table Interpolation in Excel, Python and C

Lookup table interpolation means estimating a value between the entries of a table instead of snapping to the nearest row. The method has three steps:

  1. Find the two table rows that bracket your input x (call them x1 and x2).
  2. Read their outputs y1 and y2.
  3. Return y = y1 + (x − x1) × (y2 − y1) / (x2 − x1).

That is all any interpolating lookup does, from a spreadsheet to firmware on a microcontroller. This guide uses one realistic table throughout and gives code you can paste into Excel, Python and C. All the code below was run, and its output is printed beside it. For a one-off value you can also use our online interpolation calculator.

The example table: an NTC thermistor

A thermistor is a resistor whose resistance changes with temperature. Electronics boards measure its resistance, then use a lookup table to convert that reading to a temperature. Below is a table for a common 10 kΩ NTC thermistor (B = 3950), calculated with the standard Beta equation. A real design should use the table from your part’s datasheet, but the method is identical.

Temperature (°C) Resistance (Ω)
−10 58,246
0 33,621
10 20,175
20 12,535
30 8,037
40 5,301
50 3,588
60 2,486
70 1,760
80 1,270

Note that resistance falls as temperature rises. That detail breaks a lot of lookup code, as we will see.

Worked example by hand

Problem: The board measures 7,500 Ω. What is the temperature?

7,500 Ω lies between 8,037 Ω (30 °C) and 5,301 Ω (40 °C). Treat resistance as x and temperature as y:

T = 30 + (7500 − 8037) × (40 − 30) / (5301 − 8037) T = 30 + (−537 × 10) / (−2736) T = 30 + 1.963 = 31.96 °C

The exact temperature from the Beta equation is 31.62 °C, so the 10 °C table spacing costs about 0.35 °C. We come back to how to reduce that below.

Check it with the calculator

In the linear interpolation calculator, enter X1 = 8037, Y1 = 30, X2 = 5301, Y2 = 40, X3 = 7500, and leave Y3 empty. The result is 31.96.

Lookup table interpolation in Excel

Put temperatures in A2:A11 and resistances in B2:B11, in the order shown above, with the measured resistance in E1.

Option 1: LET with INDEX and MATCH (Excel 365 and Excel 2021)

=LET(r, E1,
     i,  MATCH(r, B2:B11, -1),
     x1, INDEX(B2:B11, i),   x2, INDEX(B2:B11, i + 1),
     y1, INDEX(A2:A11, i),   y2, INDEX(A2:A11, i + 1),
     y1 + (r - x1) * (y2 - y1) / (x2 - x1))

With 7500 in E1, this returns 31.963.

The key detail is the −1 in MATCH. The resistance column is in descending order, and match type −1 finds the smallest value that is greater than or equal to the lookup value (8,037 here, in row 5 of the range). If your x column is in ascending order, as the temperature column is, use match type 1 instead, which finds the largest value less than or equal to the lookup.

Option 2: FORECAST with INDEX ranges (older Excel versions)

=FORECAST(E1,
   INDEX(A2:A11, MATCH(E1,B2:B11,-1)):INDEX(A2:A11, MATCH(E1,B2:B11,-1)+1),
   INDEX(B2:B11, MATCH(E1,B2:B11,-1)):INDEX(B2:B11, MATCH(E1,B2:B11,-1)+1))

The INDEX(...):INDEX(...) construct builds a two-cell range from the bracketing rows. FORECAST then fits a straight line through exactly two points, which is linear interpolation. Do not point FORECAST at the whole table: that fits one regression line through all ten points, which is not interpolation and is badly wrong for curved data like this. Our Excel interpolation guide covers more of these functions.

Edge cases: both formulas return an error (#N/A or #REF!) if E1 lies outside the table or equals the last entry (1,270 Ω), because i + 1 then points past the end of the range. Handle the ends first: =IF(E1>=58246, -10, IF(E1<=1270, 80, your_formula)). That clamps to the end temperatures, the same behaviour as the Python and C code below.

Lookup table interpolation in Python

With NumPy: numpy.interp

import numpy as np

ohms  = np.array([1270, 1760, 2486, 3588, 5301, 8037, 12535, 20175, 33621, 58246])
deg_c = np.array([80,   70,   60,   50,   40,   30,   20,    10,    0,     -10])

print(np.interp([7500, 25000, 3000, 500], ohms, deg_c).round(2))

Output:

[31.96  6.41 55.34 80.  ]

Two rules from the numpy.interp documentation matter here:

  1. The x values (xp) must be increasing. That is why the table above is reversed, sorted by resistance from low to high. NumPy does not check this for you. With the original descending order, np.interp(7500, ...) silently returns 80.0, which is wrong, and you get no error.
  2. Out-of-range inputs are clamped. An input of 500 Ω, below the table, returns the end value 80 rather than extrapolating. You can change this with the left and right arguments, for example left=np.nan to flag bad readings.

Pure Python with bisect

If you cannot use NumPy, for example in MicroPython, the standard library’s bisect module finds the bracket with a binary search:

from bisect import bisect_left

def table_lookup(x, xs, ys):
    """Linear interpolation in a lookup table.
    xs must be sorted in increasing order. Values outside the table are clamped."""
    if x <= xs[0]:
        return ys[0]
    if x >= xs[-1]:
        return ys[-1]
    i = bisect_left(xs, x)          # first index with xs[i] >= x
    x1, x2 = xs[i - 1], xs[i]
    y1, y2 = ys[i - 1], ys[i]
    return y1 + (x - x1) * (y2 - y1) / (x2 - x1)

# Thermistor table, sorted by resistance (increasing)
ohms  = [1270, 1760, 2486, 3588, 5301, 8037, 12535, 20175, 33621, 58246]
deg_c = [80,   70,   60,   50,   40,   30,   20,    10,    0,     -10]

for r in (7500, 25000, 3000, 500):
    print(r, round(table_lookup(r, ohms, deg_c), 2))

Output:

7500 31.96
25000 6.41
3000 55.34
500 80

The results match NumPy exactly.

Lookup table interpolation in C

This version suits embedded firmware. It uses no dynamic memory, and its binary search keeps the cost at O(log n) even for long tables.

#include <stdio.h>

/* Linear interpolation in a lookup table.
   xs must be sorted in increasing order; n >= 2.
   Values outside the table are clamped to the end values. */
double table_lookup(double x, const double *xs, const double *ys, int n)
{
    if (x <= xs[0])     return ys[0];
    if (x >= xs[n - 1]) return ys[n - 1];

    int lo = 0, hi = n - 1;            /* binary search for the bracket */
    while (hi - lo > 1) {
        int mid = (lo + hi) / 2;
        if (xs[mid] <= x) lo = mid; else hi = mid;
    }
    double t = (x - xs[lo]) / (xs[hi] - xs[lo]);
    return ys[lo] + t * (ys[hi] - ys[lo]);
}

int main(void)
{
    const double ohms[]  = {1270, 1760, 2486, 3588, 5301, 8037, 12535, 20175, 33621, 58246};
    const double deg_c[] = {80,   70,   60,   50,   40,   30,   20,    10,    0,     -10};
    const double tests[] = {7500, 25000, 3000, 500};

    for (int k = 0; k < 4; k++)
        printf("%.0f ohm -> %.2f C\n", tests[k], table_lookup(tests[k], ohms, deg_c, 10));
    return 0;
}

Compiled with gcc -Wall -Wextra -O2, it prints:

7500 ohm -> 31.96 C
25000 ohm -> 6.41 C
3000 ohm -> 55.34 C
500 ohm -> 80.00 C

On a microcontroller without a floating point unit, store the table as integers (for example tenths of a degree) and do the interpolation in fixed-point arithmetic. The structure stays the same.

Making a lookup table more accurate

Our 7,500 Ω example came out 0.35 °C high. There are three standard fixes.

1. Use closer table spacing

With entries every 1 °C instead of every 10 °C, the same reading interpolates to 31.62 °C, essentially exact. Memory is cheap in most systems, so a denser table is often the simplest fix.

2. Interpolate in a transformed variable

Thermistor resistance changes roughly exponentially with temperature, so the natural logarithm of resistance is close to linear in temperature. Interpolate on ln(R) instead of R:

T = 30 + (ln 7500 − ln 8037) × 10 / (ln 5301 − ln 8037) = 31.66 °C

That brings the error down from 0.35 °C to 0.04 °C with the same ten-row table. In the forward direction (temperature to resistance) the gain is even bigger. At 37 °C, plain interpolation gives 6,121.8 Ω against an exact 5,989.4 Ω, a 2.2 percent error. Interpolating ln(R) and converting back gives 6,005.9 Ω. The same trick works for any quantity that changes exponentially, such as vapor pressure, decibels or compound growth. See our steam table guide for a related case.

3. Use a higher-order method

Cubic splines or a short polynomial through three or four neighbouring points follow curves better than straight lines. They take more computation, and with noisy data they can overshoot. Our guides to types of interpolation and Lagrange interpolation compare the options.

Clamping vs extrapolation

You must decide what happens when the input falls outside the table:

Behaviour What it does When to use it
Clamp Returns the first or last table value Safe default for sensors and control loops
Extrapolate Extends the end segment’s straight line Only a little past the end, and only when the trend is known to continue
Error or NaN Flags the input as invalid Data validation, or for catching disconnected or shorted sensors

The code above clamps. numpy.interp clamps by default. The Excel formulas return an error. Whichever you choose, choose it on purpose: a thermistor reading of 500 Ω silently reported as “80 °C” can hide a short circuit. If you need to extend a trend past the table, read linear extrapolation first.

Common lookup table bugs

  • Unsorted or descending x values passed to a function that expects ascending order (numpy.interp, bisect, binary search in C).
  • Off-by-one errors at the last entry. An input exactly equal to the last x reads one past the end of the array. Handle the ends before searching, as the code above does.
  • Duplicate x values, which cause a division by zero in (x2 − x1).
  • Mixed units between the table and the input, such as kΩ in the table and Ω from the ADC.
  • Integer division in C or older Python, which truncates the fraction t to 0. Use floating point, or scale before dividing.

Frequently asked questions

What is the difference between a lookup and an interpolated lookup?

A plain lookup (VLOOKUP, or dict[key]) returns an exact or nearest table entry. An interpolated lookup returns a value on the straight line between the two neighbouring entries, which is smoother and usually far more accurate.

Can I interpolate in a table with two inputs?

Yes. Interpolate along one input at each of the two bracketing values of the other input, then interpolate between those results. See double interpolation and bilinear interpolation.

How do I look up x from y in the same table?

Swap the columns, as in the thermistor example, where we looked up temperature from resistance. This only works if the column you search is strictly increasing or strictly decreasing. Our inverse interpolation guide explains why.

Is binary search worth it for small tables?

For about 20 entries or fewer, a simple linear scan is fine and easier to read. Binary search pays off for long tables, or in tight loops that run thousands of times per second.

Related guides

Soren Vance
Soren Vance

Soren Vance is the Director of Quantitative Analysis & Algorithms at Interpolation Calculator, where he leads the design and optimization of the company’s core interpolation engines and data processing frameworks.

With a master’s in Computational Mathematics and over 8 years of experience in analytical modeling and numerical computing, Soren oversees the development of high‑performance methods — from spline interpolation and polynomial regressions to multidimensional estimators for real‑world datasets.

Known for his clarity of thought, Soren builds systems that balance mathematical rigor with computational efficiency. He collaborates closely with product, engineering, and UX teams to ensure users get reliable, transparent, and fast results. Outside of work, he contributes to open‑source math libraries and teaches data science workshops at local universities.

Articles: 21

Interpolation Calculator is a mathematical method used to estimate an unknown value between known data points.

Privacy

Disclaimer

Terms

GDPR

Affiliate

Refund

Company

About Us

Contact Us