Dynamic large funtion?

[monaldo12]

Hi all,

Hopefully it's possible, what not in excel
I'm familiar with the large function, the thing is that I would like to make it dynamic with a vlookup or match function in it. So in the below case if a user wants to find out the highest sales of the three weeks by a certain product, he just needs to fill in the product in a certain cell, the formula looks it up in the columns with the sales numbers. But to do it I think it first would need like a workaround to first find the right row and the look for the highest number in the row matching with the product. As far as I have figured it out, the large formula keeps on searching in the rows which I have determined and it's not dynamic.
Is this possible?



[TABLE="width: 854"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Geography[/TD]
[TD]Product[/TD]
[TD]Measure[/TD]
[TD]Wk 1, 2018[/TD]
[TD]Wk 2, 2018[/TD]
[TD]Wk 3, 2018[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]TOTAAL FACE CARE [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]3169349,00[/TD]
[TD="align: right"]4053950,00[/TD]
[TD="align: right"]3930085,00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]VENDOR NOT FOUND BIO OIL [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]153978,00[/TD]
[TD="align: right"]117877,00[/TD]
[TD="align: right"]696625,00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]BEIERSDORF NIVEA [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]28263,00[/TD]
[TD="align: right"]31669,00[/TD]
[TD="align: right"]49352,00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]L'OREAL L'OREAL [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]780267,00[/TD]
[TD="align: right"]855151,00[/TD]
[TD="align: right"]639315,00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]PROCTER & GAMBLE OLAZ [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]211439,00[/TD]
[TD="align: right"]603582,00[/TD]
[TD="align: right"]547956,00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]LOUIS WIDMR INTRNTNL LOUIS WIDMER [/TD]
[TD]Euro Sales[/TD]
[TD="align: right"]276322,00[/TD]
[TD="align: right"]429635,00[/TD]
[TD="align: right"]245835,00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Geography	Product	Measure	Wk 1, 2018	Wk 2, 2018	Wk 3, 2018
2	Walmart	TOTAAL FACE CARE	Euro Sales	3169349	4053950	3930085
3	Walmart	VENDOR NOT FOUND BIO OIL	Euro Sales	153978	117877	696625
4	Walmart	BEIERSDORF NIVEA	Euro Sales	28263	31669	49352
5	Walmart	L'OREAL L'OREAL	Euro Sales	780267	855151	639315
6	Walmart	PROCTER & GAMBLE OLAZ	Euro Sales	211439	603582	547956
7	Walmart	LOUIS WIDMR INTRNTNL LOUIS WIDMER	Euro Sales	276322	429635	245835
8
9
10
11
12	L'OREAL L'OREAL	855151
Forecast

Cell Formulas
Range	Formula
B12	=MAX(INDEX(D2:F7,MATCH(A12,B2:B7,0),0))

 

[monaldo12]

How about
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=MAX(INDEX(D2:F7,MATCH(A12,B2:B7,0),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Exactly what I was looking for, thanks a lot!!!!!!!!!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[monaldo12]

You're welcome & thanks for the feedback

Hi, sorry I wasn't specific enough in my question. The thing is that I want to create a top 10 values for that certain product. That's why I used function large so I can state nr 1, then nr2 and so on....
With this max function I can only find the nr1, is it possible to make a top 10 for example? But depending on the product it has to search in a certain row.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Geography	Product	Measure	Wk 1, 2018	Wk 2, 2018	Wk 3, 2018
2	Walmart	TOTAAL FACE CARE	Euro Sales	3169349	4053950	3930085
3	Walmart	VENDOR NOT FOUND BIO OIL	Euro Sales	153978	117877	696625
4	Walmart	BEIERSDORF NIVEA	Euro Sales	28263	31669	49352
5	Walmart	L'OREAL L'OREAL	Euro Sales	780267	855151	639315
6	Walmart	PROCTER & GAMBLE OLAZ	Euro Sales	211439	603582	547956
7	Walmart	LOUIS WIDMR INTRNTNL LOUIS WIDMER	Euro Sales	276322	429635	245835
8
9
10
11
12	L'OREAL L'OREAL	855151	780267	639315
Forecast

Cell Formulas
Range	Formula
B12	=LARGE(INDEX($D$2:$F$7,MATCH($A$12,$B$2:$B$7,0),0),COLUMNS($A$1:A1))

 

[monaldo12]

Hi, sorry for the trouble, final small step hopefully. This formula works when I copy it further to the right. Is it possible to make it vertically so the outcome of the top 10 is below each others? And is it possible , maybe in a cell next to it, to give the exact week of that certain value. So for example that I can see it's week 3, 2018?

Sorry for all the questions, but you're a genius so hopefully it's an easy one for you

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F
1	Geography	Product	Measure	Wk 1, 2018	Wk 2, 2018	Wk 3, 2018
2	Walmart	TOTAAL FACE CARE	Euro Sales	3169349	4053950	3930085
3	Walmart	VENDOR NOT FOUND BIO OIL	Euro Sales	153978	117877	696625
4	Walmart	BEIERSDORF NIVEA	Euro Sales	28263	31669	49352
5	Walmart	L'OREAL L'OREAL	Euro Sales	780267	855151	639315
6	Walmart	PROCTER & GAMBLE OLAZ	Euro Sales	211439	603582	547956
7	Walmart	LOUIS WIDMR INTRNTNL LOUIS WIDMER	Euro Sales	276322	429635	245835
8
9
10
11
12	L'OREAL L'OREAL	855151	Wk 2, 2018
13		780267	Wk 1, 2018
14		639315	Wk 3, 2018
Forecast

Cell Formulas
Range	Formula
B12	=LARGE(INDEX($D$2:$F$7,MATCH($A$12,$B$2:$B$7,0),0),ROWS($A$1:A1))
C12	=INDEX($D$1:$F$1,MATCH($B12,INDEX($D$2:$F$7,MATCH($A$12,$B$2:$B$7,0),0),0))

And please do not quote entire posts as it just clutters up the thread.

 

[monaldo12]

How about
And please do not quote entire posts as it just clutters up the thread.

Thank you a lot, it works perfect!!!

 

[Fluff]

You're welcome & thanks for the feedback