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]

depends how many decimals. 0.4999999999 have a beter result.

Hi,
if the part vol is 4 it should basically not fit in any of the box. however it still chose B3.

 

[Aladin Akyurek]

Why don't you create an example output set for a set of F values for the data you posted in post #1 ?

 

[Luke_1814]

Why don't you create an example output set for a set of F values for the data you posted in post #1 ?

A	B	C	D	E	F
Box name	Box Vol			Part Vol -->	2
b1	1
b2	1.9
b3	3

<tbody>
</tbody>

In above scenario, the part should ideally chose box "B3" i.e cell A4
these are the results based on earlier examples
formula 1 : =IF($F$1<=B2,A2,IF($F$1<=B3,A3,IF($F$1<=B4,A4)))
Result - cell A4
formula 2 : =LOOKUP(F1,B2:B4,A2:A4)
Result - cell A3
Formula 3 : =IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
Result - cell A3.

hope this explanation helps.

 

[mart37]

IF(F1<=MAX(B2:B4);INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")

 

[mart37]

=IF(F1<=MAX(B2:B4),INDEX(A2:A4,SUMPRODUCT(LARGE((B2:B4>=F1)*ROW(1:3),COUNTIF(B2:B4,">="&F1)))),"Don't fit!")

 

[Aladin Akyurek]

A	B	C	D	E	F
Box name	Box Vol			Part Vol -->	2
b1	1
b2	1.9
b3	3

<tbody>
</tbody>

In above scenario, the part should ideally chose box "B3" i.e cell A4
these are the results based on earlier examples
formula 1 : =IF($F$1<=B2,A2,IF($F$1<=B3,A3,IF($F$1<=B4,A4)))
Result - cell A4
formula 2 : =LOOKUP(F1,B2:B4,A2:A4)
Result - cell A3
Formula 3 : =IFERROR(INDEX(A2:A4,(MATCH(ROUND(F1+0.4,0),B2:B4,1))),"Don't fit!")
Result - cell A3.

hope this explanation helps.

I did not ask to evaluate the suggestions. I was rather asking for set of target values with corresponding boxes.

Still assuming that box vol is in ascending order...

=INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,1)+(LOOKUP(F1,$B$2:$B$4)<=F1))

Does this give you the indended box results?

 

[Luke_1814]

I did not ask to evaluate the suggestions. I was rather asking for set of target values with corresponding boxes.

Still assuming that box vol is in ascending order...

=INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,1)+(LOOKUP(F1,$B$2:$B$4)<=F1))

Does this give you the indended box results?

Hi,
Apologies for the delayed response...
The above formula works well for F1 value which is more than 1. However when i try for values which are less than 1, it displays as #N/A.

 

[Aladin Akyurek]

Hi,
Apologies for the delayed response...
The above formula works well for F1 value which is more than 1. However when i try for values which are less than 1, it displays as #N/A.

What response would you like to see when that's the case if not the following?

=IFERROR(INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,1)+(LOOKUP(F1,$B$2:$B$4)<=F1)),"not available")

 

[Luke_1814]

What response would you like to see when that's the case if not the following?

=IFERROR(INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,1)+(LOOKUP(F1,$B$2:$B$4)<=F1)),"not available")

Hi,
Based on the Box volume available it should ideally go into box name b1 or b2 or b3 since part volume is less than a box volume.

 

[Aladin Akyurek]

Hi,
Based on the Box volume available it should ideally go into box name b1 or b2 or b3 since part volume is less than a box volume.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Box name	Box Vol			Part Vol -->	0.2	1​	b2
2​	b1	1				2.7	2.7​	b3
3​	b2	1.9				6	6​	no fit
4​	b3	3

In G1 enter and copy down:

=IF(F1 < MIN($B$2:$B$4),MAX(F1,MIN($B$2:$B$4)),F1)

In H1 enter and copy down:

=IFERROR(INDEX($A$2:$A$4,MATCH(G1,$B$2:$B$4,1)+(LOOKUP(G1,$B$2:$B$4)<=G1)),"no fit")