google sheets - How do I compare pairs of related values to another list of related values to find best match - Stack Overflow

admin管理员组

文章数量:1293383

So I'm trying to solve cartonization of orders (finding correct package sizes for multi-item orders) within google sheets, through zapier, to re-import the correct package info back into shipstation.

In google sheet A, I have a list of order numbers, with their corresponding lengths, widths, and heights of each one. In google sheet B, I have a list of our standard box sizes (each one with a name like "small box") and their dimensions (L/H/W). I want to figure out which of the packages is the best fit for each order number, and then put that package's name in a new cel on sheet A.

Here's a simplified example of that data as described:

SIZE	LENGTH	WIDTH	HEIGHT		ORDER #	LENGTH	WIDTH	COMBINED HEIGHT	PACKAGE
flat mailer	10	8	1		1234	11	7.5	2	medium box
small box	6	5	4
medium box	12	9	6
large box	14	10	4

So I'm trying to solve cartonization of orders (finding correct package sizes for multi-item orders) within google sheets, through zapier, to re-import the correct package info back into shipstation.

In google sheet A, I have a list of order numbers, with their corresponding lengths, widths, and heights of each one. In google sheet B, I have a list of our standard box sizes (each one with a name like "small box") and their dimensions (L/H/W). I want to figure out which of the packages is the best fit for each order number, and then put that package's name in a new cel on sheet A.

Here's a simplified example of that data as described:

SIZE	LENGTH	WIDTH	HEIGHT		ORDER #	LENGTH	WIDTH	COMBINED HEIGHT	PACKAGE
flat mailer	10	8	1		1234	11	7.5	2	medium box
small box	6	5	4
medium box	12	9	6
large box	14	10	4

I know how to tell which value-in-a-range a given value is closest to/less than, I could use a query function for that I think. But I don't know how to refer to a pair of cels. How to say "if this pair of cels says 4 and 1.5, the closest pair of cels is 6 and 2"?

I'm sorry if this is a basic question. Would love some help!

Deleting Edit 1 because it's not relevant to final answer, there was just a typo in the formula and deleting Edit 2 because it was my own error. Explaining below.

EDIT 3:

For the record, Tom's formula below seems to work great:

=let(box,G3:I3,L,large(box,1),W,large(box,2),H,large(box,3),f,filter(A$3:D$6,B$3:B$6>=L,C$3:C$6>=W,D$3:D$6>=H),index(sort(f,choosecols(f,2)-L+choosecols(f,3)-W+choosecols(f,4)-H,1),1,1))

You just have to make sure to sort the order's final dimensions by max, median, and minimum (disregarding which one is L/W/H). Will be testing now before calling this answered and closed, but wanted to update for anyone keeping an eye on this issue.

• google-sheets

Share Follow edited Feb 20 at 19:21 Jesse asked Feb 12 at 21:58 JesseJesse 1333 bronze badges 4

• 1 Welcome to Stack Overflow! Please avoid sharing images of your spreadsheet data as the only source of data to avoid closure of the question. Instead, provide a minimal reproducible example in the form of markdown tables containing your sample data should be shared so that the community may replicate your issue easily. Make sure to add the input and your expected output. – Jats PPG Commented Feb 12 at 22:03
• 1 Am I missing something here? Multiply height x width x length = cubic capacity; do this for each containers; then calculate h x w x l for Order and lookup this value in the container list. vlookup will return the nearest smallest value but Vlookup next largest value shows how to return next largest value. – Tedinoz Commented Feb 13 at 0:12
• Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. – TheMaster Commented Feb 13 at 3:42
• Please provide enough code so others can better understand or reproduce the problem. – Community Bot Commented Feb 13 at 8:48

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You could try this for size (allows maximum of 99 per dimension with one decimal place):

=ArrayFormula(xlookup(G3*10^6+H3*10^3+I3,B3:B6*10^6+C3:C6*10^3+D3:D6,A3:A6,,1))

Flat mailer	10	8	1			11	7.5	2	Medium box
Small box	6	5	4
Medium box	12	9	6
Large box	14	10	4

Or perhaps a bit nicer

=let(m,{10^6;10^3;1},xlookup(mmult(G3:I3,m),mmult(B3:D6,m),A3:A6,,1))

which could be extended to more rows (e.g. for two rows of orders):

=ArrayFormula(let(m,{10^6;10^3;1},xlookup(mmult(G3:I4,m),mmult(B3:D6,m),A3:A6,,1)))

Flat mailer	10	8	1			11	7.5	2	Medium box
Small box	6	5	4			5	4	3	Small box
Medium box	12	9	6
Large box	14	10	4

---

EDIT

As mentioned in the comments, it's easy to find a set of values for which the above gives the wrong answer. I should have done something like this i.e. filter the boxes to find those which would fit on all three dimensions, then sort on some function (I have just chosen sum of differences, but you might use sum of differences squared or even difference in volumes) and take the box which fits best according to that measure.

=let(f,filter(A$3:D$6,B$3:B$6>=G3,C$3:C$6>=H3,D$3:D$6>=I3),index(sort(f,choosecols(f,2)-G3+choosecols(f,3)-H3+choosecols(f,4)-I3,1),1,1))

Flat mailer	10	8	1			11	7.5	2	Medium box
Small box	6	5	4			8	9	1	Medium box
Medium box	12	9	6			9	9	6	Medium box
Large box	14	10	4			14	10	4	Large box

---

If we can't assume that the orders are presented with the largest dimension first (but it looks as though the standard boxes are), you would need an extra step as:

=let(box,G3:I3,L,large(box,1),W,large(box,2),H,large(box,3),f,filter(A$3:D$6,B$3:B$6>=L,C$3:C$6>=W,D$3:D$6>=H),index(sort(f,choosecols(f,2)-L+choosecols(f,3)-W+choosecols(f,4)-H,1),1,1))

	Length	Width	Height			Length	Width	Height
Flat mailer	10	8	1			11	7.5	2	Medium box
Small box	6	5	4			8	9	1	Flat mailer
Medium box	12	9	6			9	9	6	Medium box
Large box	14	10	4			14	10	4	Large box

---

A final thought - the simplest way to do this would have been to arrange the standard boxes in increasing order of size (or maybe of cost, if known) then do a lookup like this:

=ArrayFormula(let(box,G3:I3,L,large(box,1),W,large(box,2),H,large(box,3),xlookup(1,(L<=B$3:B$13)*(W<=C$3:C$13)*(H<=D$3:D$13),A$3:A$13)))

It's possible to find cases where the two approaches would give a different result, e.g. considering an order measuring 12 X 9 X 2 with a table of standard boxes as below:

package names	L	W	H
small box	6	5	4
small bubble	8	6	1
flat	10	8	0.5
large bubble	10	8	1
shirt bag	12	9	1
med box	12	9	6
gemini	13	10	1
pizza	13	10	2
large box	14	10	4
big awk box	16	16	8
biggest box	16	16	16

First fit would give Med box but best fit would give Pizza.

本文标签：