Returning state cities by inputing a given state's abbrevation

[siaob66]

Hi,

I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep, if that is helpful.

Thanks

 

[Skybluekid]

One option would be in J2, put the formula below into Data Validation List

=OFFSET(A1,MATCH(K2,A2:A5000,0),1,COUNTIF(A2:A5000,K2),1)

This will list all the cities in a given state, in a drop down menu.

 

[country101]

You could use the name box to name all the cities from a state and do a referance of some kind. Say all the cities in alaska, highlight them and name them AK then do a lookup or something. I'm not exactly sure how to do this, but that could be a start for you.

 

[Skybluekid]

Here is another option:

First you need to count the number of cities per state
In K5 =COUNTIF(H2:H5000,K2)
In J2 =IF(ROWS(J$2:J2)>$K$5,"",INDEX(Cities,SMALL(IF($H$2:$H$500=$K$2,ROW($H$2:$H$5000)-ROW($H$2)+1),ROWS(J$2:J2))))
and the Ctrl+Shift+Enter( This produces an Array formula)

Copy drag down below the maximum number of cities you have for any one state

 

[T. Valko]

Hi,

I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep, if that is helpful.

Thanks

Here's how the formula should be written using range addresses:

=IF(ROWS(J$2:J2)>K$5,"",INDEX(I:I,SMALL(IF(A$2:A$5000=K$2,ROW(A$2:A$5000)),ROWS(J$2:J2))))

If you want to use named ranges then:

Define Cities to refer to the entire column I.

Define Item to refer to the actual specific range like $A$2:$A$500 (or whatever).

Also the formula needs to be array entered.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[siaob66]

Thanks for the replies, I tried it and it didn't work, so I then tried it on another pc and it did. Unfortunately, the one it must work on it doesn't.

The only difference between them is the delimiter. I use a pipe. Could that be the problem. SMALL is darkened after I ctrl+shift+enter.

 

[siaob66]

I copied the worksheet to the other pc and it's working. Thanks so much for help. This saves me a lot of time.

 

[T. Valko]

I copied the worksheet to the other pc and it's working. Thanks so much for help. This saves me a lot of time.

You're welcome. We appreciate the feedback!