Multiple user input value reference

[Hapakaya]

I have two user input data points for MAX and MIN, I need to use these values and reference the table to select a box size that is most appropriate. The Max value needs to selecta box size the has a value in the table closest to the optimal design of 0.1", but I also need to make sure the box min is less than or equal to the user input min. There are multiple box sizes that could work for different inputs, but I want it to prioritize the optimal design range of 0.1"


I cannot use the mini sheets being on a work computer so heres are some pictures: "Front Page". For reference MAX COOLING is cell C18, and UNOCC MIN is cell E18.

The reference tables are on another sheet "Look Up Tables"

In this example the best box size should be 12 because it prioritized the optimal design range, but 10 could technical work.

I just need to reference the max "MAX COOLING" and min "UNOCC MIN" and reference the AVT6000 in the 'Look Up Tables' and return the value in column B that the function determines to be the best box size.

Thanks

 

[myall_blues]

Can you explain step by step the process that leads you to arrive at the answer 12 (or 10)? What's the lookup sequence to get that value?

 

[myall_blues]

I may have worked it out anyway. Is this what you're after? You'll have to pretend the two parts are on separate sheets for the purpose of this demonstration.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Front page							Lookup tables			Optimal design range
2		Mark	Max cooling		Unocc Min	Box size			Box size		Box min						Box max
3		SAV-1	1270		200	12			6	06	30	143	174	206	230	254	315
4									8	08	80	367	447	528	589	650	800
5									10	10	120	606	733	860	958	1056	1300
6									12	12	180	840	1016	1192	1326	1461	1790
7									14	14	250	1364	1624	1884	2079	2275	2750
8									12x18	18	260	1437	1761	2086	2341	2596	3200
9									12x24	24	350	1812	2213	2614	2925	3237	4000
10									12x36	36	520	2875	3523	4172	4681	5191	6400
11									12x48	48	700	3625	4426	5228	5850	6473	8000
Sheet1

Cell Formulas
Range	Formula
F3	F3	=LET(boxsize,$J$3:$J$11, boxmin,VALUE(XLOOKUP(E$3,$K$3:$K$11,boxsize,,-1)), boxopt,MAX(VALUE(BYCOL($L$3:$Q$11,LAMBDA(optrange,XLOOKUP(C$3,optrange,boxsize,,-1))))), MIN(boxmin,boxopt))

 

[Hapakaya]

Can you explain step by step the process that leads you to arrive at the answer 12 (or 10)? What's the lookup sequence to get that value?

Thanks for the response, first I would take the unocc min user input and then check to see which box size would satisfy this input and I would start from box size 6 and go up until I reach the first box that works. so in the example any box smaller than 14 would technically work, which is when I would then check if my max cooling is lower than the box max. So the first box to satisfy the min and max would be size 10, but if i check the next box size up I will find that the max cooling falls closer to the optimal design of 0.1"

Essentially just does the max and min fall within the range of the box, and which max cooling is closer to the optimal design of 0.1"

 

[Hapakaya]

I may have worked it out anyway. Is this what you're after? You'll have to pretend the two parts are on separate sheets for the purpose of this demonstration.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Front page							Lookup tables			Optimal design range
2		Mark	Max cooling		Unocc Min	Box size			Box size		Box min						Box max
3		SAV-1	1270		200	12			6	06	30	143	174	206	230	254	315
4									8	08	80	367	447	528	589	650	800
5									10	10	120	606	733	860	958	1056	1300
6									12	12	180	840	1016	1192	1326	1461	1790
7									14	14	250	1364	1624	1884	2079	2275	2750
8									12x18	18	260	1437	1761	2086	2341	2596	3200
9									12x24	24	350	1812	2213	2614	2925	3237	4000
10									12x36	36	520	2875	3523	4172	4681	5191	6400
11									12x48	48	700	3625	4426	5228	5850	6473	8000
Sheet1

Cell Formulas
Range	Formula
F3	F3	=LET(boxsize,$J$3:$J$11, boxmin,VALUE(XLOOKUP(E$3,$K$3:$K$11,boxsize,,-1)), boxopt,MAX(VALUE(BYCOL($L$3:$Q$11,LAMBDA(optrange,XLOOKUP(C$3,optrange,boxsize,,-1))))), MIN(boxmin,boxopt))

This works, except when I try and input lower values.
For example max/min of 200/200 returns error, and max/min of 345/345 returns box size 6 but those values are out of range of the defined box limits.

 

[myall_blues]

Thanks for the response, first I would take the unocc min user input and then check to see which box size would satisfy this input and I would start from box size 6 and go up until I reach the first box that works. so in the example any box smaller than 14 would technically work, which is when I would then check if my max cooling is lower than the box max. So the first box to satisfy the min and max would be size 10, but if i check the next box size up I will find that the max cooling falls closer to the optimal design of 0.1"

Essentially just does the max and min fall within the range of the box, and which max cooling is closer to the optimal design of 0.1"

Do I understand from this that you're only interested in the matrix between the box min and box max (columns L-P in my example) you are only interested in matching to column L and the other columns can be ignored?

 

[Hapakaya]

Do I understand from this that you're only interested in the matrix between the box min and box max (columns L-P in my example) you are only interested in matching to column L and the other columns can be ignored?

Correct, I want to chose a box that has a max value as close to whats in column L, so long as the max and min fall within the range of the box