How to extract only first 2 items, from different groups in the same data, meeting certain criteria

[Navtir]

I have a table as below. The goal is to extract only first two shop i.d.s from each country having the status either "Active" or "Completed" or "Discontinued". If there are no shops in the country with that status do not extract. If only one than extract only one. Finally the answer should be in a table showing all the responses with above mentioned criteria.
I tried using AGGREGATE with formula 15 and option 6. in the [K] argument i use MOD. but the problem is it does not stop after 2 extractions. if the country has more shops with the above mentioned status it start again and repeat till all the shops in that country are analyzed. My formula looks like this

INDEX(Sheet1!$B$2:$B$265,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$265)-ROW(Sheet1!$A$2)+1)/((Sheet1!$A$2:$A$265=Sheet1!A2)*(Sheet1!$C$2:$C$265<>"SF")),ROUND((MOD(ROW(),2)+0.5),0)))

Country Code	Shop ID	Status
4101	4101001	SF
4101	4101002	Discontinued
4101	4101003	Active
4101	4101004	Active
4102	4102001	Completed
4102	4102002	Completed
4102	4102003	Completed
4102	4102004	Completed
4102	4102005	Completed
4102	4102006	Active
4102	4102007	Discontinued
4102	4102008	Active
4102	4102009	Active
4102	4102010	Active
4102	4102011	Scr
4103	4103001	Completed
4103	4103002	SF
4103	4103003	SF
4103	4103004	Completed
4103	4103005	Completed
4103	4103006	SF
4103	4103007	Active
1201	1201001	Completed
1201	1201002	Completed
1201	1201003	Active
1201	1201004	SF
1201	1201005	Active
4001	4001001	Completed
4001	4001002	Completed
4001	4001003	Completed
4001	4001004	Active
4001	4001005	Active
4004	4004001	Completed
4004	4004002	Completed
4004	4004003	Discontinued
4004	4004004	SF
4004	4004005	Completed
4004	4004006	SF
4004	4004007	SF
4004	4004008	SF
4004	4004009	Discontinued
8001	8001001	Completed
8001	8001002	SF
8001	8001003	SF
8001	8001004	Active
2301	2301001	SF
2301	2301002	SF
2303	2303001	SF
2303	2303002
2303	2303003	Screened

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe something like this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Country Code​	Shop ID​	Status​		Status​		Country Code​	Shop ID​
2​	4101​	4101001​	SF​		Active​		4101​	4101002​
3​	4101​	4101002​	Discontinued​		Completed​		4101​	4101003​
4​	4101​	4101003​	Active​		Discontinued​		4102​	4102001​
5​	4101​	4101004​	Active​				4102​	4102002​
6​	4102​	4102001​	Completed​				4103​	4103001​
7​	4102​	4102002​	Completed​				4103​	4103004​
8​	4102​	4102003​	Completed​				1201​	1201001​
9​	4102​	4102004​	Completed​				1201​	1201002​
10​	4102​	4102005​	Completed​				4001​	4001001​
11​	4102​	4102006​	Active​				4001​	4001002​
12​	4102​	4102007​	Discontinued​				4004​	4004001​
13​	4102​	4102008​	Active​				4004​	4004002​
14​	4102​	4102009​	Active​				8001​	8001001​
15​	4102​	4102010​	Active​				8001​	8001004​
16​	4102​	4102011​	Scr​
17​	4103​	4103001​	Completed​
18​	4103​	4103002​	SF​
19​	4103​	4103003​	SF​
20​	4103​	4103004​	Completed​
21​	4103​	4103005​	Completed​
22​	4103​	4103006​	SF​
23​	4103​	4103007​	Active​
24​	1201​	1201001​	Completed​
25​	1201​	1201002​	Completed​
26​	1201​	1201003​	Active​
27​	1201​	1201004​	SF​
28​	1201​	1201005​	Active​
29​	4001​	4001001​	Completed​
30​	4001​	4001002​	Completed​
31​	4001​	4001003​	Completed​
32​	4001​	4001004​	Active​
33​	4001​	4001005​	Active​
34​	4004​	4004001​	Completed​
35​	4004​	4004002​	Completed​
36​	4004​	4004003​	Discontinued​
37​	4004​	4004004​	SF​
38​	4004​	4004005​	Completed​
39​	4004​	4004006​	SF​
40​	4004​	4004007​	SF​
41​	4004​	4004008​	SF​
42​	4004​	4004009​	Discontinued​
43​	8001​	8001001​	Completed​
44​	8001​	8001002​	SF​
45​	8001​	8001003​	SF​
46​	8001​	8001004​	Active​
47​	2301​	2301001​	SF​
48​	2301​	2301002​	SF​
49​	2303​	2303001​	SF​
50​	2303​	2303002​
51​	2303​	2303003​	Screened​

Formula in G2 copied down
=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(COUNTIF(G$1:G1,A$2:A$100)<2)),1)),"")

Formula in H2 copied down
=IF(G2="","",INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(A$2:A$100=G2)),COUNTIF(G$2:G2,G2))))

M.

 

[DanteAmor]

Great contribution from Marcelo, without a doubt they are the formulas you were looking for.


However, I wanted to show you another approach with a pivot table. It is easy to build, you just have to drag the fields.

In the status field you can filter the necessary ones.

In the shop ID field, you can filter the items you need.