Excel interpolate with hlookup

Question

I have a table with x,y values. I want to interpolate for a given x1 value between the y values using the HLOOKUP function. I have found formulas for VLOOKUPand XLOOKUP but not for HLOOKUP. I cannot use XLOOKUP because of the verson of Excel I use.

Example:

    x-values  0.2  0.5  0.8  1.0  1.25  1.5  1.75  2.0  2.5  3.0  4.0
    y-values  0.1  0.11 0.12 0.15 0.18  0.2  0.23  0.24 0.28 0.31 0.32

I need the y-value for x=1.1

I appreciate any help

Answer 1

There are various ways to interpolate: spline, polynomial, linear and so on. I assume that you want linear interpolation between 2 x values. In this case first of all, you need to find closest larger and closest lower x values:

Lower x:

=MAX(IF(B1:L1<B5,B1:L1))

Larger x:

=MIN(IF(B1:L1>B5,B1:L1))

Now need to find corresponding y's with HLOOKUP.

Lower x's y:

=HLOOKUP(A9,B1:L2,2,FALSE)

Larger x's y:

=HLOOKUP(B9,B1:L2,2,FALSE)

Now that you have all needed values you can write linear interpolation formula or you can use excel formula FORECAST. With 2 x's and 2 y's it will work as linear interpolation.

=FORECAST(B5,A11:B11,A9:B9)

Formula without using helper cells:

=FORECAST(B5,CHOOSE({1,2},HLOOKUP(MAX(IF(B1:L1<B5,B1:L1)),B1:L2,2,FALSE),HLOOKUP(MIN(IF(B1:L1>B5,B1:L1)),B1:L2,2,FALSE)),CHOOSE({1,2},MAX(IF(B1:L1<B5,B1:L1)),MIN(IF(B1:L1>B5,B1:L1))))

Result: