Formula suggestions (Index/match, lookup, or other)

[tt1995]

Hi guys!

I am trying to find a formula that allows me to match/insert the Batch size from column "J" to column "D", while:

• Matching the Product (column "B" = "G"), and
• Batch size being larger in column "C" than in "H"

Example 1: Product A; Order QTY = 10 280 --> Batch size 2/3
Example 2: Product A; Order QTY = 5 001 --> Batch size 1/3

Later I would like to insert COGs from column "I" to "E" (matched as above) as well.

I have tried formulas such as the different lookups and match/index but without success. Very thankful for suggestions!

 

[JamesCanale]

MrExcelPlayground12.xlsx
	B	C	D	E	F	G	H	I	J
2	Products	Order Qty	Batch Size	COGs		Products	Order Qty	COGs	Batch Size
3	A	10280	2/3	27		A	5000	20	1/3
4	A	5001	1/3	20		A	10000	27	2/3
5	A	6998	1/3	20		A	30000	31	3/3
6	A	778	#N/A			B	5000	11	1/3
7	A	29736	2/3	27		B	10000	16	2/3
8	A	10197	2/3	27		B	30000	20	3/3
9	B	7776	1/3	11		C	5000	100	1/3
10	B	61992	3/3	20		C	10000	130	2/3
11	B	22929	2/3	16		C	27000	150	3/3
12	B	14007	2/3	16		D	5000	3	1/3
13	B	4643	#N/A			D	10000	4	2/3
14	C	51813	3/3	150		D	25000	5	3/3
15	C	19937	2/3	130
16	C	87769	3/3	150
Sheet29

Cell Formulas
Range	Formula
D14:E16,D7:E12,D6,D13,D3:E5	D3	=VLOOKUP(C3,FILTER($H$3:$J$14,$G$3:$G$14=B3),{3,2},TRUE)
Dynamic array formulas.

 

[ExceLoki]

try this out
------------

index match round.xlsx
	B	C	D	E	F	G	H	I	J
2	Product	Order QTY	Batch Size	COGs		Product	Order QTY	COGs	Batch Size
3	Product A	10280	2/3	27		Product A	5000	20	1/3
4	Product A	10530	2/3	27		Product A	10000	27	2/3
5	Product A	5001	1/3	20		Product A	30000	31	1
6	Product A	778	1/3	20		Product B	5000	11	1/3
7	Product A	29736	2/3	27		Product B	10000	16	2/3
8	Product A	10197	2/3	27		Product B	30000	20	1
9	Product B	7776	1/3	11		Prodcut C	5000	100	1/3
10	Product B	61992	1	20		Prodcut C	10000	130	2/3
11	Product B	22929	2/3	16		Prodcut C	27000	150	1
12	Product B	14007	2/3	16		Product D	5000	3	1/3
13	Product B	4643	1/3	11		Product D	10000	4	2/3
14	Product B	51813	1	20		Product D	25000	5	1
15	Product B	19937	2/3	16
16	Prodcut C	87769	1	150
17	Prodcut C	10152	2/3	130
18	Prodcut C	12921	2/3	130
19	Prodcut C	26214	2/3	130
20	Prodcut C	22680	2/3	130
21	Prodcut C	3024	1/3	100
22	Product D	30842	1	5
23	Product D	30557	1	5
24	Product D	9072	1/3	3
25	Product D	9504	1/3	3
26	Product D	9504	1/3	3
27	Product D	26106	1	5
28	Product D	25920	1	5
29	Product E	9072
30	Product E	25056
31	Product E	5400
32	Product E	9936
33	Product E	10152
34	Product E	10152
Sheet1

Cell Formulas
Range	Formula
D3:D34	D3	=IFERROR(INDEX($J$3:$J$100,MATCH(1,([@Product]=$G$3:$G$100)*(IF(MAX(IF(FILTER($H$3:$H$100, [@Product]=$G$3:$G$100)<[@[Order QTY]],FILTER($H$3:$H$100, [@Product]=$G$3:$G$100)))=0,MIN(FILTER($H$3:$H$100, [@Product]=$G$3:$G$100)),MAX(IF(FILTER($H$3:$H$100, [@Product]=$G$3:$G$100)<[@[Order QTY]],FILTER($H$3:$H$100, [@Product]=$G$3:$G$100))))=$H$3:$H$100),0)),"")
E3:E34	E3	=IFERROR(INDEX($I$3:$I$100,MATCH(1,([@Product]=$G$3:$G$100)*([@[Batch Size]]=$J$3:$J$100),0)),"")

 

[tt1995]

MrExcelPlayground12.xlsx
	B	C	D	E	F	G	H	I	J
2	Products	Order Qty	Batch Size	COGs		Products	Order Qty	COGs	Batch Size
3	A	10280	2/3	27		A	5000	20	1/3
4	A	5001	1/3	20		A	10000	27	2/3
5	A	6998	1/3	20		A	30000	31	3/3
6	A	778	#N/A			B	5000	11	1/3
7	A	29736	2/3	27		B	10000	16	2/3
8	A	10197	2/3	27		B	30000	20	3/3
9	B	7776	1/3	11		C	5000	100	1/3
10	B	61992	3/3	20		C	10000	130	2/3
11	B	22929	2/3	16		C	27000	150	3/3
12	B	14007	2/3	16		D	5000	3	1/3
13	B	4643	#N/A			D	10000	4	2/3
14	C	51813	3/3	150		D	25000	5	3/3
15	C	19937	2/3	130
16	C	87769	3/3	150
Sheet29

Cell Formulas
Range	Formula
D14:E16,D7:E12,D6,D13,D3:E5	D3	=VLOOKUP(C3,FILTER($H$3:$J$14,$G$3:$G$14=B3),{3,2},TRUE)
Dynamic array formulas.

Very smooth - thanks a lot!