CODEWITHSUNDEEP
excelexcel-formula
Noctis
Noctis

Reputation: 127

Creating a sub-list depending on criteria

I have a 2-column table which contains Country and Number of people. I would like to create a sub-list from this consisting only of countries who have over 100 people. For example, my current table is:

Country    Number of people
Australia        600
Bulgaria         90
Canada           200
Germany          200
Spain            70
India            190
UK               900
US               20

I need a formula that would produce a new list from this table, showing only countries that have only 100 people in the second column. I would then use an INDEX-MATCH to bring across the numbers. The second table would look like:

Country    Number of people
Australia        600
Canada           200
Germany          200
India            190
UK               900

Again, I need a formula that would look at the first table and produce the first column of the second table, with the key piece of information being countries that have over 100 people.

My actual data set contains 102 rows. The table produced has less rows, but I hope for it to have the capability to increase or decrease in rows automatically as the initial data set is updated.

Upvotes: 0

Views: 3157

Answers (1)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

=INDEX(Sheet1!A$2:A$9,SMALL(IF(Sheet1!$B$2:$B$9>100,ROW(Sheet1!$B$2:$B$9)-1),Row(1:1)))

This is an array formula - confirmed by pressing Ctrl+Shift+Enter while still in the formula bar

Essentially you build the array of rows that you want to return using the if statement in the middle, then use small to grab the nth smallest row so as ROW(1:1) updates it'll obtain the next match and so on.

Upvotes: 1

Related Questions