Detailed Interpolation....help!

[GPSmith*]

Dear All

This is my first post on here and I really hope someone will be able to assist me.

Interpolation Table.jpg

I have an issue with the table array above. I have been trying for quite a while to come up with a formula that will return a value from row 8.

What I need it to do is, when the "angle of viewing terrace" is selected, referenced in from a different cell/sheet, and a "horizontal distance between barriers" selected the formula selects the appropriate line to suit the slope, then finds where in the line the horizontal distance lies and interpolates within the line to return an interpolated Load from row 8.

I really hope you can follow that.

This has been driving me mad for a fair few hours but I am sure their is a simple solution or a way in which the data can be rearranged to deliver the desired result.

As an example, say the following values were selected by the user; Angle of Terrace <=5, Horizontal distance between barriers = 3.15m. In this instance it needs to select the row 3 and recognise that 3.15m lies between 3 and 3.3. Then through interpolation give a corresponding value from row 8 which should be 3.2kN/m.

I look forward to your comments.

Regards

Gary

Interpolation Table.xlsx

[Kaper]

Well, you can finally put it in one formula (see below), but will be a long one (at least I can set it up working properly with long one), so in enclosed file it is made step by step with calculating in which row we are, then which column, then how far within scope this-next column and finally the load.

This long formula would be:

=INDEX(B8:F8,1,MIN(MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1)+1,5))*IFERROR((INDEX(B3:F7,MATCH(L3,A3:A7,0),MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1))-L4)/(INDEX(B3:F7,MATCH(L3,A3:A7,0),MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1))-INDEX(B3:F7,MATCH(L3,A3:A7,0),MIN(MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1)+1,5))),0)+INDEX(B8:F8,1,MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1))*(1-IFERROR((INDEX(B3:F7,MATCH(L3,A3:A7,0),MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1))-L4)/(INDEX(B3:F7,MATCH(L3,A3:A7,0),MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1))-INDEX(B3:F7,MATCH(L3,A3:A7,0),MIN(MATCH(L4,OFFSET(B2:F2,MATCH(L3,A3:A7,0),0),-1)+1,5))),0))

with only 2 variables: your angle in L3 and distance in L4