Lookup-like FUnction and Double Linear Interpolation-need help

Hi everyone! I am asking for help if anyone could give me formula on this. First take a look on the attached spreadsheet, Look-up and Double Linear Interpolation.xlsx. I need a formula for computing the volume by looking up in the table provided. The formula should take account the following conditions:

a) when values are inputted to cells B3 and B4, where each value EXACTLY appears on the row and column heads of the table, then the value for the volume in cell B5 appears.
For example, if B3=20 and and B4=30, then looking-up in the table, B5=9;

b) when the value inputted in cell B3 OR B4, is NOT on the row or column heading, the value that appears in the row or column heads would just be "looked up" in the table, and the other one will involve linear interpolation to compute for the volume.
For example, B3=24 and B4=10, then the value of the volume (B5) should be in between 6 and 7. To do the interpolation, first notice that the values of volume for B4=10 as given in the table are 5, 6 ,7 and 8 for pressures 10, 20, 30 and 40, respectively. And 24 is in between pressures 20 and 30, which corresponds to volume 6 and 7, respectively. So we have 2 given points (20,6) and (30,7) and a point (24,Y), where Y=volume. Using the 2-point equation of a line, Y is computed as follows:

Y=[(30-24)/(30-20)](6) + [(24-20)/(30-20)](7) = 6.40 so B5=6.40.

In general, if point (X,Y) is in between (X1,Y1) and (X2,Y2), then Y=[(X2-X)/(X2-X1)](Y1) + [(X-X1)/(X2-X1)](Y2).

c) when the value inputted in cells B3 AND B4 neither appears in the column and row heads, a double linear interpolation is done to compute for the volume.
For example, B3=25 and B4=37. 25 is in between 20 and 30 and 37 is in between 30 and 40. Based on the table

30 37 40
20 7 8
25 M=?
30 9 10

where M=volume (which appears in cell B5). Then M is computed as follows:

M={[(40-37)/(40-30)](7)+[(37-30)/(40-30)](8)}[(30-25)/(30-20)] + {[(40-37)/(40-30)](9)+[(37-30)/(40-30)](10)}[(25-20)/(30-20)]=8.70 so B5=8.70.

The general formula is shown in the spreadsheet.

=INDEX($H$6:$K$9,MATCH($B$4,$G$6:$G$9,0),MATCH($B$3,$H$5:$K$5,0))


Azumi

Change to this:

=INDEX($H$6:$K$9,MATCH($B$4,$G$6:$G$9,1),MATCH($B$3,$H$5:$K$5,1))

Thank you azumi for your help. Do you have any idea how to do the interpolation? I mean the formula is easy but for values not in the table, I do not know how to make a formula that automatically looks up for the the 2 values found in the table where the given value is intermediate to these. For example, 23 is between 20 and 30..Im really having a hard time figuring out how to do the last 2 conditions.

Here you go. This does the interpolation too.

I've left it a bit messy so you can see how it works. It picks up the 4 corners of your matrix using hlookup and then interpolates.

By the way. I think your example for the double interpolation is wrong. Mine gets a different number. Try mine and check it. Let me know if you disagree

Thank you so much Crooza just as I wanted..By the way, yeah, I made a mistake. I was just using a handheld calculator something wrong with my inputs perhaps.

Great. Glad it worked. If this has fixed you problem mark it as solved