Find city name in file, return state name. Using two files

[lbolin]

I have a list of Citys with not state. I am needing to use the external file provided to find the city and populate the state. I have tried using Find and Replace but is didn't work. 

City	State
MILWAUKEE
KENT
LAKE FOREST
NEW BERLIN
SEATTLE
OAK CREEK
SHOREWOOD
WATERTOWN
WESTBEND

[BrandonB]

How do you plan on dealing with scenarios where the same city name exists in multiple states?

[lbolin]

I would say leave it blank. I am not looking for 100% coverage but any I can get is a plus. 

[SPetrie · Accepted solution]

Did you use "Case insensitive find" with your find/replace?

Another thing to consider is that find/replace will only update with the last found value, so even if a city matches multiple entries, it will only output the last match. Join would show you all the matches, you just need to convert the state/zip file to caps first.

Also, you have Westbend in your sample set, but in the file its two words, so it would not match either.

[lbolin]

What did you use to join on??

[SPetrie]

The City column from your sample list of 9 city names, and the city column of the .csv you provided. Before joining, I used trim and uppercase to match to the uppercase names from your list.