Index match

[kristipham]

Based on the user's selection of enclosure types, there are 4 different tables based off of temperature and watt/ft^2. In one spread sheet, it calculates the change in temperature (delta t) and we want it to point to the watts/ft^2 on the specific enclosure type that the user has selected. Rather than a nested If statement, we want to use the index match function. Please explain step by step which option is a better route just in case there are other enclosures added.

[/URL][/IMG]

[/URL][/IMG]

Rather than using this:
=IF(B4="Stainless Steel",VLOOKUP(B16,Sheet3!A3:B53,2,TRUE),IF(B4="ANSI 61 Gray",VLOOKUP(B16,Sheet3!D3:E57,2,TRUE),IF(B4="Aluminum",VLOOKUP(B16,Sheet3!G3:H51,2,TRUE),IF(B4="White",VLOOKUP(B16,Sheet3!J3:K37,2,TRUE)))))

 

[L. Howard]

Here is my vision of how that may look.

I assume the delta t values are numbers and not in sequence (but are sorted low to high) in column A sheet 3.
B4 sheet 1 is a drop down with the enclosure types.

Howard

Excel 2012
	B	C
4	ANSI 61 Gray	AN_GR_4
5
6
7
8
9
10
11
12
13
14
15
16	17
Sheet1

Cell Formulas
Range	Formula
C4	=INDEX(Sheet3!$B$4:$E$10,MATCH($B$16,Sheet3!$A$4:$A$10,1),MATCH(B4,Sheet3!$B$3:$G$3,0))

Excel 2012
	A	B	C	D	E
3		Stainless Steel	ANSI 61 Gray	Aluminum	White
4	0	SS-1	AN_GR_1	Alum-1	Wit-1
5	5	SS-2	AN_GR_2	Alum-2	Wit-2
6	10	SS-3	AN_GR_3	Alum-3	Wit-3
7	15	SS-4	AN_GR_4	Alum-4	Wit-4
8	20	SS-5	AN_GR_5	Alum-5	Wit-5
9	25	SS-6	AN_GR_6	Alum-6	Wit-6
10	30	SS-7	AN_GR_7	Alum-7	Wit-7
Sheet3

 

[L. Howard]

I failed to point out the.

...just in case there are other enclosures added.

The enclosure range...

...MATCH(B4,Sheet3!$B$3:$G$3,0))

In this, my example, the current number of enclosures uses B3 to E3, but the range available is B3 to G3, so you could add two more types without changing anything. I would think one would normally match the enclosure types range in the formula to the exact number of enclosures on the B row and if additions were needed you would add them and adjust the formula to catch the new ones.

Perhaps you already knew that, as you mention adding enclosures in your post.

Howard

 

[kristipham]

Based on the tables on sheet 3, how would that match up? It showed errors and did not formulate it correctly.

[/URL][/IMG]

Not sure if this is a better way of formatting the tables or not, all the information in the table is critical.

 

[L. Howard]

It was a guess on my part from looking at the formula you posted.
Don't have any idea what or how the image you post plays into a solution.
Don't know what is in the tables or how they are formatted.

Can you post a small example of the data that is in play and an expected result with certain lookup values?

Howard

 

[kristipham]

Sheet 2: ENCLOSURE HEIGHT (IN)	90
ENCLOSURE WIDTH (IN)	72
ENCLOSURE DEPTH (IN)	24
Select Enclosure Type: (Drop down list)	Stainless Steel
Watt/ft2 of Enclosure: (Feeds a formula)	#N/A
ENCLOSURE AREA (ft2) <tbody> </tbody>	144
<tbody> </tbody>
MAX AMBIENT TEMP OUTSIDE ENCLOSURE (°F)	104
INTERNAL TEMP (°F)	122
CONVECTION HEAT TRANSFER COEFFICIENT OUTSIDE ENCLOSURE	1.6
CONVECTION HEAT TRANSFER COEFFICIENT INSIDE ENCLOSURE	2
INSULATION VALUE (R VALUE)	0
<tbody> </tbody>
HEAT TRANSFER (BTU/HR/FT2)	-16
DELTA T (information that is needed)	-18

<colgroup><col><col></colgroup><tbody>
</tbody>

Sheet 3:

Stainless Steel ANSI 61 Gray Aluminum White
Delta T Watt per ft2 Delta T Watt per ft2 Delta TWatt per ft2 Delta T Watt per ft2
-40 25 -40 27 -40 24 -40 17
-38.56 24.5 -38.51851852 26.5 -38.5625 23.5 -38.51470588 16.5
-37.12 24 -37.03703704 26 -37.125 23 -37.02941176 16
-35.68 23.5 -35.55555556 25.6 -35.6875 22.5 -35.54411765 15.5
-34.24 23 -34.07407407 25 -34.25 22 -34.05882353 15
-32.8 22.5 -32.59259259 24.5 -32.8125 21.5 -32.57352941 14.5
-31.36 22 -31.11111111 24 -31.375 21 -31.08823529 14
-29.92 21.5 -29.62962963 23.5 -29.9375 20.5 -29.60294118 13.5
-28.48 21 -28.14814815 23 -28.5 20 -28.11764706 13
-27.04 20.5 -26.66666667 22.5 -27.0625 19.5 -26.63235294 12.5
-25.6 20 -25.18518519 22 -25.625 19 -25.14705882 12
-24.16 19.5 -23.7037037 21.5 -24.1875 18.5 -23.66176471 11.5
-22.72 19 -22.22222222 21 -22.75 18 -22.17647059 11
-21.28 18.5 -20.74074074 20.5 -21.3125 17.5 -20.69117647 10.5
-19.84 18 -19.25925926 20 -19.875 17 -19.20588235 10
-18.4 17.5 -17.77777778 19.5 -18.4375 16.5 -17.72058824 9.5
-16.96 17 -16.2962963 19 -17 16 -16.23529412 9
-15.52 16.5 -14.81481481 18.5 -15.5625 15.5 -14.75 8.5
-14.08 16 -13.33333333 18 -14.125 15 -13.26470588 8
-12.64 15.5 -11.85185185 17.5 -12.6875 14.5 -11.77941176 7.5
-11.2 15 -10.37037037 17 -11.25 14 -10.29411765 7
-9.76 14.5 -8.888888889 16.5 -9.8125 13.5 -8.808823529 6.5
-8.32 14 -7.407407407 16 -8.375 13 -7.323529412 6
-6.88 13.5 -5.925925926 15.5 -6.9375 12.5 -5.838235294 5.5
-5.44 13 -4.444444444 15 -5.5 12 -4.352941176 5
-4 12.5 -2.962962963 14.5 -4.0625 11.5 -2.867647059 4.5
-2.56 12 -1.481481481 14 -2.625 11 -1.382352941 4
-1.12 11.5 -1.28786E-14 13.5 -1.1875 10.5 0.102941176 3.5
0.32 11 1.481481481 13 0.25 10 1.588235294 3
1.76 10.5 2.962962963 12.5 1.6875 9.5 3.073529412 2.5
3.2 10 4.444444444 12 3.125 9 4.558823529 2
4.64 9.5 5.925925926 11.5 4.5625 8.5 6.044117647 1.5
6.08 9 7.407407407 11 6 8 7.529411765 1
7.52 8.5 8.888888889 10.5 7.4375 7.5 9.014705882 0.5
8.96 8 10.37037037 10 8.875 7 10.5 0
10.4 7.5 11.85185185 9.5 10.3125 6.5
11.84 7 13.33333333 9 11.75 6
13.28 6.5 14.81481481 8.5 13.1875 5.5
14.72 6 16.2962963 8 14.625 5
16.16 5.5 17.77777778 7.5 16.0625 4.5
17.6 5 19.25925926 7 17.5 4
19.04 4.5 20.74074074 6.5 18.9375 3.5
20.48 4 22.22222222 6 20.375 3
21.92 3.5 23.7037037 5.5 21.8125 2.5
23.36 3 25.18518519 5 23.25 2
24.8 2.5 26.66666667 4.5 24.6875 1.5
26.24 2 28.14814815 4 26.125 1
27.68 1.5 29.62962963 3.5 27.5625 0.5
29.12 1 31.11111111 3 29 0
30.56 0.5 32.59259259 2.5
32 0 34.07407407 2
35.55555556 1.5
37.03703704 1
38.51851852 0.5
40 0


(i guess the pictures are not appearing, so i could only copy and paste over from excel)

 

[kristipham]

Please ignore the last post, it will not format corrected.
Please click on this link to view images:
Based On The User's Selection Of Enclosure Types, ... | Chegg.com

 

[L. Howard]

Okay, so the four enclosure types each have a table unique to a given Delta t vs watt/per/ft2? i.e. SSteel = -40 & 25 and scaled in .5 ft increments.
And is this fixed for the particular type?
Can the scale be sorted low to hi on the delta t column? (A & B columns for SSteel sorted on A, low to high)
What is the formula ( delta t vs ft2) to make a scale for my test sheet. (can't copy from a image)

Howard

 

[kristipham]

column c column d column f
Delta t watt/ft^2 =(C53-C3)/(ROW(C53)-ROW(C3))
-40 (starting point) 25 (going down by increments of 0.5
=c3+$f$2


32(end point) 0 (end point)


(Stainless) and so on with the other enclosures

 

[kristipham]

column c \column d \column f
Delta t \ watt/ft^2 \=(C53-C3)/(ROW(C53)-ROW(C3))
-40 (starting point) \ 25 (going down by increments of 0.5
=c3+$f$2


32(end point) \0 (end point)


(Stainless) and so on with the other enclosures