Large function

[cunningAce]

I have sales data for several weeks for over 100 products one sheet. Week numbers horizontally across the top, Product details vertically down the left.

I have created a drop down list (separate sheet) of week numbers and I want to return the top 10 largest sales values depending on the week number selected.

I can achieve this using an index, match function with a nested large function but only for a defined array selection.
My question is how can I get the array to look up the correct data set according to the week number selected?

Thanks

Mike

 

[DRSteele]

Welcome to the forum.

Doing this with formulas will drive you around the bend, for many reasons, not the least of which is ties. I suggest you use PivotTables and then a slicer to filter; PTs were built for this very purpose.

 

[ajamess]

It wouldn't be too bad for this case imo.
My thoughts are assuming:
-You have a data sheet, products down column A with no total row, Weeks across remaining top rows. Last week listed is in column Y.
-Your drop down of week number is on a sheet "Dashboard" A1 is "Rank", b1 is "Product", c1 is "Sales". 1-10 is listed in A2-A11. And your dropdown is in D1.

On Data sheet, in Z2 use this formula then fill down.

=RANK(OFFSET(A2,0,MATCH(Dashboard!$D$1,$1:$1,0)-1,1,1),OFFSET($A$2,0,MATCH(Dashboard!$D$1,$1:$1,0)-1,COUNTA(A:A)-1,1))

Then on B2 of Dashboard use the formula

=index(Data!A:A,match(a2,Data!Z:Z,0))

On C2 of the Dashboard use the formula

=index(offset(Data!A:A,0,match($D$1,Data!$1:$1,0)) , match(a2,Data!Z:Z,0))

I think that would do it, or get you close, didn't have time to test it all out, hopefully all the $ are in right places that are needed :D)

Buuuuuut. Pivot tables are your friend :D

 

[cunningAce]

Works perfectly with a few adjustments to match my data.
I had to make each sales value a unique number using the ROW() function to avoid any 'ties'.

Thanks for the help. Much appreciated