Pode tentar procv.

.
Não ficou igualzinho o que pediu, mas funcionou ... estou de saída, daqui a pouco alguém passa aqui e melhora ...
.

Solução alcançada!

Vou testar mais a noite, mas pelo que vi ficou exaamente como eu precisava. Obrigado

DJunqueira, estou com alguns problemas ao colocar suas funções na planilha "real", estou fazendo algo errado, pois no seu exemplo está correto.
Estou anexando a planilha na qual estou mexendo, poderia dar uma olhada? Obrigado.

Não uso o Winrar, por favor envie o arquivo zipado, para tanto clique com o botão direito do mouse no arquivo selecione Enviar para / Pasta compactada.
O Windows compacta com o algoritmo zip nativamente já faz várias versões, mas infelizmente muitos desconhecem essa facilidade.

Segue o arquivo. Obrigado

Corrigi uma das fórmulas, mais tarde vejo a outra.

Pelo que vi a outra , o erro é devido a duas ocorrências com o mesmo total( 4 ocorrências por exemplo. Mas não consegui acertar).

Solucionado a parte do cabeçalho, portanto solução completa.

Como recebo a planilha com diferentes números de dados frequentemente, preciso ficar alterando a fórmula toda vez que isso acontece, seria possível alterar a fórmula, de forma que deixasse o intervalo até um número bem maior, e não fosse necessário alterar todas as vezes os intervalos? Estou anexando novamente, caso seja preciso crio uma nova pergunta. Obrigado.

Usei intervalo nomeado e DESLOC(), agora a fórmula irá se adaptar ao número de códigos e cidades q vc inserir nas colunas A e B.