Forum Discussion
Using IF formulas with Multiple Criteria
Hi
I am trying to figure out which shipping box sizes I could use to ship products. I need each products dimension to fit into a specific box.
Here is a sample of the box sizes
| Box # | L | W | H |
| 9 | 6 | 6 | 12 |
| 10 | 20 | 8 | 8 |
| 11 | 9 | 9 | 4 |
| 12 | 16 | 9 | 9 |
| 13 | 20 | 10 | 10 |
Here is a sample of product dimensions:
| L | W | H |
| 2.75 | 1.25 | 1.25 |
| 6.75 | 1.375 | 2.625 |
| 10.25 | 2 | 6 |
| 11.5 | 2.25 | 7.75 |
| 11.5 | 2 | 8 |
| 18 | 11.5 | 2.75 |
Here is the formula I used
=IFS((G52<E4)*AND(H52<F4)*AND(I52<G4),$B$4)
B$4$ represents the box #.
That formula works to test for 1 box size.
Am I able to continue the formula so that if the first box size doesn't fit, it tries the next box size until the end?
1 Reply
This might work for you:
=LET(box,$C$2:$C$6,L,$D$2:$D$6,W,$E$2:$E$6,H,$F$2:$F$6,filtered,FILTER(box,(I2<L)*(J2<W)*(K2<H)),IFERROR(MIN(filtered),"Bigger box needed"))
