Need to create a box calculator that will work acorss multiple rows

[missyeager]

Hello -

I have two lists that I am working with - one that has the dimensions of a product and one that has the dimensions of the available shipping boxes. I need to compare the length, width and height of the product to the dimensions of the box and determine which box is the best fit.

I have set up a formula that will determine if the product will fit in the box. It will return a 1 if the L, W or H of the box is larger than the product. Any box size that adds up to 3 will be marked as "Yes". For all "Yes" boxes, it will return the remaining cubic inches in the box.

=IF((IF((SUM((IF((C6>A$3),1,0))+(IF((D6>B$3),1,0))+(IF((E6>C$3),1,0)))=3),"Yes","No")="Yes"),((PRODUCT(C6:E6))-(PRODUCT(A$3:C$3)))," ")

I then have a separate formula that determines what the smallest box that fits is and returns the L, W and H.

(Length) =VLOOKUP((VLOOKUP((MIN($A$6:$A$35)),A6:B35,2,FALSE)),$B:$E,2,FALSE)

The problem with my current solution is that I have a list of products and need to fill in the appropriate boxes. The way that this workbook is currently set up will only allow me to return the correct box dimensions for one row at a time.

How can I create a formula, calculator, etc. that I can drag down through all of the rows and have it return the dimensions of the best fit box?

I have posted the workbook that I am working with on my public MobileMe folder here:
https://files.me.com/katherineyeager/o6dplw
In case that doesn't work, it's in the folder "Mr. Excel Examples" here: https://public.me.com/katherineyeager

The first worksheet is the "calculator" with the box dimensions. The second sheet contains the product list that I am working with.

Thanks for any help! I appreciate it.

Katie

 

[missyeager]

I forgot to add the specifics. I am working with Excel 2010 and Windows 7. Thanks!