Index match

kristipham

New Member
Joined
Jul 24, 2017
Messages
6
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)))))
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">ANSI 61 Gray</td><td style="text-align: right;;">AN_GR_4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet3!$B$4:$E$10,MATCH(<font color="Red">$B$16,Sheet3!$A$4:$A$10,1</font>),MATCH(<font color="Red">B4,Sheet3!$B$3:$G$3,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />


<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">Stainless Steel</td><td style="text-align: center;;">ANSI 61 Gray</td><td style="text-align: center;;">Aluminum</td><td style="text-align: center;;">White</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">0</td><td style="text-align: center;;">SS-1</td><td style="text-align: center;;">AN_GR_1</td><td style="text-align: center;;">Alum-1</td><td style="text-align: center;;">Wit-1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: center;;">SS-2</td><td style="text-align: center;;">AN_GR_2</td><td style="text-align: center;;">Alum-2</td><td style="text-align: center;;">Wit-2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">10</td><td style="text-align: center;;">SS-3</td><td style="text-align: center;;">AN_GR_3</td><td style="text-align: center;;">Alum-3</td><td style="text-align: center;;">Wit-3</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">15</td><td style="text-align: center;;">SS-4</td><td style="text-align: center;;">AN_GR_4</td><td style="text-align: center;;">Alum-4</td><td style="text-align: center;;">Wit-4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">20</td><td style="text-align: center;;">SS-5</td><td style="text-align: center;;">AN_GR_5</td><td style="text-align: center;;">Alum-5</td><td style="text-align: center;;">Wit-5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">25</td><td style="text-align: center;;">SS-6</td><td style="text-align: center;;">AN_GR_6</td><td style="text-align: center;;">Alum-6</td><td style="text-align: center;;">Wit-6</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">30</td><td style="text-align: center;;">SS-7</td><td style="text-align: center;;">AN_GR_7</td><td style="text-align: center;;">Alum-7</td><td style="text-align: center;;">Wit-7</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br />
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jul 24, 2017
Messages
6
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

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jul 24, 2017
Messages
6
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)
 

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
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

New Member
Joined
Jul 24, 2017
Messages
6
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

New Member
Joined
Jul 24, 2017
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,104
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top