Using IF formulas with Multiple Criteria

Occasional Visitor

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 #LWH
96612
102088
11994
121699
13201010

 

Here is a sample of product dimensions:

LWH
2.751.251.25
6.751.3752.625
10.2526
11.52.257.75
11.528
1811.52.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

@Aberlin2125 

 

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