Find Item Code Then Min value in same row

[crzytimes]

Hi I have a database that I export every week from SAP. I'm trying to set up a vlookup or index match with a MIN function to return the minimum value in a row, but am really struggling for some reason. Can I get a bit of help? Once I get assistance with finding the Min value, I'm pretty sure I'll be able to research how to find the Header Name the Min value is located under.

Thanks!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1		Wk 27	Wk 28	Wk 29	Wk 30	Wk 31	Wk 32
2	Item A	100	55	4	87	52	47
3	Item B	93	15	73	54	85	23
4	Item C	30	33	5	41	1	4
5
6
7
8		Min
9	Item A	4
10	Item B	15
11	Item C	1
12
Data

Cell Formulas
Range	Formula
B9:B11	B9	=AGGREGATE(15,6,$B$2:$G$4/($A$2:$A$4=A9),1)

Also please update your account details to show what version of Excel you are using, as this affects which functions you have got. Thanks

 

[Eric W]

Using Fluff's layout, this formula also works:

B9: =MIN(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),0))

 

[crzytimes]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G
1		Wk 27	Wk 28	Wk 29	Wk 30	Wk 31	Wk 32
2	Item A	100	55	4	87	52	47
3	Item B	93	15	73	54	85	23
4	Item C	30	33	5	41	1	4
5
6
7
8		Min
9	Item A	4
10	Item B	15
11	Item C	1
12
Data

Cell Formulas
Range	Formula
B9:B11	B9	=AGGREGATE(15,6,$B$2:$G$4/($A$2:$A$4=A9),1)

Also please update your account details to show what version of Excel you are using, as this affects which functions you have got. Thanks

Wow I've never used the Aggregate formula before. I'm about to go into a rabbit hole of youtube videos Thanks! I also updated my profile per your request. Didn't know about that feature!

 

[crzytimes]

Using Fluff's layout, this formula also works:

B9: =MIN(INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),0))

This works as well I was trying to put my min function inside my Index / Match. Thank you

 

[Fluff]

To get the column header you can use either the formula in col C or D if you have the new functions

+Fluff New.xlsm
	A	B	C	D	E	F	G
1		Wk 27	Wk 28	Wk 29	Wk 30	Wk 31	Wk 32
2	Item A	100	55	4	87	4	47
3	Item B	93	15	73	54	85	23
4	Item C	30	33	5	41	1	4
5
6
7
8		Min	Index	Filter
9	Item A	4	Wk 29	Wk 29	Wk 31
10	Item B	15	Wk 28	Wk 28
11	Item C	1	Wk 31	Wk 31
Data

Cell Formulas
Range	Formula
B9:B11	B9	=AGGREGATE(15,6,$B$2:$G$4/($A$2:$A$4=A9),1)
C9:C11	C9	=INDEX($B$1:$G$1,MATCH(B9,INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),),0))
D9:E9,D10:D11	D9	=FILTER($B$1:$G$1,FILTER($B$2:$G$4,$A$2:$A$4=A9)=B9)
Dynamic array formulas.

 

[crzytimes]

To get the column header you can use either the formula in col C or D if you have the new functions

+Fluff New.xlsm
	A	B	C	D	E	F	G
1		Wk 27	Wk 28	Wk 29	Wk 30	Wk 31	Wk 32
2	Item A	100	55	4	87	4	47
3	Item B	93	15	73	54	85	23
4	Item C	30	33	5	41	1	4
5
6
7
8		Min	Index	Filter
9	Item A	4	Wk 29	Wk 29	Wk 31
10	Item B	15	Wk 28	Wk 28
11	Item C	1	Wk 31	Wk 31
Data

Cell Formulas
Range	Formula
B9:B11	B9	=AGGREGATE(15,6,$B$2:$G$4/($A$2:$A$4=A9),1)
C9:C11	C9	=INDEX($B$1:$G$1,MATCH(B9,INDEX($B$2:$G$4,MATCH(A9,$A$2:$A$4,0),),0))
D9:E9,D10:D11	D9	=FILTER($B$1:$G$1,FILTER($B$2:$G$4,$A$2:$A$4=A9)=B9)
Dynamic array formulas.

Also - Fantastic I truly appreciate your time with my issue. I manage roughly 300 sku's of varying complexity. It normally takes me about 3-4 days each month to do my planning by looking at each individual sku to ensure proper coverage in all of my markets. If I can manipulate the export, I should be able to cut that 3-4 days down to an afternoon by highlighting my shortfalls. This is exciting!

 

[Fluff]

Glad we could help & thanks for the feedback.