Match 3 different values to MAX value of cell in list.

[Weeble]

Hi!

So I have a table with description that look like this.

Description
255-146-130-1600
260-110-130-1600
260-115-130-1600
260-120-130-1600
260-135-130-1600
260-146-130-1600
260-160-150-1800

The digits represents values ' Length , Width, Height and Weight '

In a different sheet I have each value of a product written down in single cells.

Length	Width	Height	Weight
140	11	55	106
120	113	80	100
256	112	120	1680

I this table I would like to add a column that matches the closest max value of the description.

Length	Width	Height	Weight	Decription match
140	11	55	106	255-146-130-1600
120	113	80	100	255-146-130-1600
256	112	120	1680	260-160-150-1800

The first two match with description ' 255-146-130-1600 ' due to all critiera fits in the match and takes the lowest length.
The last one matches with description ' 260-160-150-1800 ' Since length does not fit in '255' and weight is above '1600'.
Writing this is seams very complicated. But maybe somone here has a good idea of what I want to achieve, and might have done it before.
How do i match the closest MAX critieria with my Description list?

 

[KRice]

Thank you for the clarification. I encountered an issue when attempting to write a formula that would sequentially evaluate the priorities. The main problem is that potentially preferred options may be excluded by the first priority screening. For example, suppose the dimensions to be matched (in the Match Table) are:

240

17

80

54

And the table to select items from (the Selections Table) contains:
240-200-80-54
241-17-80-54
A sequential search for priority #1 (finding the closest match for Length) would identify all Selections having a length=240, consequently, the selection whose length=241 would be excluded, even though it is only 1 cm longer and all of its other parameters are exact matches. To overcome this issue, I propose considering both length and width at the same time and basing the "best match" criteria on area. We calculate the area (Length x Width) of the item in the Match Table and then find which options in the Selections Table would be best utilized (i.e., we compute the ratio of the required area of the Match item to the available area of the options in the Selection Table and then choose the options having the largest ratio). At the same time, the formula requires that the individual parameters of the Selection Table options are greater than or equal to the corresponding parameters of the Match item. To address situations where more than one item in the Selections Table satisfies all of these criteria (as is the case for (259,146,55,106) where 260-146-130-1600 and 260-146-150-1800 are both possibilities), the array of potential choices is fed into another test to identify which of them best matches the Height of the item in the Match Table...this serves as the tie-breaker. The Weight is not used for tie-breaking purposes, only for confirmation that the Selection Table item has a weight that is greater than or equal to the Match Table item.

The entire list you provided earlier is included in the working example below, with two exceptions. I inserted two hypothetical options in H80:H81 to test the match/selection of the yellow cells. Your original data for those two cells is shown in N80:N81...I'm not sure if there are issues with row 78 and others that have text. The orange cells reflect your most recent example: You will see that this approach finds a different solution for both of them. I also added in other examples to the Match Table drawn from earlier posts (light blue and dark blue). Please have a look at the matches found for these (green cells in column F) and determine whether this area ratio approach produces reasonable results, and let me know if something different should be done.

The Selections Table will be in a follow-up post...

MrExcel20200915.xlsx
	A	B	C	D	E	F
1	Match Table
2					undersize dim ratios	LxW area ratios
3					sum all, take min	take max
4	Length	Width	Height	Weight	Description match	Description match
5	259	146	55	106	260-146-130-1600	260-146-130-1600
6	120	113	80	100	130-305-85-800	130-115-85-1050
7	256	112	120	1680	260-146-150-1800	260-146-150-1800
8	260	160	150	1850	280-880-240-8000	320-550-260-24900
9	260	110	120	1850	340-146-130-2200	340-146-130-2200
10	259	113	130	1600	260-113-130-1600	260-113-130-1600
11	260	112	130	1600	260-113-130-1600	260-113-130-1600
12	240	17	80	54.264	440-40-126-1500	260-60-130-1600
13	360	17	92	188.7	440-40-126-1500	440-40-126-1500
14	100	4	4	100	260-18-30-100	115-30-45-250
15	440	11	55	106.24	440-40-126-1500	440-40-126-1500
16	900	11.55	63	316	920-70-126-2000	920-70-126-2000
17	251	17	90	842.4	440-40-126-1500	260-60-130-1600
18	251	17	90	842.4	440-40-126-1500	260-60-130-1600
19	251	17	90	842.4	440-40-126-1500	260-60-130-1600
20	251	17	90	842.4	440-40-126-1500	260-60-130-1600
21	251	17	90	842.5	440-40-126-1500	260-60-130-1600
22	251	17	90	842.4	440-40-126-1500	260-60-130-1600
23	251	17	90	842.4	440-40-126-1500	260-60-130-1600
24	251	17	90	842.4	440-40-126-1500	260-60-130-1600
25	251	17	90	842.5	440-40-126-1500	260-60-130-1600
26	251	17	90	842.5	440-40-126-1500	260-60-130-1600
27	251	17	90	842.4	440-40-126-1500	260-60-130-1600
28	240	17	80	54.264	440-40-126-1500	260-60-130-1600
29	360	17	92	188.7	440-40-126-1500	440-40-126-1500
30	244	17.7	56	130	440-40-126-1500	260-60-130-1600
31	440	18	80	229.2	440-40-126-1500	440-40-126-1500
32	440	18	80	229.2	440-40-126-1500	440-40-126-1500
33	1200	18	112.5	1178	1220-70-126-2000	1220-70-126-2000
34	120	30	80	43	130-60-85-800	130-40-85-1050
35	120	30	80	100	130-60-85-800	130-40-85-1050
36	120	31	80	27	130-60-85-800	130-40-85-1050
37	110	32	100	95	130-80-120-1050	130-80-120-1050
38	110	32	100	95	130-80-120-1050	130-80-120-1050
39	120	32	80	8.48	130-60-85-800	130-40-85-1050
Weeble

Cell Formulas
Range	Formula
E5:E39	E5	=IFERROR(INDEX($H$3:$H$105,AGGREGATE(15,6,(ROW($H$3:$H$105)-ROW($H$2))/(AGGREGATE(15,6,($J$3:$J$105/$A5-1)/($J$3:$J$105/$A5>=1)+($K$3:$K$105/$B5-1)/($K$3:$K$105/$B5>=1)+($L$3:$L$105/$C5-1)/($L$3:$L$105/$C5>=1)+($M$3:$M$105/$D5-1)/($M$3:$M$105/$D5>=1),1)=(($J$3:$J$105/$A5-1)/($J$3:$J$105/$A5>=1)+($K$3:$K$105/$B5-1)/($K$3:$K$105/$B5>=1)+($L$3:$L$105/$C5-1)/($L$3:$L$105/$C5>=1)+($M$3:$M$105/$D5-1)/($M$3:$M$105/$D5>=1))),1)),"no match")
F5:F39	F5	=IFERROR(INDEX($H$3:$H$105,AGGREGATE(15,6,(ROW($H$3:$H$105)-ROW($H$2))/(AGGREGATE(14,6,$C5/$L$3:$L$105/(AGGREGATE(14,6,($A5/$J$3:$J$105)*($B5/$K$3:$K$105)/(($A5/$J$3:$J$105<=1)*($B5/$K$3:$K$105<=1)*($C5/$L$3:$L$105<=1)*($D5/$M$3:$M$105<=1)),1)=($A5/$J$3:$J$105)*($B5/$K$3:$K$105)/(($A5/$J$3:$J$105<=1)*($B5/$K$3:$K$105<=1)*($C5/$L$3:$L$105<=1)*($D5/$M$3:$M$105<=1))),1) = $C5/$L$3:$L$105/(AGGREGATE(14,6,($A5/$J$3:$J$105)*($B5/$K$3:$K$105)/(($A5/$J$3:$J$105<=1)*($B5/$K$3:$K$105<=1)*($C5/$L$3:$L$105<=1)*($D5/$M$3:$M$105<=1)),1)=($A5/$J$3:$J$105)*($B5/$K$3:$K$105)/(($A5/$J$3:$J$105<=1)*($B5/$K$3:$K$105<=1)*($C5/$L$3:$L$105<=1)*($D5/$M$3:$M$105<=1)))),1) ),"no match")

 

[KRice]

...and the Selections Table:

MrExcel20200915.xlsx
	H	I	J	K	L	M	N
1	Selections Table		Helper Table
2	Description		L	W	H	Wt
3	255-146-130-1600		255	146	130	1600
4	260-110-130-1600		260	110	130	1600
5	260-115-130-1600		260	115	130	1600
6	260-120-130-1600		260	120	130	1600
7	260-135-130-1600		260	135	130	1600
8	260-146-130-1600		260	146	130	1600
9	260-146-150-1800		260	146	150	1800
10	260-305-150-1800		260	305	150	1800
11	260-60-130-1600		260	60	130	1600
12	260-80-130-1600		260	80	130	1600
13	260-85-130-1600		260	85	130	1600
14	260-95-130-1600		260	95	130	1600
15	#VÄRDEFEL!
16	340-105-130-1600		340	105	130	1600
17	340-146-130-1600		340	146	130	1600
18	340-146-130-2200		340	146	130	2200
19	340-85-130-2200		340	85	130	2200
20	340-87-130-1600		340	87	130	1600
21	1300-550-300-117000		1300	550	300	117000
22	1450-550-500-97000		1450	550	500	97000
23	1500-600-800-99999		1500	600	800	99999
24	250-550-140-10500		250	550	140	10500
25	250-550-225-16500		250	550	225	16500
26	270-335-85-6850		270	335	85	6850
27	300-550-130-11700		300	550	130	11700
28	320-550-260-24900		320	550	260	24900
29	420-450-130-16000		420	450	130	16000
30	420-550-130-16000		420	550	130	16000
31	450-550-300-40500		450	550	300	40500
32	490-550-225-33000		490	550	225	33000
33	520-550-300-46800		520	550	300	46800
34	610-550-130-23500		610	550	130	23500
35	620-550-240-44500		620	550	240	44500
36	650-550-500-50000		650	550	500	50000
37	820-550-240-59000		820	550	240	59000
38	840-550-300-75000		840	550	300	75000
39	1220-70-126-2000		1220	70	126	2000
40	440-100-126-1500		440	100	126	1500
41	440-40-126-1500		440	40	126	1500
42	440-50-126-1500		440	50	126	1500
43	440-60-126-1500		440	60	126	1500
44	440-80-126-1500		440	80	126	1500
45	440-90-126-1500		440	90	126	1500
46	500-360-130-4500		500	360	130	4500
47	620-70-126-1500		620	70	126	1500
48	920-70-126-2000		920	70	126	2000
49	140-25-75-250		140	25	75	250
50	40-16-20-25		40	16	20	25
51	50-35-30-250		50	35	30	250
52	50-35-45-250		50	35	45	250
53	50-50-30-250		50	50	30	250
54	50-50-45-250		50	50	45	250
55	50-60-30-250		50	60	30	250
56	80-15-30-100		80	15	30	100
57	80-15-45-100		80	15	45	100
58	80-35-30-100		80	35	30	100
59	80-35-45-100		80	35	45	100
60	80-50-30-100		80	50	30	100
61	80-50-45-100		80	50	45	100
62	120-40-42-500		120	40	42	500
63	130-110-85-1050		130	110	85	1050
64	130-115-85-1050		130	115	85	1050
65	130-146-85-1050		130	146	85	1050
66	130-165-85-1050		130	165	85	1050
67	130-305-85-800		130	305	85	800
68	130-40-42-500		130	40	42	500
69	130-40-85-1050		130	40	85	1050
70	130-60-42-400		130	60	42	400
71	130-60-85-800		130	60	85	800
72	130-85-85-1050		130	85	85	1050
73	130-95-85-1050		130	95	85	1050
74	140-146-85-1050		140	146	85	1050
75	150-146-85-1050		150	146	85	1050
76	165-65-85-1050		165	65	85	1050
77	201-80-85-1050		201	80	85	1050
78	1050
79	#VÄRDEFEL!
80	260-113-130-1600		260	113	130	1600	1000_Test
81	259-114-130-1600		259	114	130	1600	2000_Test
82	3000_Test
83	XLARGE
84	130-100-130-1050		130	100	130	1050
85	130-146-130-1050		130	146	130	1050
86	130-180-130-1050		130	180	130	1050
87	130-305-130-1050		130	305	130	1050
88	130-80-120-1050		130	80	120	1050
89	130-80-130-1050		130	80	130	1050
90	140-260-130-1050		140	260	130	1050
91	MODULTYP
92	1500-600-800-99999		1500	600	800	99999
93	Owerflow-plats
94	Virtuell plats
95	115-30-45-250		115	30	45	250
96	115-42-45-250		115	42	45	250
97	115-63-52-250		115	63	52	250
98	260-18-30-100		260	18	30	100
99	PLOCK
100	#VÄRDEFEL!
101	405-53-80-2000		405	53	80	2000
102	405-68-80-2000		405	68	80	2000
103	610-450-130-23500		610	450	130	23500
104	240-220-140-2000		240	220	140	2000
105	280-880-240-8000		280	880	240	8000
Weeble

Cell Formulas
Range	Formula
J3:J105	J3	=IFERROR(VALUE(LEFT($H3,FIND("-",$H3)-1)),"")
K3:K105	K3	=IFERROR(VALUE(MID($H3,SEARCH("x",SUBSTITUTE($H3,"-","x",1))+1,SEARCH("y",SUBSTITUTE($H3,"-","y",2))-SEARCH("x",SUBSTITUTE($H3,"-","x",1))-1)),"")
L3:L105	L3	=IFERROR(VALUE(MID($H3,SEARCH("y",SUBSTITUTE($H3,"-","y",2))+1,SEARCH("z",SUBSTITUTE($H3,"-","z",3))-SEARCH("y",SUBSTITUTE($H3,"-","y",2))-1)),"")
M3:M105	M3	=IFERROR(VALUE(RIGHT($H3,LEN($H3)-SEARCH("z",SUBSTITUTE($H3,"-","z",3)))),"")