INDEX, MATCH to "dynamic" lookup_array

[ddubbs]

Volume	Box #	Max Length
100	Box 1	30
200	Box 2	15
300	Box 3	20
400	Box 4	55
500	Box 5	20
600	Box 6	35
700	Box 7	50
800	Box 8	25
900	Box 9	40
1000	Box 10	55

Hi all,

Thanks in advance for any help you can offer. I am very much struggling with this issue...

This spreadsheet aims to choose the correct box based on quantity and size of an ordered product. Order volume is calculated elsewhere and must be lower than the box volume (obviously). The "Cut Length" of the product is entered, and that must also be lower than the max length of the box.

With those two inputs "Order Volume" and "Cut Length", I am trying to figure out the best way to find the correct box. Please see above examples. I have been trying to use an INDEX, MATCH that would grab the box with a volume one step greater. However, I need to determine a method to ensure that the length is also checked. I was hoping it would be as easy as modifying the lookup_array to be all cells in column C that are greater than cell "Cut Length".

Any ideas? I'm sure I could explain this better as well.

PS I have been toying around with the CSE formulas, but am struggling with that as well. An important note: When I sort "Volume" in descending order, the "Max Length" column becomes randomized (i.e., there is no straight correlation).

Thank you so much!

[ajm123456789]

can u post a sample spreadsheet please?

[ddubbs]

Sample.xlsx

I stripped the unnecessary information. As it stands now, it pulls the Box# (green) based on one step greater volume. It does not currently take the Cut Length into account. Essentially, I need a secondary criteria added so that it only pulls a Box# that has greater volume AND has an "Inside Length" greater than or equal to the "Cut Length".

Thanks so much!

[ddubbs]

Quick bump. Thanks.

[icestationzbra]

if my understanding is correct, see if this works for you. also, DO NOT use merged cells; they are a big PITA.

[ddubbs]

Awesome. Thanks so much!