Spreadsheet Practice

How to interpolate between values in a lookup table in Excel

Formula (Excel and Google Sheets)

=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))

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)).

Result on the example
262.3 in E2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.

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.

=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))

Result262.3

  1. i3

    A name defined in LET, or a named range.

  2. E127

    The value in E1.

  3. A2:A87 rows × 1 col: 0, 10, 20, 30, …

    A range: 7 cells in one column.

  4. MATCH(E1,A2:A8,1)3

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueE127
    • lookup_arrayA2:A87 rows × 1 col: 0, 10, 20, 30, …
    • match_type1
  5. dLo20

    A name defined in LET, or a named range.

  6. INDEX(A2:A8,i)20

    INDEX: Value at a given row and column of a range.

    • arrayA2:A87 rows × 1 col: 0, 10, 20, 30, …
    • row_numi3
  7. dHi30

    A name defined in LET, or a named range.

  8. i+14

    The + operator adds.

  9. INDEX(A2:A8,i+1)30

    INDEX: Value at a given row and column of a range.

    • arrayA2:A87 rows × 1 col: 0, 10, 20, 30, …
    • row_numi+14
  10. vLo172

    A name defined in LET, or a named range.

  11. B2:B87 rows × 1 col: 0, 64, 172, 301, …

    A range: 7 cells in one column.

  12. INDEX(B2:B8,i)172

    INDEX: Value at a given row and column of a range.

    • arrayB2:B87 rows × 1 col: 0, 64, 172, 301, …
    • row_numi3
  13. vHi301

    A name defined in LET, or a named range.

  14. i+14

    The + operator adds.

  15. INDEX(B2:B8,i+1)301

    INDEX: Value at a given row and column of a range.

    • arrayB2:B87 rows × 1 col: 0, 64, 172, 301, …
    • row_numi+14
  16. E1-dLo7

    The - operator subtracts.

  17. vHi-vLo129

    The - operator subtracts.

  18. E1-dLo)*(vHi-vLo903

    The * operator multiplies.

  19. dHi-dLo10

    The - operator subtracts.

  20. E1-dLo)*(vHi-vLo)/(dHi-dLo90.3

    The / operator divides.

  21. vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo262.3

    The + operator adds.

  22. 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))262.3

    LET: Names values inside a formula.

    • name1i3
    • value1MATCH(E1,A2:A8,1)3
    • name2, value2dLo20
    • calculationINDEX(A2:A8,i)20
    • name2dHi30
    • value2INDEX(A2:A8,i+1)30
    • name3vLo172
    • value3INDEX(B2:B8,i)172
    • name4vHi301
    • value4INDEX(B2:B8,i+1)301
    • name5vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo262.3

In Google Sheets

The same formula works in Google Sheets: =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)). We ran it in Google Sheets mode on the same data and it returns 262.3.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=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)))262.3The 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Interpolate between values in a lookup table

The tank chart gives the volume every 10 inches. In E2, estimate the volume for the depth in E1 by linear interpolation between the two nearest rows.

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 $.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.