Interpolation and Vlookup help

[mel1660]

I have two worksheets, one contains table of tank volumes as per ullage level of tank, I call this Table 1, another worksheet contains computation of tank content based on the Table 1.I need to make a formula that would look up the interpolated result from the table and display the result in Tank Condition Report as per ullage, also if Trim C1 changes to another value, I need to look up from the corresponding column in Table 1. How can I combine these formulas into one formula. I have tried combining Vlookup and Trend function without any success, Any help will be greatly appreciated,am using Excel 2003. Thank you very much.

Tank Condition Report Worksheet
A B C
1 Trim 2.00
2 Tank Condition Report
3 Tank Ullage M3
4 Wing (P) 27 M3 from Table1(??Formula)
5 38 ??Formula
6 46 ??Formula

Table 1
Table 1
A B C D E F
Trim 0.00 4.00 3.00 2.00 1.00
Ullage M3 M3 M3 M3 M3
1 25 632.10 632.10 632.20 632.20 632.20
2 30 631.50 631.90 631.80 631.80 631.70
3 35 630.60 631.30 631.20 631.00 630.80
4 40 629.50 630.40 630.20 629.90 629.70
5 45 628.30 629.30 629.10 628.80 628.60
6 50 627.20 628.20 627.90 627.70 627.50
7 55 626.10 627.10 627.90 626.60 626.30
8 60 625.00 625.90 626.80 625.50 626.20

 

[fairwinds]

Hi,

This is the same formula as in my reply in your other thread. I just added a match for Trim to find you the right column.


=(INDEX($B$3:$F$10,MATCH(I3,$A$3:$A$10),MATCH($I$2,$B$1:$F$1,0))-INDEX($B$3:$F$10,MATCH(I3,$A$3:$A$10)+1,MATCH($I$2,$B$1:$F$1,0)))/(INDEX($A$3:$A$10,MATCH(I3,$A$3:$A$10))-INDEX($A$3:$A$10,MATCH(I3,$A$3:$A$10)+1))*(I3-INDEX($A$3:$A$10,MATCH(I3,$A$3:$A$10)))+INDEX($B$3:$F$10,MATCH(I3,$A$3:$A$10),MATCH($I$2,$B$1:$F$1,0))

Book1
	A	B	C	D	E	F	G	H	I	J
1	Trim	0	4	3	2	1
2	Ullage	M3	M3	M3	M3	M3		Trim	2.00
3	25	632.10	632.10	632.20	632.20	632.20		Ullage	27.00	632.04
4	30	631.50	631.90	631.80	631.80	631.70			38.00	630.34
5	35	630.60	631.30	631.20	631.00	630.80			46.00	628.58
6	40	629.50	630.40	630.20	629.90	629.70
7	45	628.30	629.30	629.10	628.80	628.60
8	50	627.20	628.20	627.90	627.70	627.50
9	55	626.10	627.10	627.90	626.60	626.30
10	60	625.00	625.90	626.80	625.50	626.20
11
Sheet1

 

[mel1660]

Thank you very much for your help, I was able to finish my worksheets on time, Hope I can give back in the future ,the kindness you have done