Creating a conditional ranking list

[droot]

Hi,

I'm trying to create a top 10 ranking list and would like to add a condition that regardless of the rank/performance score, the highest performers in a key country will always be included in the final list.

The mini sheet below contains two tables. The left table is the main table and the table on the right contains key countries and the minimum number of entries to be included in the final list (Col E).

Is this possible to do in Excel?

Book2
	A	B	C	D	E	F	G	H
1	Country	Name	Performance	Rank	Final List		Country	Min to Include
2	UK	John	100	1	Yes		UK	1
3	France	Boris	98	2	Yes		USA	2
4	France	Michael	88	3	Yes		UAE	1
5	Canada	Sara	86	4	Yes
6	Mexico	Maria	78	5	Yes
7	USA	Sarah	75	6	Yes
8	Germany	Peter	75	6	Yes
9	UAE	Rachael	53	10	Yes
10	UK	Mike	50	11	Yes
11	USA	Amy	34	13	Yes
12	Canada	Paul	64	8	No
13	Germany	Klaudia	64	8	No
14	Mexico	Juan	35	12	No
15	UK	Joe	33	14	No
Sheet1

Cell Formulas
Range	Formula
D2:D15	D2	=RANK(C2,$C$2:$C$15,0)

 

[droot]

Sorry, can't seem to edit my first post, but I forgot to mention that I'm looking for either a Yes / No column like Column E or a functioning rank column. Ultimately, I just need to be able to identify 10 top performers, with at least 4 of them that belong to a Country in Column G (hope this makes sense).