Forum Discussion
Finding the next largest material in both length and width
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.
8 Replies
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.
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.
Thank you! SergeiBaklan
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.
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))
