Finding the next largest material in both length and width

[j-bird]

I am looking for a way to find the next size material based off any given width and length. I tried using index match to search for multiple criteria at the same time but could never get it to work how I intended. Any help would be greatly appreciated. Bonus points for also finding the next size material based off the face material. 

[#2]

@j-bird 

It depends on logic for "next size". If min of Width-C2+Length-C3 that could be

=INDEX(Material,MATCH(1,INDEX( --(C2-Width+C3-Length=AGGREGATE(15,6,1/(Width<C2)/(Length<C3)*(C2-Width+C3-Length),1)),0),0))

didn't catch the second question.

[#3]

Thank you! @Sergei Baklan 

This was great! Instead of finding the "next size" that is less than the given value though I need to return the value that is the "next size" greater than or equal to the given width and length.

[#4]

@j-bird 

That will be practically the same, only change sign of criteria in AGGREGATE and in calculating the difference in sizes:

=INDEX(Material,MATCH(1,INDEX( --((Width-C2+Length-C3)=AGGREGATE(15,6,1/(Width>=C2)/(Length>=C3)*(Width-C2+Length-C3),1)),0),0))

[#5]

@Sergei Baklan 

Thank you so much! This is extremely helpful!

[#6]

@j-bird , you are welcome

[#7]

@j-bird 

This is only available from the most up to date versions of Office 365.  The new lookup function

= XLOOKUP( W*L, IF((width>=W)*(length>=L), width*length ), dimensions, , 1 )

is capable of identifying the next largest value (I have used an area) and returning both dimensions in a single lookup.

[#8]

@Sergei Baklan 

I came up with the formula above (after you posted your first one) but hesitated to post it. The formula seeks the most efficient (i.e. minimum waste) for the desired dimensions, allowing for more than one piece to be produced out of the material. It further takes Length and Width as separate (non inter-changeable) parameters. In other words, a piece 10L X 5W will not be made out of a material 5L X 10W , e.g. due to the pattern or structure of the material. (formula edited 5 Jan, 2020):

=INDEX(Material,IFERROR(MATCH(1,1/(MOD(Width,$C$2)=0)*1/(MOD(Length,$C$3)=0),0),MATCH(AGGREGATE(14,6,(1/(1/(Width>=$C$2)*(Length>=$C$3)*(MOD(Width,$C$2)*Length+MOD(Length,$C$3)*Width))),1),1/(MOD(Width,$C$2)*Length+MOD(Length,$C$3)*Width),0)),1)

I find material code "2431316" which gives a waste of (304.8-300) X 908.1 + (908.1-875) X 304.8 = 14447.76

Your formula identifies code "2220873" when I enter it into the schedule. Correct? That one gives me a total waste of (333.5-300) X 875 + (875-875) X 333.5 = 29312.5

Up to @j-bird to decide what in fact is the most desirable "next size".

[#9]

@Riny_van_Eekelen 

I guess you are right, I took difference of perimeters, in your case that's difference of squares (same as @Peter Bartholomew took), that will be more correct. Anyway, any calculation could be used within formula in (...)=AGGREGATE part

=INDEX(Material,MATCH(1,INDEX( --((Width*Length-C2*C3)=AGGREGATE(15,6,1/(Width>=C2)/(Length>=C3)*(Width*Length-C2*C3),1)),0),0))