I have an excel spreadsheet that has 3 Columns: 1) City 2) State 3) Population all on sheet 1. On Sheet 2 cell B1 I want to be able to select the state and display all cities within that state & their population. I would like to have a formula that accomplishes this and it needs to be dynamic as items get added to the spreadsheet. I do not want to use a filter or advanced filter. I have a formula but it is pulling back a list of all cities regardless of the state they are in. I would also like to remove the #REF from showing up on Sheet 2.
Sheet 1 Below:
<tbody>
</tbody>
SHEET 2 RESULTS BELOW:
<tbody>
</tbody>
Formula:
Sheet 1 Below:
City | State | Population |
Orlando | FL | 100 |
Winter Park | FL | 10 |
Winter Garden | FL | 5 |
Melbourne | FL | 3 |
Cocoa | FL | 2 |
Cocoa Beach | FL | 4 |
Birmingham | AL | 7 |
Las Vegas | NV | 8 |
<tbody>
</tbody>
SHEET 2 RESULTS BELOW:
State Name | FL |
List of Cities | Population |
Orlando | |
Winter Park | |
Winter Garden | |
Melbourne | |
Cocoa | |
Cocoa Beach | |
Birmingham | |
Las Vegas | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! | |
#REF! |
<tbody>
</tbody>
Formula:
Code:
=INDEX(Sheet1!$A$2:$A$9,SMALL(IF(Sheet1!$B$2:$B$9=Cities!$B$1,ROW(Sheet1!A2:A2)-ROW(Sheet1!$A$2)+1),ROW($A$3:A3)))