Find matches in two spreadsheets and result is the match or count.

[pbundrant]

I've been researching my question all over the place. I have two sheets. One has a list of folks who stated their state or city and/or both. I am trying to match the list I have in another sheet with states, territories and cities with whatever matches and get a count.

In one sheet(1):

1 Kentucky
2 New York
3 Oklahoma

In another sheet(2): 14 Delaware 15 District of Columbia 16 Florida 17 Georgia 18 Guam 19 Hawaii 20 Idaho 21 Illinois 22 Indiana 23 Iowa <tbody> </tbody>

<tbody>
</tbody>

I want a total of how many states match anywhere in the line of sheet one, either by full state name or abbreviations, including territories, to sheet two and return either the count or the value that matches. I've tried so many formulas and none work. Please help.

 

[Aladin Akyurek]

Your exhibits do not show the variation your description implies. Recall that not everyone is familiar with the US geography and the US naming conventions.

 

[pbundrant]

Couldn't leave that there.

 

[pbundrant]

I want to pull the text that matches the list on the States sheet onto a column in order to get a count from states and cities and territories, if any.

In separate columns of course.