# How to interpolate between values in a lookup table in Excel

Find the row below the value with MATCH, then take the straight line to the next row: `=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo))`.

**Excel:** `=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Depth (in) | Volume (gal) |  | Measured depth (in) | 27 |
| 0 | 0 |  | Volume (gal) |  |
| 10 | 64 |  |  |  |
| 20 | 172 |  |  |  |
| 30 | 301 |  |  |  |
| 40 | 432 |  |  |  |
| 50 | 538 |  |  |  |
| 60 | 601 |  |  |  |

Formula in E2: `=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo))`

Result: 262.3

## How it works

MATCH with 1 returns the last depth at or below 27 inches, the 20-inch row. The value lies 7/10 of the way to the 30-inch row, so the volume is 172 + 7 × (301 − 172) / 10 = 262.3 gallons. LET names the two points so the straight-line formula reads like the math.

## Other ways

- `=INDEX(B2:B8,MATCH(E1,A2:A8,1))+(E1-INDEX(A2:A8,MATCH(E1,A2:A8,1)))*(INDEX(B2:B8,MATCH(E1,A2:A8,1)+1)-INDEX(B2:B8,MATCH(E1,A2:A8,1)))/(INDEX(A2:A8,MATCH(E1,A2:A8,1)+1)-INDEX(A2:A8,MATCH(E1,A2:A8,1)))`: The same calculation without LET, for Excel 2019 and earlier.

## Common mistakes

- The first column must be sorted from smallest to largest for MATCH with 1.
- A value equal to the last row (60) has no next row, so `i+1` returns #REF!. Handle it with `IF(E1>=MAX(A2:A8),INDEX(B2:B8,ROWS(B2:B8)),…)`.
- A plain approximate lookup returns 172, the 20-inch value, which is off by 90 gallons here.

## Questions

**Is there a built-in function?**

FORECAST.LINEAR on the two surrounding points does the same: give it the x and y values of rows i and i+1.

**How do I interpolate a whole column of depths?**

Put the formula next to each depth with `E1` replaced by the cell, and lock the table with $.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/interpolate-lookup-table