AlexinJap
New Member
- Joined
- Jan 27, 2022
- Messages
- 4
- Office Version
- 2021
- Platform
- MacOS
Hello I am new here.
I've got way too difficult task for my current skills, dear members I appreciate any help or guidance.
Needs to find data in table based on 5 conditions to show it in "Required power" cell.
I have added conditions manually via Data Validation.

My data sheet structure conditions are:
Condition 1 is material thickness (100mm,75mm)
Condition 2 Outside temperature (43, 35,...)
Condition 3 Desirable temperature inside (15, 10, 5, 0)
Condition 4,5 warehouse size (I think here I can use VLOOKUP + HLOOKUP.)

Only the final search step is clear for me, but I don't know how to get to that point.
Can't even think how to start.
IF material thickness 100mm search on the left table, IF 75mm search in range on the right table,
Then I need to come to Outside temperature condition and so on, until finally I can search by matching via VLOOKUP and HLOOKUP.
I've got way too difficult task for my current skills, dear members I appreciate any help or guidance.
Needs to find data in table based on 5 conditions to show it in "Required power" cell.
I have added conditions manually via Data Validation.

My data sheet structure conditions are:
Condition 1 is material thickness (100mm,75mm)
Condition 2 Outside temperature (43, 35,...)
Condition 3 Desirable temperature inside (15, 10, 5, 0)
Condition 4,5 warehouse size (I think here I can use VLOOKUP + HLOOKUP.)

Only the final search step is clear for me, but I don't know how to get to that point.
Can't even think how to start.
IF material thickness 100mm search on the left table, IF 75mm search in range on the right table,
Then I need to come to Outside temperature condition and so on, until finally I can search by matching via VLOOKUP and HLOOKUP.
Cooling power.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
3 | ||||||||
4 | ||||||||
5 | Warehouse size | 15 | ㎡ | |||||
6 | Warehouse height | 2.2 | m | |||||
7 | Panel thickness | 75 | mm | |||||
8 | Outside air temp. | ℃ | ||||||
9 | Desirable Internal temp. | ℃ | ||||||
10 | ||||||||
11 | TODO | How to? -> | Required power | kw | ||||
12 | ||||||||
13 | ||||||||
14 | ||||||||
Calculation form |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
D5 | List | =Data!$F$6:$K$6 |
D6 | List | =Data!$D$7:$D$9 |
D7 | List | 100,75 |
D8 | List | 43,35 |
D9 | List | 15,10,5,0 |
Cooling power.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | Cond.1 | ||||||||||||||||||||||||||
2 | |||||||||||||||||||||||||||
3 | 100㎜ Urethane | 75㎜ Urethane | |||||||||||||||||||||||||
4 | Cond. 3 | Cond. 4 warehouse size | |||||||||||||||||||||||||
5 | Outside temp. | Desir. Internal Temp. | Warehouse height | Warehouse size ㎡ | Outside temp. | Desir. Internal Temp. | Warehouse height | Warehouse size ㎡ | |||||||||||||||||||
6 | ㎡ | 5.0 | 10.0 | 15.0 | 20.0 | 25.0 | 30.0 | 35.0 | ㎡ | 5.0 | 10.0 | 15.0 | 20.0 | 25.0 | 30.0 | 35.0 | |||||||||||
7 | 43℃ | 15 | 2.2 | 1.45 | 2.34 | 3.12 | 3.89 | 4.7 | 5.54 | 6.15 | 43℃ | 15 | 2.2 | 1.54 | 2.48 | 3.29 | 4.08 | 4.92 | 5.8 | 6.43 | |||||||
8 | 2.5 | 1.51 | 2.44 | 3.36 | 4.26 | 5.27 | 5.99 | 6.91 | 2.5 | 1.6 | 2.57 | 3.53 | 4.63 | 5.5 | 6.26 | 7.21 | |||||||||||
9 | 3 | 1.76 | 2.87 | 3.86 | 5.07 | 5.98 | 6.97 | 8.05 | 3 | 1.86 | 3.01 | 4.05 | 5.29 | 6.23 | 7.26 | 8.37 | |||||||||||
10 | 10 | 2.2 | 1.78 | 2.97 | 4.05 | 5.27 | 6.2 | 7.34 | 8.23 | 10 | 2.2 | 1.89 | 3.14 | 4.26 | 5.52 | 6.49 | 7.68 | 8.61 | |||||||||
11 | 2.5 | 1.87 | 3.13 | 4.55 | 5.8 | 6.98 | 8.02 | 9.28 | 2.5 | 1.99 | 3.31 | 4.78 | 6.07 | 7.29 | 8.38 | 9.68 | |||||||||||
12 | Cond. 2 | 3 | 2.19 | 3.7 | 5.26 | 6.71 | 8.02 | 9.41 | 10.9 | 3 | 2.32 | 3.9 | 5.51 | 7.01 | 8.36 | 9.8 | 11.83 | ||||||||||
13 | 5 | 2.2 | 2.1 | 3.59 | 5.12 | 6.47 | 7.68 | 9.13 | 10.3 | 5 | 2.2 | 2.23 | 3.8 | 5.39 | 6.79 | 8.05 | 9.55 | 10.77 | |||||||||
14 | 2.5 | 2.22 | 3.82 | 5.58 | 7.16 | 8.67 | 10.04 | 11.8 | 2.5 | 2.36 | 4.03 | 5.86 | 7.5 | 9.06 | 10.48 | 12.3 | |||||||||||
15 | 3 | 2.61 | 4.69 | 6.49 | 8.34 | 10.04 | 12 | 13.89 | 3 | 2.78 | 4.94 | 6.8 | 8.71 | 10.46 | 12.48 | 14.44 | |||||||||||
16 | 0 | 2.2 | 2.41 | 4.2 | 6.02 | 7.66 | 9.16 | 10.9 | 12.51 | 0 | 2.2 | 2.57 | 4.61 | 6.34 | 8.05 | 9.6 | 11.41 | 13.08 | |||||||||
17 | 2.5 | 2.57 | 4.66 | 6.59 | 8.51 | 10.36 | 12.21 | 14.15 | 2.5 | 2.74 | 4.92 | 6.93 | 8.92 | 10.82 | 12.74 | 14.75 | |||||||||||
18 | 3 | 3.03 | 5.51 | 7.7 | 9.96 | 12.22 | 14.41 | 16.71 | 3 | 3.23 | 5.8 | 8.07 | 10.4 | 12.72 | 14.99 | 17.36 | |||||||||||
19 | Outside temp. | Desir. Internal Temp. | Warehouse height | Warehouse size ㎡ | Outside temp. | Desir. Internal Temp. | Warehouse height | Warehouse size ㎡ | |||||||||||||||||||
20 | ㎡ | 5.0 | 10.0 | 15.0 | 20.0 | 25.0 | 30.0 | 35.0 | ㎡ | 5.0 | 10.0 | 15.0 | 20.0 | 25.0 | 30.0 | 35.0 | |||||||||||
21 | 35℃ | 15 | 2.2 | 1.17 | 1.92 | 2.6 | 3.28 | 3.89 | 4.77 | 5.36 | 35℃ | 15 | 2.2 | 1.24 | 2.02 | 2.74 | 3.44 | 4.07 | 4.98 | 5.6 | |||||||
22 | 2.5 | 1.24 | 2.04 | 2.83 | 3.62 | 4.54 | 5.23 | 6.03 | 2.5 | 1.31 | 2.14 | 2.97 | 3.79 | 4.74 | 5.45 | 6.28 | |||||||||||
23 | 3 | 1.43 | 2.39 | 3.29 | 4.21 | 5.23 | 6.13 | 7.07 | 3 | 1.51 | 2.51 | 3.44 | 4.56 | 5.44 | 6.37 | 7.35 | |||||||||||
24 | 10 | 2.2 | 1.5 | 2.54 | 3.52 | 4.66 | 5.55 | 6.57 | 7.44 | 10 | 2.2 | 1.59 | 2.69 | 3.71 | 4.88 | 5.81 | 6.87 | 7.77 | |||||||||
25 | 2.5 | 1.6 | 2.73 | 3.87 | 5.16 | 6.25 | 7.26 | 8.4 | 2.5 | 1.69 | 2.88 | 4.06 | 5.4 | 6.53 | 7.57 | 8.76 | |||||||||||
26 | 3 | 1.86 | 3.23 | 4.69 | 6.02 | 7.27 | 8.57 | 9.92 | 3 | 1.98 | 3.4 | 4.9 | 6.28 | 7.56 | 8.91 | 10.31 | |||||||||||
27 | 5 | 2.2 | 1.81 | 3.16 | 4.59 | 5.86 | 7.03 | 8.36 | 9.51 | 5 | 2.2 | 1.93 | 3.34 | 4.83 | 6.14 | 7.36 | 8.74 | 9.93 | |||||||||
28 | 2.5 | 1.95 | 3.42 | 5.05 | 6.52 | 7.95 | 9.28 | 10.76 | 2.5 | 2.07 | 3.61 | 5.3 | 6.82 | 8.3 | 9.68 | 11.21 | |||||||||||
29 | 3 | 2.29 | 4.06 | 5.91 | 7.65 | 9.29 | 10.99 | 12.91 | 3 | 2.43 | 4.27 | 6.19 | 7.98 | 9.67 | 11.42 | 13.41 | |||||||||||
30 | 0 | 2.2 | 2.13 | 3.77 | 5.5 | 7.05 | 8.51 | 10.13 | 11.72 | 0 | 2.2 | 2.27 | 3.99 | 5.79 | 7.4 | 8.91 | 10.6 | 12.25 | |||||||||
31 | 2.5 | 2.3 | 4.1 | 6.07 | 7.88 | 9.63 | 11.29 | 13.27 | 2.5 | 2.44 | 4.33 | 6.37 | 8.24 | 10.06 | 11.94 | 13.82 | |||||||||||
32 | 3 | 2.71 | 5.04 | 7.13 | 9.27 | 11.3 | 13.56 | 15.73 | 3 | 2.88 | 5.3 | 7.47 | 9.67 | 11.93 | 14.1 | 16.34 | |||||||||||
33 | |||||||||||||||||||||||||||
Data |