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:[TABLE="width: 238"]
<tbody>[TR]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]Population
[/TD]
[/TR]
[TR]
[TD]Orlando
[/TD]
[TD]FL
[/TD]
[TD="align: right"]100
[/TD]
[/TR]
[TR]
[TD]Winter Park
[/TD]
[TD]FL
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]Winter Garden
[/TD]
[TD]FL
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]Melbourne
[/TD]
[TD]FL
[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Cocoa
[/TD]
[TD]FL
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Cocoa Beach
[/TD]
[TD]FL
[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]Birmingham
[/TD]
[TD]AL
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]Las Vegas
[/TD]
[TD]NV
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2 RESULTS BELOW:[TABLE="width: 205"]
<tbody>[TR]
[TD]State Name
[/TD]
[TD]FL
[/TD]
[/TR]
[TR]
[TD]List of Cities
[/TD]
[TD]Population
[/TD]
[/TR]
[TR]
[TD]Orlando
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winter Park
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winter Garden
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Melbourne
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cocoa
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cocoa Beach
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Las Vegas
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF!
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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