Dynamic Search Based on Multiple Crtieria between worksheets...Index Match, Rank, Large, Sumproduct?

[crinaldi90]

I have tried going about this in a variety of different ways and seem to not be able to figure out the solution. The data itself shown below is not real and is rather there just to show what I need help doing, and also for your reference the 1st worksheet is several thousand rows longer with that many titles and a total of over 50 different categories. On the 1st worksheet 'Database', I have all the sales data for each title for the past 1, 2, and 4 weeks in columns C, D, and E, and then each ranked within their specific category for the corresponding period in columns F, G, and H. On the 2nd worksheet 'Rank', cells B2 and C2 are drop downs (data validation lists) to select the category and week ranking you want to choose. These are not dynamic lists that index the first worksheet, although I think that could be a potential solution to have built in if somebody thinks that will work with what I am asking.

What I need help with is that based upon the drop down selections that are chosen in those two cells, B5 will populate the title from the 1st worksheet that has the greatest sales figures for it's category within the period chosen. Then obviously in B6 it will have the second highest and so on thereafter. Ideally, I would like to not have to have the ranking columns in the first worksheet and have the formula I need within the Rank worksheet populate by searching through just the sales figures in columns C, D, and E. I added those in there in case it is a required step by using the numbers in cells A5:A9 in the 2nd worksheet to reference the rankings in columns F,G, and H in the 1st worksheet. As an example on the Rank Worksheet, if you were to choose 'Fiction' and '2 Week', the cells with the formula I am trying to figure out would populate in order: Heroes, Martyn L, and Batman as shown below. I've tried using many variations of the Index, Match, Large, Rank, Sumproduct, Vlookup, indirect, row functions and am fairly confident that some combination of these will work, but I cant figure out what it exactly is. Let me know if this makes sense and whether I left out any important information that might be critical to understanding/answering this question. I appreciate any help or insight somebody can provide here. Thanks!

Worksheet 1: Database


[TABLE="class: grid, width: 1007"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Title[/TD]
[TD]Category[/TD]
[TD]1 Week[/TD]
[TD]2 Week[/TD]
[TD]4 Week[/TD]
[TD]Rank 1 wk[/TD]
[TD]Rank 2 wk[/TD]
[TD]Rank 4 wk[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]LIVING IN THE END TIMES[/TD]
[TD]Comics & Graphic Novels[/TD]
[TD]89[/TD]
[TD]109[/TD]
[TD]107[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BATMAN[/TD]
[TD]Fiction[/TD]
[TD]27[/TD]
[TD]2[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]MORTE D'URBAN[/TD]
[TD]Health & Fitness[/TD]
[TD]80[/TD]
[TD]99[/TD]
[TD]72[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BLUE ZONES, THE[/TD]
[TD]Self Help[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]MINDFUL EATING[/TD]
[TD]Comics & Graphic Novels[/TD]
[TD]21[/TD]
[TD]19[/TD]
[TD]13[/TD]
[TD]18[/TD]
[TD]14[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PO: HEROES ELEMENTAL CHAOS[/TD]
[TD]Comics & Graphic Novels[/TD]
[TD]67[/TD]
[TD]67[/TD]
[TD]70[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]TRANSMET VOL 08 NEW EDITION[/TD]
[TD]Photography[/TD]
[TD]23[/TD]
[TD]22[/TD]
[TD]28[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]HEROES[/TD]
[TD]Fiction[/TD]
[TD]37[/TD]
[TD]50[/TD]
[TD]38[/TD]
[TD]17[/TD]
[TD]17[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]MARTYN L[/TD]
[TD]Fiction[/TD]
[TD]24[/TD]
[TD]10[/TD]
[TD]26[/TD]
[TD]23[/TD]
[TD]18[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]CANNING[/TD]
[TD]Cooking[/TD]
[TD]26[/TD]
[TD]40[/TD]
[TD]38[/TD]
[TD]11[/TD]
[TD]11[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]WING CHUN COMPENDIUM V1[/TD]
[TD]Religion[/TD]
[TD]402[/TD]
[TD]363[/TD]
[TD]439[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]BUDDHA V. 3[/TD]
[TD]Comics & Graphic Novels[/TD]
[TD]16[/TD]
[TD]25[/TD]
[TD]21[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]ALMOST ANIMAL ALPHABET[/TD]
[TD]Comics & Graphic Novels[/TD]
[TD]56[/TD]
[TD]71[/TD]
[TD]103[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Worksheet 2: Rank

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Category[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Fiction[/TD]
[TD]2 Week[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Title[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]Heroes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Martyn L[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD]Batman[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[chrism216]

It seems to me you could set this up with a pivot table, unless im missing something?

 

[crinaldi90]

Yes i know and that's what I would have done if there were not other components of what I am doing. On the 2nd worksheet there is more than just the title column, but this one part that I cannot figure out a solution to is preventing me finishing everything else. A pivot table conflicts with the other pieces of worksheet 2 and for those other parts to make sense I just need a solution to the scenario above.

 

[chrism216]

its complicated enough to set up a manual filter, let alone a manual, sorted one. It is possible, but by a truly messy collection of multiple instances ROW(), ROWS(), RANK(), INDEX(), MATCH(), all packed into an array formula.
From a pragmatic point of view, you really should try to work out your solution with a pivot table. Maybe you can set up your pivot table on hidden rows that show only the filters, and reference its contents it to the correct cells?
Cheers

 

[Aladin Akyurek]

Select A1:H14 of Database and name the selection DB using the Name Box.

Rank

[TABLE="width: 210"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4380" width=123><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3299" width=93><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Category[/TD]
[TD="class: xl64, width: 93, bgcolor: white"]Rank[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 123, bgcolor: white"]Fiction[/TD]
[TD="class: xl63, width: 93, bgcolor: white"]2 Week[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl63, width: 123, bgcolor: white"] [/TD]
[TD="class: xl63, width: 93, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"] [/TD]
[TD="class: xl64, width: 123, bgcolor: white"]Title[/TD]
[TD="class: xl63, width: 93, bgcolor: white"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]1[/TD]
[TD="class: xl66, width: 123, bgcolor: white"]HEROES[/TD]
[TD="class: xl63, width: 93, bgcolor: white"]50[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]2[/TD]
[TD="class: xl66, width: 123, bgcolor: white"]MARTYN L[/TD]
[TD="class: xl63, width: 93, bgcolor: white"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]3[/TD]
[TD="class: xl66, width: 123, bgcolor: white"]BATMAN[/TD]
[TD="class: xl63, width: 93, bgcolor: white"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]4[/TD]
[TD="class: xl66, width: 123, bgcolor: white"] [/TD]
[TD="class: xl63, width: 93, bgcolor: white"] [/TD]
[/TR]
</TBODY>[/TABLE]

B5, control+shift+enter (CSE), not just enter, and copy down:

=IF($C5="","",INDEX(DB,SMALL(IF(INDEX(DB,0,MATCH($C$2,INDEX(DB,1,0),0))=$C5,
  ROW(DB)-ROW(INDEX(DB,1,1))+1),COUNTIF($C$5:C5,C5)),1))

C5, CSE and copy down:

=IFERROR(LARGE(IF(INDEX(DB,0,2)=$B$2,
  INDEX(DB,0,MATCH($C$2,INDEX(DB,1,0),0))),ROWS($C$5:C5)),"")

 

[crinaldi90]

Thanks so much, greatly appreciated!!!

 

[Aladin Akyurek]

Thanks so much, greatly appreciated!!!

You are welcome.