Interpolation of data

[Cobra17]

I'm trying to interpolate data from some tables and I'm having some issues.


1. This is my first Vlookup that works. I'm looking to interpolate across columns
IFERROR((VLOOKUP(MDT,'BDD Library.xlsm'!B313A1,MATCH(MDT,'BDD Library.xlsm'!A1Temps,0),0))*1000," ")
If I have 120 as my 'MDT', I want to find my 'A1Temps' for my 'B313A1', which is my A105 or A106 B

2. This is my second Vlookup that works. I'm looking to interpolate between rows
VLOOKUP(T,INDIRECT("'BDD Library.xlsm'!" & Class),MATCH(Rating,'BDD Library.xlsm'!FRating),0)
If I have a 'T' of 130 and a 'FRating' of 150, i'm looking to interpolate 265 and 260


I'm fairly new to excel so any help would be appreciated. I'm use to working with Access .
I have downloaded a free interpolation add-on but i can't figure it out. I'm open to any other free add-ons or just coding it in.

[AliGW]

What do you mean here by interpolate?

There are instructions at the top of the page explaining how to attach your sample workbook.

[Cobra17]

added sample

[AliGW]

Thank you. Now you need to explain what we are looking at. If these are results tables, where is the source data? If not, where are the results mocked up? Where are the formulae you mentioned???

[MrShorty]

I don't quite understand example 1.

Example 2 looks like bilinear interpolation, which shows up with some regularity on the board:
Two possible approaches here: https://www.excelforum.com/excel-for...rpolation.html
A little more complicated situation: https://www.excelforum.com/excel-pro...le-graphs.html
Another example: https://www.excelforum.com/excel-gen...ml#post4300787

There are more, but those should suffice to show the basic steps to a bilinear interpolation.

Instinct suggests that your first example is basically a 1D interpolation (with a lookup to decide which "row" to use in the interpolation). Recognizing that bilinear interpolation is often just 1D interpolation applied twice, What you learn from the 2D interpolations for example 2 may provide the solutions for example 1.

[Cobra17]

I messed around with it and still can't figure it out. I've attached the trimmed out workbooks.

[MrShorty]

Your sample does not include any of your attempts to implement any of the given suggestions, so I'm not sure where you got stuck. Here's what I did in Workbook.xlsm!Design Data.

1) I need to know the row number for the material in A1:A4 (be wary of merged cells in the future. they have a way of creating problems). This is a simple MATCH() function. In M14, I enter =MATCH(A14,'[BDD Library.xlsm]TableA1'!$A$2:$A$4,1). Note the mix of relative and absolute references and note the use of the approximate match option in the 3rd argument.
2) I need the column number for the temperature. This is also a MATCH() function. In N14, I enter =MATCH(MDT,'[BDD Library.xlsm]TableA1'!$C$1:$Z$1,1)
3) Now I know where to find the data in the source table, I can simply extract the four values that I need for the 1D interpolation with INDEX() functions.
3a) In O14, =INDEX('[BDD Library.xlsm]TableA1'!$C$1:$Z$1,$M14) to get the lower temperature value.
3b) In P14, =INDEX('[BDD Library.xlsm]TableA1'!$C$1:$Z$1,$M14+1) to get the upper temperature value.
3c) In Q14, =INDEX('[BDD Library.xlsm]TableA1'!$C$2:$Z$4,$N14,$M14) to get the value corresponding to the lower temperature value and the material.
3d) In R14, =INDEX('[BDD Library.xlsm]TableA1'!$C$2:$Z$4,$N14,$M14+1) to get the value corresponding to the upper temperature value and the material.
4) With all of these values extracted from the source data, the linear interpolation is a simple algebraic expression. (yhi-ylo)*(T-Tlo)/(Thi-Tlo)+ylo Enter that formula into F14 (obviously substituting the appropriate cell references for the values).

The procedure in Flanges will be basically the same -- except that you have the complication of going from 1D to 2D bilinear interpolation where you will need to extract eight values -- Tlo, Thi, ratinglo, ratiinghi, and the four y values that correspond. But the process is still the same -- A MATCH() function to find the row #, a MATCH() function to find the column #, INDEX() functions to extract the eight values, then combine them all into the proper bilinear interpolation formula (see the Wikipedia links in the previous threads).

Since you may be unfamiliar with these functions:
INDEX() help file: https://support.office.com/en-us/art...2-b56b061328bd
MATCH() help file: https://support.office.com/en-us/art...9-533f4a37673a