Excel Dynamic Formula to List all Cities in a State

[yagu99]

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:

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:

 =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)))

 

[Weazel]

maybe a few modifications to what you already have...

=IFERROR(INDEX(Sheet1!$A$2:$A$9,SMALL(IF(Sheet1!$B$2:$B$9=$B$1,ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3))),"")

 

[mikerickson]

Select Sheet2!A1:A100 and enter this array formula.

=INDEX(Sheet1!$A:A,SMALL(IF((Sheet1!$B$1:$B$100=Sheet2!$B$1),ROW(Sheet1!$B$1:$B$100),9999),ROW(1:100)),1)&""

(entered with Ctrl-Shift-Enter (Cmd+Return for Mac))

The range Sheet1!B1:B100 (and 1:100 and the result range) should be expanded until it is longer than your longest possible list.

 

[yagu99]

Thanks for your responses, both formulas provide the City names, however, it still doesn't provide the population data for each city.

 

[Weazel]

if you copy the formula but change the index column to C you should get the population


=IFERROR(INDEX(Sheet1!$C$2:$C$9,SMALL(IF(Sheet1!$B$2:$B$9=$B$1,ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1),ROWS($A$3:B3))),"")

 

[yagu99]

Thanks Weazel