filter Top 5 by 2 criterions

[Omer_K]

Dear All,
I have a database divided by countries and letters,
(Each letter has a separate column),
I also have a data validaion list in cell J3 with all letters (from letter A to letter F),
And another cell data validaion list in cell J6 that also has all the letters (from letter A to letter F),
I would like to know how I can use by functions (not 365 functions like filter) to list all countries so that if I select a letter in cell J3 the list will show me all the countries with the highest score (eg 10 countries) in that letter and once I select another letter in cell J6 the list will narrow For the 5 countries on the existing list with the highest score in the second list (J6),
I hope i was clear...

חוברת2
	B	C	D	E	F	G	H	I	J
1	A	B	C	D	E	F
2	1.25	4.65	1.73	3.68	4	6.47
3	4.5	6.26	5.1	4.32	1.23	1.57		First Choose	B
4	3.6	5.3	1.07	6.21	3.86	1.01
5	6.75	2.38	1.42	4.01	6.07	5.25
6	5.87	3.17	2.89	6.64	2.92	1.47		Second Choose
7	5.28	1.64	1.48	3.87	3.44	1.73
8	4.82	2.73	2.85	4.47	4.21	1.3
9	6.22	5.18	1.06	1.89	3.99	4.38
10	5.72	1.8	5.44	5.79	5.6	4.52
11	4.41	4.87	6.6	5.91	3.08	6.9
12	4.23	3.34	4.65	5.92	4.42	5.08
13	5.87	6.68	6.42	4.31	1.91	6.99
14	4.63	6.51	3.73	4.29	6.22	5.29
15	5.35	2.28	2.74	5.62	4.06	6.16
16	3.53	4.41	2.49	6.61	5.52	4.26
17	3.68	2.85	4.88	6.11	5.51	1.21
18	3.99	2.81	4.6	3.48	2.59	1.62
19	2.43	6.66	3.03	3.63	3.42	1.6
20	3.64	6.9	6.91	5.21	6.62	3.08
21	5.51	2.09	5.9	4.63	2.55	5.2
22	5.08	3.01	4.38	5.49	2.24	3
23	6.93	2.97	4.5	5.81	2.62	5.43
24	4.51	2.6	4.06	4.02	5.64	1.61
25	1.06	6.1	5.46	6.77	4.48	5.03
26	1.68	6.53	6.91	1.3	4.31	2.33
27	2.24	4.63	3.3	5.31	5.33	4.12
28	6.64	5.49	3.27	4.45	1.38	4.76
29	4.69	3.76	2.65	3.04	4.78	6.64
30	4.74	5.74	4.04	2.48	1.89	1.54
31	1.01	4.16	3.58	5.63	2.61	4.81
32	3.96	2.14	4.06	5.01	2.56	3.86
33	3.41	5.06	3.46	2.17	1.44	3.99
34	6.99	3.59	3.97	4.23	4.79	3.47
35	5.04	5.49	1.7	1.4	4.24	1.59
36	2.82	2.18	6.46	2.76	1.87	3.2
Data

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1

Thank you,

 

[jasonb75]

Also, I should point out that selecting B and E results in a 3 way tie for 5th place, does that mean that 7 results should be shown instead of 5?

If only 5, how would you decide which of the 3 tied states is the correct result?

Below is a slightly modified approach to my last reply which shows the top 5 sorted as needed, along with any ties for last place up to a maximum of 10 rows.

The yellow helper section could be hidden by using a white font with no fill colour, you just need to remember where it is so that you don't delete it in error.

Sample Data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	State	A	B	C	D	E	F
2	Alabama	2.67	1.67	2.33	1.67	3.67	2
3	Alaska	2	3.67	3.67	3	1.67	3.67		First Choose	A
4	Arizona	2.67	3	3	2	2.33	2.33
5	Arkansas	2	2	2.33	1.33	3	3
6	California	1.67	2.67	1.67	3	3	3		Second Choose	E
7	Colorado	1.67	1.33	3	3.67	2.33	2
8	Connecticut	2.67	2	1.33	1.33	3	2		State	A	B	C	D	E	F
9	Delaware	2.33	3.67	3	3.67	2.33	3.67		Nevada	3.67	3.67	1.67	2.67	3.67	3
10	Florida	2.33	2	2.67	2	3	1.33		North Dakota	3.67	1.67	2	2.67	3.67	3.67
11	Georgia	1.33	2	2	2	2.33	3		Iowa	3	3	3	2.33	3.67	2.67
12	Hawaii	2	2.67	1.33	2	3	2.33		Idaho	3	3.67	3	2	2.67	3.67
13	Idaho	3	3.67	3	2	2.67	3.67		Massachusetts[E]	3	2	3	1.33	2.33	2
14	Illinois	2.33	1.33	2.33	1.33	2.67	2		New Mexico	3	3	2.33	1.67	2.33	1.67
15	Indiana	2.33	2.33	3.67	1.33	1.67	1.67
16	Iowa	3	3	3	2.33	3.67	2.67
17	Kansas	3	3	1.67	2.33	1.33	2
18	Kentucky[E]	3	3	3.67	2.67	1.33	1.67
19	Louisiana	2.67	1.33	1.33	1.33	2.33	2.33
20	Maine	1.67	3	1.67	3	1.33	2.33
21	Maryland	1.67	3.67	2.33	2	1.67	3.67		Top 10	1st	2nd	rank
22	Massachusetts[E]	3	2	3	1.33	2.33	2		Nevada	3.67	3.67	1
23	Michigan	2	3.67	2.33	1.67	3.67	2.67		North Dakota	3.67	3.67	2
24	Minnesota	1.33	2.67	2.33	2.67	3.67	2.33		Idaho	3	2.67	4
25	Mississippi	2.33	1.67	1.67	3.67	3	3		Iowa	3	3.67	3
26	Missouri	2.33	1.67	3.67	2	2.33	3.67		Kansas	3	1.33
27	Montana	2	1.67	2	3	1.67	2.67		Kentucky[E]	3	1.33
28	Nebraska	2.67	2.67	2.67	1.33	2	3		Massachusetts[E]	3	2.33	5
29	Nevada	3.67	3.67	1.67	2.67	3.67	3		New Mexico	3	2.33	6
30	New Hampshire	1.33	3.67	3	1.67	1.67	3
31	New Jersey	1.33	1.33	1.67	2.67	1.67	2
32	New Mexico	3	3	2.33	1.67	2.33	1.67
33	New York	1.67	2.33	2.67	2	3	3.67
34	North Carolina	2.33	2	3	2.67	3.67	3
35	North Dakota	3.67	1.67	2	2.67	3.67	3.67
36	Ohio	2.67	3	2.33	3.67	1.67	3
Sheet13

Cell Formulas
Range	Formula
I9:I18	I9	=IFERROR(INDEX($I$22:$I$31,MATCH(SMALL($L$22:$L$31,ROWS(I$9:I9)),$L$22:$L$31,0)),"")
J9:O18	J9	=IF($I9="","",INDEX($B$2:$G$36,MATCH($I9,$A$2:$A$36,0),MATCH(J$8,$B$1:$G$1,0)))
I22:I31	I22	=IF(J22="","",INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$36)/(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0))=J22),COUNTIF(J$22:J22,J22))))
J22:J31	J22	=IF(ROWS(J$22:J22)>COUNTIF(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),">="&LARGE(INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),5)),"",AGGREGATE(14,6,INDEX($B$2:$G$36,0,MATCH($J$3,$B$1:$G$1,0)),ROWS(J$22:J22)))
K22:K31	K22	=IF(J22="","",INDEX($B$2:$G$36,MATCH(I22,$A$2:$A$36,0),MATCH($J$6,$B$1:$G$1,0)))
L22:L31	L22	=IF(K22<LARGE($K$22:$K$31,5),"",IF(K22="","",COUNTIFS($J$22:$J$31,">"&J22)+COUNTIFS($J$22:$J$31,J22,$K$22:$K$31,">"&K22)+COUNTIFS($J$22:$J22,J22,$K$22:$K22,K22)))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet13!$A$1:$G$36	I22:I31

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J9:O18	Expression	=AND(J$8=$J$6,J9<>"")	text	NO
J9:O18	Expression	=AND(J$8=$J$3,J9<>"")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
J3,J6	List	=$B$1:$G$1