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.

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


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...

[TABLE="width: 385"]
<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>[TR]
[TD="class: xl65, width: 94, bgcolor: transparent"]OH[/TD]
[TD="class: xl65, width: 213, bgcolor: transparent"]PA[/TD]
[TD="class: xl65, width: 206, bgcolor: transparent"]SC[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Akron[/TD]
[TD="class: xl65, bgcolor: transparent"]Allentown-Bethlehem-Easton[/TD]
[TD="class: xl65, bgcolor: transparent"]Augusta-Richmond County[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Ashland[/TD]
[TD="class: xl65, bgcolor: transparent"]Altoona[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Ashtabula[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Athens[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


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.