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

#### Marcelo Branco

##### MrExcel MVP
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

##### Well-known Member
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.

1,085,429
Messages
5,383,624
Members
401,843
Latest member
stevensmith1