Multiple Criteria IndexMatch/ VLOOKUP with partial match

[sberhalter]

I have a file with all the state and cities that I need. I want to match the population of the US census dataset to the cities in my list but the cities are not in the same format. This task would be easy if there weren't multiple cities that show up in different state.

What I want the formula to do is to return the population of a city that partially matches the US census data only if the states match first. Basically I want to find match the partial name in column B to the list in column D and return the population from column E only if the states in columns A and C match. Otherwise I will have to do all of this manually.

Please help.

Texas	Abilene	Arkansas	Portland city	430
Ohio	Akron	Arkansas	Portland city	430
Georgia	Albany	Connecticut	Portland town	9,508
New York	Albany	Illinois	Portland township	422
Oregon	Albany	Indiana	Portland city	6,223
New Mexico	Albuquerque	Indiana	Portland city	6,223
Louisiana	Alexandria	Indiana	Portland city	6,223
Pennsylvania	Altoona	Maine	Portland city	66,194

<tbody>
</tbody>

 

[Aladin Akyurek]

What represents this sample exactly?