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

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

<tbody>
</tbody>

Worksheet 2: Rank

	A	B	C
1		Category	Rank
2		Fiction	2 Week
3
4		Title
5	1	Heroes
6	2	Martyn L
7	3	Batman
8	4
9	5

<tbody>
</tbody>

 

[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

	Category	Rank
	Fiction	2 Week
	Title
1	HEROES	50
2	MARTYN L	10
3	BATMAN	2
4

<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>
</TBODY>

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.