Index/Match with multiple headers and tables

[Bjomesphat]

I'm trying to setup a formula for products I produce and ship that will automatically pull the correct shipping price based on a defined set of criteria. I've been able to do this with Index after I heavily edit my shipping tables, but I was hoping someone could assist me with this when my shipping tables are formatted as I receive them. One of the main questions I have is, is it possible to have 2 headers in a table (among multiple tables), and have the formula pull from two sets of criteria based on those headers? For example, below I have my product list with 3 sets of criteria: weight, carrier, zone. Ideally, I would want the formula to look at the main header which is the carrier, then look to the zone as the sub-header, and choose the correct cost based on the next greatest weight value.

So far all I've done is crudely stitched all of my shipping data together and setup some basic index formulas and I used XMATCH to get the next largest weight value in order to pull the cost, but that's requiring me to select my arrays very specifically for each product. I'm trying to automate this a little better, but have no idea where to start. Thanks for any and all help that can be provided.

Book1
	A	B	C	D	E	F
1	SKU	Description	Weight	Carrier	Zone	Cost
2	1802	11x14 Canvas - Felt Back	35.04	Select	8
3	1122	White Ceramic Mug 11oz	13	Parcel	1
4	5069	Mouse Pad	8	Flat	1
5	11500	5x7 JOURNAL - WIRE-O	14	BPM	5
Sheet2

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		Flats			Parcels		BPM									Select
2	OZ	Zone 1		OZ	Zone 1		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8
3	1	$1.15		1	$3.45		16	$2.84	$2.86	$2.86	$2.86	$2.89	$2.90	$2.90		16	$4.90	$5.23	$5.60	$6.16	$6.23	$6.36	$6.36
4	2	$1.15		2	$3.45		24	$2.96	$3.04	$3.08	$3.17	$3.25	$3.26	$3.27		32	$5.20	$5.73	$6.07	$6.60	$7.24	$7.59	$7.82
5	3	$1.24		3	$3.45		32	$3.16	$3.27	$3.35	$3.50	$3.67	$3.68	$3.70		48	$5.45	$6.14	$6.84	$7.32	$8.26	$8.73	$9.18
6	4	$1.37		4	$3.45		40	$3.41	$3.55	$3.63	$3.79	$4.01	$4.01	$4.04		64	$7.16	$7.68	$8.56	$9.16	$10.24	$10.67	$10.67
7	5	$1.53		5	$4.03		48	$3.52	$3.68	$3.79	$3.98	$4.22	$4.38	$4.39
8	6	$1.68		6	$4.03		56	$3.76	$3.93	$4.07	$4.27	$4.35	$4.60	$4.61
9	7	$1.84		7	$4.03		64	$3.87	$4.05	$4.20	$4.33	$4.44	$4.81	$4.82
10	8	$2.00		8	$4.03		72	$3.98	$4.19	$4.35	$4.49	$4.65	$5.06	$5.10
11	9	$2.15		9	$4.59		80	$4.23	$4.46	$4.66	$5.11	$5.39	$5.41	$5.43
12	10	$2.31		10	$4.59		96	$4.45	$4.74	$4.96	$5.36	$5.84	$5.87	$5.88
13	11	$2.46		11	$4.59		112	$4.70	$5.00	$5.27	$5.74	$6.30	$6.32	$6.39
14	12	$2.62		12	$4.59		128	$4.79	$5.02	$5.33	$5.88	$6.51	$6.51	$6.53
15	13	$2.78		13	$5.23		144	$5.07	$5.33	$5.69	$6.31	$7.59	$8.48	$8.93
16	14	$4.73		14	$5.97		160	$6.51	$7.01	$7.50	$8.25	$9.24	$9.73	$11.47
17	15	$4.73		15	$5.97		176	$6.82	$7.37	$7.91	$8.73	$9.82	$10.37	$12.28
18	16	$4.73		16	$5.97		192	$7.13	$7.73	$8.32	$9.21	$10.40	$11.00	$13.08
19							208	$7.46	$8.11	$8.75	$9.72	$11.01	$11.66	$13.91
20							224	$7.75	$8.44	$9.14	$10.18	$11.57	$12.26	$14.69
21							240	$8.07	$8.82	$9.56	$10.68	$12.16	$12.91	$15.51
Sheet1

 

[arunsjain]

Hi

Try power query to automate this process. I solved it but I am not sure how to attach file here. Any link where I can share excel file solution with you?

SKU	Description	Weight	Carrier	Zone	Cost
1802	11x14 Canvas - Felt Back	35.04	Select	8	9.1756​
1122	White Ceramic Mug 11oz	13	Parcel	1	5.2312​
5069	Mouse Pad	8	Flat	1	1.9968​
11500	5x7 JOURNAL - WIRE-O	14	BPM	5	2.8644​

Cheers!!

 

[Bjomesphat]

Hi

Try power query to automate this process. I solved it but I am not sure how to attach file here. Any link where I can share excel file solution with you?

SKU	Description	Weight	Carrier	Zone	Cost
1802	11x14 Canvas - Felt Back	35.04	Select	8	9.1756​
1122	White Ceramic Mug 11oz	13	Parcel	1	5.2312​
5069	Mouse Pad	8	Flat	1	1.9968​
11500	5x7 JOURNAL - WIRE-O	14	BPM	5	2.8644​

Cheers!!

I've never used power query before, but I'd love to be able to look at you file. I don't think Mr Excel allows attachments, but you can use something like WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free.

Just upload the file, click get link, and copy/paste it here.

Really appreciate it!

 

[Peter_SSs]

I solved it but I am not sure how to attach file here. Any link where I can share excel file solution with you?

If you have a solution, please post it in the forum, not just in a file that you provide a link to. Refer to #4 of the Forum Rules, particularly the last paragraph.

 

[Bjomesphat]

If you have a solution, please post it in the forum, not just in a file that you provide a link to. Refer to #4 of the Forum Rules, particularly the last paragraph.

Thanks for the callout. Yes @arunsjain please post what you can in the forum to help others in the future.

 

[Marcelo Branco]

Maybe something like this

Sheet1
Note the headers on row 1 on Sheet1 (gray) - they all need to be in the same column as Zone 1 or Zone 1&2, i.e. precisely on B1, E1, H1, Q1.

07062022 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		Flats			Parcels			BPM									Select
2	OZ	Zone 1		OZ	Zone 1		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8
3	1	1,15		1	3,45		16	2,84	2,86	2,86	2,86	2,89	2,90	2,90		16	4,90	5,23	5,60	6,16	6,23	6,36	6,36
4	2	1,15		2	3,45		24	2,96	3,04	3,08	3,17	3,25	3,26	3,27		32	5,20	5,73	6,07	6,60	7,24	7,59	7,82
5	3	1,24		3	3,45		32	3,16	3,27	3,35	3,50	3,67	3,68	3,70		48	5,45	6,14	6,84	7,32	8,26	8,73	9,18
6	4	1,37		4	3,45		40	3,41	3,55	3,63	3,79	4,01	4,01	4,04		64	7,16	7,68	8,56	9,16	10,24	10,67	10,67
7	5	1,53		5	4,03		48	3,52	3,68	3,79	3,98	4,22	4,38	4,39
8	6	1,68		6	4,03		56	3,76	3,93	4,07	4,27	4,35	4,60	4,61
9	7	1,84		7	4,03		64	3,87	4,05	4,20	4,33	4,44	4,81	4,82
10	8	2,00		8	4,03		72	3,98	4,19	4,35	4,49	4,65	5,06	5,10
11	9	2,15		9	4,59		80	4,23	4,46	4,66	5,11	5,39	5,41	5,43
12	10	2,31		10	4,59		96	4,45	4,74	4,96	5,36	5,84	5,87	5,88
13	11	2,46		11	4,59		112	4,70	5,00	5,27	5,74	6,30	6,32	6,39
14	12	2,62		12	4,59		128	4,79	5,02	5,33	5,88	6,51	6,51	6,53
15	13	2,78		13	5,23		144	5,07	5,33	5,69	6,31	7,59	8,48	8,93
16	14	4,73		14	5,97		160	6,51	7,01	7,50	8,25	9,24	9,73	11,47
17	15	4,73		15	5,97		176	6,82	7,37	7,91	8,73	9,82	10,37	12,28
18	16	4,73		16	5,97		192	7,13	7,73	8,32	9,21	10,40	11,00	13,08
19							208	7,46	8,11	8,75	9,72	11,01	11,66	13,91
20							224	7,75	8,44	9,14	10,18	11,57	12,26	14,69
21							240	8,07	8,82	9,56	10,68	12,16	12,91	15,51
Sheet1

Sheet2
I used 3 auxiliary columns (H, I, J) to simplify the formula in column F and also for better understanding and ease of editing.

07062022 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J
1	SKU	Description	Weight	Carrier	Zone	Cost		Column	Row	Zone Relative Position
2	1802	11x14 Canvas - Felt Back	35,04	Select	8	9,1756		16	3	7
3	1122	White Ceramic Mug 11oz	13	Parcels	1	5,2312		4	13	1
4	5069	Mouse Pad	8	Flats	1	1,9968		1	8	1
5	11500	5x7 JOURNAL - WIRE-O	14	BPM	5	2,8644		7	1	4
Sheet2

Cell Formulas
Range	Formula
H2:H5	H2	=MATCH(D2,Sheet1!$1:$1,0)-1
I2:I5	I2	=IFERROR(MATCH(C2,INDEX(Sheet1!A$3:W$21,0,H2))+IF(ISNUMBER(MATCH(C2,INDEX(Sheet1!A$3:W$21,0,H2),0)),0,1),1)
J2:J5	J2	=MATCH("*"&E2&"*",INDEX(Sheet1!$2:$2,H2+1):INDEX(Sheet1!$2:$2,23),0)
F2:F5	F2	=INDEX(Sheet1!A$3:W$21,I2,J2+H2)

Hope this helps

M.

 

[Bjomesphat]

Maybe something like this

Sheet1
Note the headers on row 1 on Sheet1 (gray) - they all need to be in the same column as Zone 1 or Zone 1&2, i.e. precisely on B1, E1, H1, Q1.

07062022 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1		Flats			Parcels			BPM									Select
2	OZ	Zone 1		OZ	Zone 1		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8		OZ	Zone 1&2	Zone 3	Zone 4	Zone 5	Zone 6	Zone 7	Zone 8
3	1	1,15		1	3,45		16	2,84	2,86	2,86	2,86	2,89	2,90	2,90		16	4,90	5,23	5,60	6,16	6,23	6,36	6,36
4	2	1,15		2	3,45		24	2,96	3,04	3,08	3,17	3,25	3,26	3,27		32	5,20	5,73	6,07	6,60	7,24	7,59	7,82
5	3	1,24		3	3,45		32	3,16	3,27	3,35	3,50	3,67	3,68	3,70		48	5,45	6,14	6,84	7,32	8,26	8,73	9,18
6	4	1,37		4	3,45		40	3,41	3,55	3,63	3,79	4,01	4,01	4,04		64	7,16	7,68	8,56	9,16	10,24	10,67	10,67
7	5	1,53		5	4,03		48	3,52	3,68	3,79	3,98	4,22	4,38	4,39
8	6	1,68		6	4,03		56	3,76	3,93	4,07	4,27	4,35	4,60	4,61
9	7	1,84		7	4,03		64	3,87	4,05	4,20	4,33	4,44	4,81	4,82
10	8	2,00		8	4,03		72	3,98	4,19	4,35	4,49	4,65	5,06	5,10
11	9	2,15		9	4,59		80	4,23	4,46	4,66	5,11	5,39	5,41	5,43
12	10	2,31		10	4,59		96	4,45	4,74	4,96	5,36	5,84	5,87	5,88
13	11	2,46		11	4,59		112	4,70	5,00	5,27	5,74	6,30	6,32	6,39
14	12	2,62		12	4,59		128	4,79	5,02	5,33	5,88	6,51	6,51	6,53
15	13	2,78		13	5,23		144	5,07	5,33	5,69	6,31	7,59	8,48	8,93
16	14	4,73		14	5,97		160	6,51	7,01	7,50	8,25	9,24	9,73	11,47
17	15	4,73		15	5,97		176	6,82	7,37	7,91	8,73	9,82	10,37	12,28
18	16	4,73		16	5,97		192	7,13	7,73	8,32	9,21	10,40	11,00	13,08
19							208	7,46	8,11	8,75	9,72	11,01	11,66	13,91
20							224	7,75	8,44	9,14	10,18	11,57	12,26	14,69
21							240	8,07	8,82	9,56	10,68	12,16	12,91	15,51
Sheet1

Sheet2
I used 3 auxiliary columns (H, I, J) to simplify the formula in column F and also for better understanding and ease of editing.

07062022 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J
1	SKU	Description	Weight	Carrier	Zone	Cost		Column	Row	Zone Relative Position
2	1802	11x14 Canvas - Felt Back	35,04	Select	8	9,1756		16	3	7
3	1122	White Ceramic Mug 11oz	13	Parcels	1	5,2312		4	13	1
4	5069	Mouse Pad	8	Flats	1	1,9968		1	8	1
5	11500	5x7 JOURNAL - WIRE-O	14	BPM	5	2,8644		7	1	4
Sheet2

Cell Formulas
Range	Formula
H2:H5	H2	=MATCH(D2,Sheet1!$1:$1,0)-1
I2:I5	I2	=IFERROR(MATCH(C2,INDEX(Sheet1!A$3:W$21,0,H2))+IF(ISNUMBER(MATCH(C2,INDEX(Sheet1!A$3:W$21,0,H2),0)),0,1),1)
J2:J5	J2	=MATCH("*"&E2&"*",INDEX(Sheet1!$2:$2,H2+1):INDEX(Sheet1!$2:$2,23),0)
F2:F5	F2	=INDEX(Sheet1!A$3:W$21,I2,J2+H2)

Hope this helps

M.

Been playing with this now, and this indeed works wonderfully. Thank you very much for the assistance! The use of auxillary columns is very clever, and should come in handy in the future.

 

[Marcelo Branco]

Thank you very much for the assistance!

You're welcome. Glad to help.

M.