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
[TABLE="width: 640"]
<tbody>[TR]
[TD]In another sheet(2):

[TABLE="width: 249"]
<tbody>[TR]
[TD="align: right"]14[/TD]
[TD] Delaware[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD] District of Columbia[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Georgia[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD] Guam[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD] Hawaii[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Idaho[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD]Illinois[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD]Indiana[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD]Iowa[/TD]
[/TR]
</tbody>[/TABLE]


[/TD]
[/TR]
</tbody>[/TABLE]
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.