CODEWITHSUNDEEP
excelsearch
C. Harr
C. Harr

Reputation: 13

excel Search and list

i am having an issue with Excel. What I am trying to do is have a user input an area and it list the cities in that area with a ; inbetween each city. Basically the data only has 2 columns. So you can think of it as Metro Area|Cities for the columns. so each city in that metro area list it.

eg. metro|City LA|West Covina LA|Glendale LA|Irwindale NY|Queens NY|Brooklyn

I would like to have another sheet where a user inputs a Metro area and in the cities go in the cell next to it seperated with ;. so for NY it would bring up Queens;Brooklyn.

Can someone provide help for this.

Upvotes: 1

Views: 41

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Say our data in Sheet1 is like:

enter image description here

In cell C2 enter:

=IF(A2=Sheet2!$A$1,Sheet1!B2,"")

and copy down. In Sheet2 cell A1, the user enters the metro area and in Sheet2 cell B1 we enter the formula:

=TEXTJOIN(";",TRUE,Sheet1!C:C)

enter image description here

Upvotes: 1

Related Questions