Alternative for Nested IF formula

Luke_1814

New Member
Joined
Aug 17, 2017
Messages
21
Hi Guys,
Chose the right box to store Part A which has volume of 2.7

Box name(Column A)Box Volume(Column B)
b11
b22
b33

<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.
 
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

<tbody>
</tbody>


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")

Hi,
Thank you for the above formula provided.

The above formula does not hold good if the Part Volume is equal to the Box volume. as shown in the example above, for part volume of 0.2, it chooses the box b2. is it possible to add an formula where in if the Part volume is equal to Box volume it should chose the one which is equal rather than the next above Box.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi, here is a couple of other options.

1. If you can change the sort on your "Box Table" to descending.


Excel 2013/2016
ABCDEFG
1Box nameBox VolumeVol:1b1
2b331.5b2
3b222b2
4b112.5b3
53b3
63.5Doesn't Fit
Sheet3
Cell Formulas
RangeFormula
G1=IFERROR(INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,-1)),"Doesn't Fit")


2. If you cannot change the sort order.


Excel 2013/2016
ABCDEFG
1Box nameBox VolumeVol:1b1
2b111.5b2
3b222b2
4b332.5b3
53b3
63.5Doesn't Fit
Sheet4
Cell Formulas
RangeFormula
G1=IFERROR(INDEX($A$2:$A$4,MATCH(TRUE,INDEX($B$2:$B$4>=F1,0),0)),"Doesn't Fit")
 
Upvote 0
Hi, here is a couple of other options.

1. If you can change the sort on your "Box Table" to descending.

Excel 2013/2016
ABCDEFG
1Box nameBox VolumeVol:1b1
2b331.5b2
3b222b2
4b112.5b3
53b3
63.5Doesn't Fit

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

Worksheet Formulas
CellFormula
G1=IFERROR(INDEX($A$2:$A$4,MATCH(F1,$B$2:$B$4,-1)),"Doesn't Fit")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



2. If you cannot change the sort order.

Excel 2013/2016
ABCDEFG
1Box nameBox VolumeVol:1b1
2b111.5b2
3b222b2
4b332.5b3
53b3
63.5Doesn't Fit

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

Worksheet Formulas
CellFormula
G1=IFERROR(INDEX($A$2:$A$4,MATCH(TRUE,INDEX($B$2:$B$4>=F1,0),0)),"Doesn't Fit")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Worked in every way i required. Thank you so much, appreciate it!

Thank you all for helping me with this formula.
 
Upvote 0
Referring to #15 .
My apologies, had not noticed this reply earlier.

Apparently this worked just the way i wanted too!

Thank you so much.
 
Last edited:
Upvote 0
Hi,
Could you please help me with below mentioned example :
Below formula was used to check just for Box volume, however in this example i need check one more additional column which is Box weight along with Box volume. Please help.
Worksheet Formulas
CellFormula
G1=IFERROR(INDEX($A$2:$A$4,MATCH(TRUE,INDEX($B$2:$B$4>=F1,0),0)),"Doesn't Fit")

<tbody>
</tbody>

Excel 2013/2016
ABCDEFG
1Box nameBox VolumeBox WeightVol:1b1
2b112
3b224
4b336
5
6

<tbody>
</tbody>
 
Upvote 0
i need check one more additional column which is Box weight along with Box volume.

Hi, you didn't describe what the check should be or provide any examples so this suggestion is a bit of a guess.


Excel 2013/2016
ABCDEFG
1Box nameBox VolumeBox WeightVol:WeightBox
2b11211b1
3b22412b1
4b33612.001b2
514b2
614.001b3
716.001Doesn't Fit
Sheet1
Cell Formulas
RangeFormula
G2=IFERROR(INDEX($A$2:$A$4,MATCH(1,INDEX(($B$2:$B$4>=E2)*($C$2:$C$4>=F2),0),0)),"Doesn't Fit")
 
Upvote 0
My apologies, i didnt explain the question better.
The solution which you provided is exactly what i wanted. Thank you. :)

Also could you please elaborate the formula for me to understand. I am mainly confused about the multiplication symbol used in index.
 
Upvote 0
I am mainly confused about the multiplication symbol used in index.

Hi, in Excel when mathematical operations are performed against Boolean values TRUE's are evaluated as 1's and FALSE's as 0's - so when the two results of the logical tests are multiplied we get 1's when both tests are true and 0's if either are false.

The INDEX function is just acting as a wrapper to pass this array of 1's and 0's to the MATCH function which is returning the position of the first 1.
 
Upvote 0
Hi, need to add one more criteria along with Volume and weight.
For Ex: Sheet1 contains below data
Part No.WidthDepthHeightWeightStor Volume Stor WeightStorage
A1231650180901.0126.731.01FORMULA

<tbody>
</tbody>

FORMULA :
=IFERROR(INDEX(Sheet2!$B$2:$B$197,MATCH(1,INDEX((Sheet2!$F$2:$F$197>=G3)*(Sheet2!$G$2:$G$197>=F3),0),0)),"Doesn't Fit")

Sheet2 contains below data,
StorageWidthDepthHeightVolumeWeight
K2033201200510195.84250
K2046501200510397.8500
K2066501200800624500

<tbody>
</tbody>

Based on Stor-Volume and Stor-Weight, its choosing K203 as correct storage. However physically the part doesn't fit in any orientation due to its dimension. could you help me with formula where all five condition(Width,Depth,Height,Stor-weight and Stor-Volume) can be used to chose a particular storage type.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top