Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Using IF formulas with Multiple Criteria

Copper Contributor

# 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?

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