Find result in Data Table by multiple conditions

[AlexinJap]

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.

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

 

[Toadstool]

Hi AlexinJap,

Merged cells are anathema to Excel formulae and what looks easier to the human eye (e.g. splitting 100mm and 75mm Urethane into separate tables) adds complexity to a formula solution.

I am reformatting your tables but before I suggest the calculation I must ask the question: Will the specified values entered on the Calculation Form always be exact matches or does it need to be the next highest? (e.g. Outside Temp is 40 so assume 43, Desired Internal Temp is 7 so assume 10, Warehouse Size is 28 sq metres so assume 30).

 

[AlexinJap]

Hello Toadstool. Thank you for your time. Yes, the user must select temperature, warehouse size, etc, there is no input options outside of those in Data Validation selection.
It is pity that I couldn't attach the Excel here.

I could get the answer with the formula :

=INDEX(INDIRECT("U_" &$D$7),MATCH($D$8,Data!$B$7:$B$32,0)+MATCH($D$6,Data!$D$7:$D$32,0)+MATCH($D$9,Data!$C$7:$C$32,0)-2,MATCH($D$5,Data!$F$6:$L$6,0))

The data table had to be modified where ℃ was removed and only number left. And also named ranges were created for different material thickness U_100, U_75, etc. So now having much bigger table with different temperatures

 

[BSALV]

the green cells, you should change to your values, best with data-validation, the orange cell is the result

Map2
	Z	AA	AB	AC	AD	AE	AF	AG	AH
4
5				offset $A$1				not necessary
6				row	column		check	indirect
7		material	75㎜ Urethane	3	14		$N$3	75㎜ Urethane
8		outside	35℃	21	14		$N$21	35℃
9		internal	5	27	15		$O$27	5
10		height	2.5	28	16		$P$28	2.5
11		size	35	20	24		$X$20	35
12		value	11.21	28	24		$X$28	11.21
13
14
Blad1

Cell Formulas
Range	Formula
AD7	AD7	=MATCH(AB7,OFFSET($A$1,AC7-1,,,26),0)
AF7:AF12	AF7	=ADDRESS(AC7,AD7)
AG7:AG12	AG7	=INDIRECT(AF7)
AC8	AC8	=MATCH(AB8,OFFSET($A$1,,AD8-1,100,),0)
AD8,AD12	AD8	=+AD7
AC9	AC9	=+AC8-1+MATCH(AB9,OFFSET($A$1,AC8-1,AD9-1,14,),0)
AD9:AD10	AD9	=+AD8+1
AC10	AC10	=AC9+MATCH(AB10,OFFSET($A$1,AC9-1,AD10-1,5,),0)-1
AC11	AC11	=+AC8-1
AD11	AD11	=AD7-1+MATCH(AB11,OFFSET($A$1,AC8-2,AD7-1,,15),0)
AB12	AB12	=OFFSET($A$1,AC12-1,AD12-1,,)
AC12	AC12	=+AC10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AG7:AG12	Expression	=AB7<>AG7	text	NO

example file

 

[Toadstool]

Reformatting the Data sheet makes the formula much simpler.

AlexinJap.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Urethane Thickness mm	Outside temp. in ℃	Desir. Internal Temp.	Warehouse height	Warehouse size ㎡
2					5	10	15	20	25	30	35
3	100	43	15	2.2	1.45	2.34	3.12	3.89	4.7	5.54	6.15
4	100	43	15	2.5	1.51	2.44	3.36	4.26	5.27	5.99	6.91
5	100	43	15	3	1.76	2.87	3.86	5.07	5.98	6.97	8.05
6	100	43	10	2.2	1.78	2.97	4.05	5.27	6.2	7.34	8.23
7	100	43	10	2.5	1.87	3.13	4.55	5.8	6.98	8.02	9.28
8	100	43	10	3	2.19	3.7	5.26	6.71	8.02	9.41	10.9
9	100	43	5	2.2	2.1	3.59	5.12	6.47	7.68	9.13	10.3
10	100	43	5	2.5	2.22	3.82	5.58	7.16	8.67	10.04	11.8
11	100	43	5	3	2.61	4.69	6.49	8.34	10.04	12	13.89
12	100	43	0	2.2	2.41	4.2	6.02	7.66	9.16	10.9	12.51
13	100	43	0	2.5	2.57	4.66	6.59	8.51	10.36	12.21	14.15
14	100	43	0	3	3.03	5.51	7.7	9.96	12.22	14.41	16.71
15	100	35	15	2.2	1.17	1.92	2.6	3.28	3.89	4.77	5.36
16	100	35	15	2.5	1.24	2.04	2.83	3.62	4.54	5.23	6.03
17	100	35	15	3	1.43	2.39	3.29	4.21	5.23	6.13	7.07
18	100	35	10	2.2	1.5	2.54	3.52	4.66	5.55	6.57	7.44
19	100	35	10	2.5	1.6	2.73	3.87	5.16	6.25	7.26	8.4
20	100	35	10	3	1.86	3.23	4.69	6.02	7.27	8.57	9.92
21	100	35	5	2.2	1.81	3.16	4.59	5.86	7.03	8.36	9.51
22	100	35	5	2.5	1.95	3.42	5.05	6.52	7.95	9.28	10.76
23	100	35	5	3	2.29	4.06	5.91	7.65	9.29	10.99	12.91
24	100	35	0	2.2	2.13	3.77	5.5	7.05	8.51	10.13	11.72
25	100	35	0	2.5	2.3	4.1	6.07	7.88	9.63	11.29	13.27
26	100	35	0	3	2.71	5.04	7.13	9.27	11.3	13.56	15.73
27	75	43	15	2.2	1.54	2.48	3.29	4.08	4.92	5.8	6.43
28	75	43	15	2.5	1.6	2.57	3.53	4.63	5.5	6.26	7.21
29	75	43	15	3	1.86	3.01	4.05	5.29	6.23	7.26	8.37
30	75	43	10	2.2	1.89	3.14	4.26	5.52	6.49	7.68	8.61
31	75	43	10	2.5	1.99	3.31	4.78	6.07	7.29	8.38	9.68
32	75	43	10	3	2.32	3.9	5.51	7.01	8.36	9.8	11.83
33	75	43	5	2.2	2.23	3.8	5.39	6.79	8.05	9.55	10.77
34	75	43	5	2.5	2.36	4.03	5.86	7.5	9.06	10.48	12.3
35	75	43	5	3	2.78	4.94	6.8	8.71	10.46	12.48	14.44
36	75	43	0	2.2	2.57	4.61	6.34	8.05	9.6	11.41	13.08
37	75	43	0	2.5	2.74	4.92	6.93	8.92	10.82	12.74	14.75
38	75	43	0	3	3.23	5.8	8.07	10.4	12.72	14.99	17.36
39	75	35	15	2.2	1.24	2.02	2.74	3.44	4.07	4.98	5.6
40	75	35	15	2.5	1.31	2.14	2.97	3.79	4.74	5.45	6.28
41	75	35	15	3	1.51	2.51	3.44	4.56	5.44	6.37	7.35
42	75	35	10	2.2	1.59	2.69	3.71	4.88	5.81	6.87	7.77
43	75	35	10	2.5	1.69	2.88	4.06	5.4	6.53	7.57	8.76
44	75	35	10	3	1.98	3.4	4.9	6.28	7.56	8.91	10.31
45	75	35	5	2.2	1.93	3.34	4.83	6.14	7.36	8.74	9.93
46	75	35	5	2.5	2.07	3.61	5.3	6.82	8.3	9.68	11.21
47	75	35	5	3	2.43	4.27	6.19	7.98	9.67	11.42	13.41
48	75	35	0	2.2	2.27	3.99	5.79	7.4	8.91	10.6	12.25
49	75	35	0	2.5	2.44	4.33	6.37	8.24	10.06	11.94	13.82
50	75	35	0	3	2.88	5.3	7.47	9.67	11.93	14.1	16.34
51
Data

AlexinJap.xlsx
	A	B	C	D	E
1
2
3
4
5		Warehouse size	15	㎡
6		Warehouse height	2.2	m
7		Panel thickness	75	mm
8		Outside air temp.	35	℃
9		Desirable Internal temp.	10	℃
10
11		Required power	3.71	kw
12
13
14
Calculation Form

Cell Formulas
Range	Formula
C11	C11	=INDEX(Data!$E$3:$K$9999,AGGREGATE(15,6,ROW(Data!$A$3:$A$9999)-ROW(Data!$A$2)/((Data!$B$3:$B$9999=$C$8)*(Data!$C$3:$C$9999=$C$9)*(Data!$A$3:$A$50=$C$7)*(Data!$D$3:$D$9999=$C$6)),1),MATCH($C$5,Data!$E$2:$K$2,0))

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=Data!$E$2:$K$2
C6	List	=Data!$D$3:$D$5
C7	List	75,100
C8	List	35,43
C9	List	0,5,10,15

 

[AlexinJap]

Toadstool, Thank you very much, yes indeed reformatting data sheet would make it simpler for formula. But I was not given that option as those who manage the data sheet still want it to look pleasant for human eye and even print out if needs.
So I went with the solution provided by JohnTopley: Find result in Data Table by multiple conditions [SOLVED]

=INDEX(INDIRECT("U_" &$D$7),MATCH($D$8,Data!$B$7:$B$32,0)+MATCH($D$6,Data!$D$7:$D$32,0)+MATCH($D$9,Data!$C$7:$C$32,0)-2,MATCH($D$5,Data!$F$6:$L$6,0))

 

[Fluff]

Welcome to the MrExcel Message Board!

For future reference.
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[AlexinJap]

Fluff, understood.​