Generate List from Index Match that searches for a value in a field

[Sarol]

Good Afternoon Everyone,

I was given a spreadsheet at work that contains a list of counties and the states they are in. What I am needing to do is generate a list of counties in a given state. The catch to this is that there are some counties in multiple states. In my spreadsheet I created a drop down list for the states. Upon selecting the state I have the State Abbreviation in another field. I also count the number of expected entries to be returned from the data set. I am able to return a list of counties in the selected state, however, it does not return the values of counties in multiple states. It seems I am not able to post my sample spreadsheet, but here is an example of the data.

CountyName	CountyLocation
Aberdeen	SD
Aberdeen	WA
Abilene	TX
Ada	OK
Adrian	MI
Akron	OH
Alamogordo	NM
Albany	GA
Albany	OR
Albany-Schenectady-Troy	NY
Albemarle	NC
Albert Lea	MN
Albertville	AL
Albuquerque	NM
Alexandria	LA
Alexandria	MN
Alice	TX
Allentown-Bethlehem-Easton	PA-NJ
Alma	MI
Alpena	MI
Altoona	PA
Altus	OK
Amarillo	TX
Americus	GA
Ames	IA
Amsterdam	NY
Anchorage	AK
Andrews	TX
Angola	IN
Ann Arbor	MI
Anniston-Oxford-Jacksonville	AL
Appleton	WI
Arcadia	FL
Ardmore	OK
Arkadelphia	AR
Arkansas City-Winfield	KS
Asheville	NC
Ashland	OH
Ashtabula	OH
Astoria	OR
Atchison	KS
Athens	OH
Athens	TN
Athens	TX
Athens-Clarke County	GA
Atlanta-Sandy Springs-Roswell	GA
Atlantic City-Hammonton	NJ
Auburn	IN
Auburn	NY
Auburn-Opelika	AL
Augusta-Richmond County	GA-SC

<colgroup><col><col></colgroup><tbody>
</tbody>

As you can see for Allentown-Bethlehem-Easton they reside in the states of PA-NJ, and Augusta-Richmond County is in GA-SC. Here are a few formulas I am using.

My Drop down list for my states is located in E1, the State Abreviation is located in E2, and my formula for counting the number of expected entries is in E3. The formula for counting the expected number of entries is:

=COUNTIF(CountyLocation,"*"&StateAbbrev&"*")

CountyLocation is Column B of my data set, and StateAbbrev is a reference to E2.

The formula for generating a list of returned values (starting in cell H1) is as follows:

=IF(ROWS(H$1:H1)<=$E$5,INDEX(CountyName,SMALL(IF(CountyLocation=$E$2,ROW(CountyName)-ROW($A$2)+1),ROWS(H$1:H1))),"")

CountyName is the name of the county from Column A, CountyLocation is Column B.


If anyone can offer suggestions on how to get my INDEX function to match the state values in Column B it would be greatly appreciated.

Also, if someone could tell me how to get permission to upload a file I would be happy to share my sample spreadsheet.

Thanks in advance.

 

[Aladin Akyurek]

Care to state concisely what are the results you want to see?

 

[Sarol]

I would like to see a list of Counties (Column A) that are located in the state selected. So for the data set provided if the user selects the state of Ohio (OH) the following list would generate:
Akron
Ashland
Ashtabulat
Athens

If the user selects Pennsylvania (PA) then the results should be:
Allentwon-Bethlehem-Easton
Altoona

And if the user selects South Carolina (SC) or Georgia (GA) then the following would appear
Augusta-Richmond County

The issue is with the counties in multiple states where the states are listed as PA-NJ, GA-SC, or DC-VA-MD-WV as examples.

 

[Aladin Akyurek]

I would like to see a list of Counties (Column A) that are located in the state selected. So for the data set provided if the user selects the state of Ohio (OH) the following list would generate:
Akron
Ashland
Ashtabulat
Athens

If the user selects Pennsylvania (PA) then the results should be:
Allentwon-Bethlehem-Easton
Altoona

And if the user selects South Carolina (SC) or Georgia (GA) then the following would appear
Augusta-Richmond County

The issue is with the counties in multiple states where the states are listed as PA-NJ, GA-SC, or DC-VA-MD-WV as examples.

Let A:B house the data (not shown), E:G the processing...

OH	PA	SC
Akron	Allentown-Bethlehem-Easton	Augusta-Richmond County
Ashland	Altoona
Ashtabula
Athens

<COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3328" width=94><COL style="WIDTH: 160pt; mso-width-source: userset; mso-width-alt: 7566" width=213><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 7338" width=206><TBODY>
</TBODY>

E2, control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$52,
  SMALL(IF(ISNUMBER(SEARCH("-"&E$1&"-","-"&$B$2:$B$52&"-")),
  ROW($A$2:$A$52)-ROW($A$2)+1),ROWS(E$2:E2))),"")

 

[Sarol]

Thank you Aladin!!

 

[Aladin Akyurek]

Thank you Aladin!!

You are welcome.