Dynamic large funtion?

M

monaldo12

New Member
Joined
Mar 17, 2016
Messages
45
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?



GeographyProductMeasureWk 1, 2018Wk 2, 2018Wk 3, 2018
WalmartTOTAAL FACE CARE Euro Sales3169349,004053950,003930085,00
WalmartVENDOR NOT FOUND BIO OIL Euro Sales153978,00117877,00696625,00
WalmartBEIERSDORF NIVEA Euro Sales28263,0031669,0049352,00
WalmartL'OREAL L'OREAL Euro Sales780267,00855151,00639315,00
WalmartPROCTER & GAMBLE OLAZ Euro Sales211439,00603582,00547956,00
WalmartLOUIS WIDMR INTRNTNL LOUIS WIDMER Euro Sales276322,00429635,00245835,00

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
How about


Excel 2013/2016
ABCDEF
1GeographyProductMeasureWk 1, 2018Wk 2, 2018Wk 3, 2018
2WalmartTOTAAL FACE CAREEuro Sales316934940539503930085
3WalmartVENDOR NOT FOUND BIO OILEuro Sales153978117877696625
4WalmartBEIERSDORF NIVEAEuro Sales282633166949352
5WalmartL'OREAL L'OREALEuro Sales780267855151639315
6WalmartPROCTER & GAMBLE OLAZEuro Sales211439603582547956
7WalmartLOUIS WIDMR INTRNTNL LOUIS WIDMEREuro Sales276322429635245835
8
9
10
11
12L'OREAL L'OREAL855151
Forecast
Cell Formulas
RangeFormula
B12=MAX(INDEX(D2:F7,MATCH(A12,B2:B7,0),0))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
Fluff said:
You're welcome & thanks for the feedback
Click to expand...

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.
 
Upvote 0
How about


Excel 2013/2016
ABCDEF
1GeographyProductMeasureWk 1, 2018Wk 2, 2018Wk 3, 2018
2WalmartTOTAAL FACE CAREEuro Sales316934940539503930085
3WalmartVENDOR NOT FOUND BIO OILEuro Sales153978117877696625
4WalmartBEIERSDORF NIVEAEuro Sales282633166949352
5WalmartL'OREAL L'OREALEuro Sales780267855151639315
6WalmartPROCTER & GAMBLE OLAZEuro Sales211439603582547956
7WalmartLOUIS WIDMR INTRNTNL LOUIS WIDMEREuro Sales276322429635245835
8
9
10
11
12L'OREAL L'OREAL855151780267639315
Forecast
Cell Formulas
RangeFormula
B12=LARGE(INDEX($D$2:$F$7,MATCH($A$12,$B$2:$B$7,0),0),COLUMNS($A$1:A1))
 
Upvote 0
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 :)
 
Last edited by a moderator:
Upvote 0
How about


Excel 2013/2016
ABCDEF
1GeographyProductMeasureWk 1, 2018Wk 2, 2018Wk 3, 2018
2WalmartTOTAAL FACE CAREEuro Sales316934940539503930085
3WalmartVENDOR NOT FOUND BIO OILEuro Sales153978117877696625
4WalmartBEIERSDORF NIVEAEuro Sales282633166949352
5WalmartL'OREAL L'OREALEuro Sales780267855151639315
6WalmartPROCTER & GAMBLE OLAZEuro Sales211439603582547956
7WalmartLOUIS WIDMR INTRNTNL LOUIS WIDMEREuro Sales276322429635245835
8
9
10
11
12L'OREAL L'OREAL855151Wk 2, 2018
13780267Wk 1, 2018
14639315Wk 3, 2018
Forecast
Cell Formulas
RangeFormula
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.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,983
Messages
6,122,598
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top