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

[markslaysman]

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.

At the beginning of the data set if you look 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.

Thanks in advance.



CountyStateExampleData.xlsx

[benishiryo]

welcome to the forum, markslayman. you can use this array formula:

Please Login or Register  to view this content.

to make it slightly faster, try deducting the ROW in the non array area:

Please Login or Register  to view this content.

[markslaysman]

Thank you very much benishiryo!