Alternative for Nested IF formula

[Luke_1814]

Hi Guys,
Chose the right box to store Part A which has volume of 2.7

Box name(Column A)	Box Volume(Column B)
b1	1
b2	2
b3	3

<tbody>
</tbody>

Based on on above table i would use an if conditions which goes like this,
=IF($F$1<=B2,A2,IF($F$1<=B3,A3,IF($F$1<=B4,A4)))

is there any other alternative instead of using IF function. the above example contains 3 rows, however in reality i would need to apply IF func for 100+ rows. Please help.

 

[Luke_1814]

Hi,
This workaround solved entire issue i had. you are my savior. genius work! thanks for all the help...

How about putting each of the storage dimensions in order and each of the product dimensions in order and then applying the formula to those ordered dimensions. For example:

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L
1	Part No.	L	D	H	Wt	Qty	Vol	Wt	Part Dim 1	Part Dim 2	Part Dim 3	SUT
2	A1234	9.5	9.5	1	0.037	500	0.045125	18.5	1	9.5	9.5	A15
3	A1235	35	20	1	0.003	1000	0.7	3	1	20	35	A1
4	A1236	1280	200	130	5.9	200	6656	1180	130	200	1280	No fit
5	A1237	1600	230	1920	2	2	14		230	1600	1920	No fit
6
7
8	SUT	L	D	H	V	Wt	SUT Dim 1	SUT Dim 2	SUT Dim 3
9	A1	95	130	90	1.1115	6	90	95	130
10	A2	95	180	90	1.539	8	90	95	180
11	A3	95	280	90	2.394	13	90	95	280
12	A15	1000	1200	1300	1560	1000	1000	1200	1300
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =SMALL($B2:$D2,1) J2 =SMALL($B2:$D2,2) K2 =SMALL($B2:$D2,3) G9 =SMALL($B9:$D9,1) H9 =SMALL($B9:$D9,2) I9 =SMALL($B9:$D9,3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula L2 {=IFERROR(INDEX($A$9:$A$12,MATCH(1,IF(I2<=$G$9:$G$12,IF(J2<=$H$9:$H$12,IF(K2<=$I$9:$I$12,IF(H2<=$F$9:$F$12,IF(G2<=$E$9:$E$12,1))))),0)),"No fit")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>