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.

[TABLE="width: 531"]
<tbody>[TR]
[TD]Texas[/TD]
[TD]Abilene[/TD]
[TD]Arkansas[/TD]
[TD]Portland city[/TD]
[TD] 430[/TD]
[/TR]
[TR]
[TD]Ohio[/TD]
[TD]Akron[/TD]
[TD]Arkansas[/TD]
[TD]Portland city[/TD]
[TD] 430[/TD]
[/TR]
[TR]
[TD]Georgia[/TD]
[TD]Albany[/TD]
[TD]Connecticut[/TD]
[TD]Portland town[/TD]
[TD] 9,508[/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]Albany[/TD]
[TD]Illinois[/TD]
[TD]Portland township[/TD]
[TD] 422[/TD]
[/TR]
[TR]
[TD]Oregon[/TD]
[TD]Albany[/TD]
[TD]Indiana[/TD]
[TD]Portland city[/TD]
[TD] 6,223[/TD]
[/TR]
[TR]
[TD]New Mexico[/TD]
[TD]Albuquerque[/TD]
[TD]Indiana[/TD]
[TD]Portland city[/TD]
[TD] 6,223[/TD]
[/TR]
[TR]
[TD]Louisiana[/TD]
[TD]Alexandria[/TD]
[TD]Indiana[/TD]
[TD]Portland city[/TD]
[TD] 6,223[/TD]
[/TR]
[TR]
[TD]Pennsylvania[/TD]
[TD]Altoona[/TD]
[TD]Maine[/TD]
[TD]Portland city[/TD]
[TD] 66,194[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What represents this sample exactly?