Boa noite!!

Eu fiz de uma forma não muito interessante, pois falta uns pequenos ajuste, cujo os mesmo eu deixei para você fazer!!

Att

bom dia Carlos

A tua fórmula não estava funcionando porque na tabela você estava colocando o zero no final da tabela e tem que colocar no início, antes do número 1.

Baixe a planilha e veja como funciona perfeitamente:

Dê retorno.

Um abraço.

Contribuindo:
Outra opção é complementar a função PROCV, com o Termo: procurar_intervalo = Falso, assim será procurado o valor correto sem necessidade da coluna pesquisada estar em ordem:
Do help:

procurar_intervalo Opcional. Um valor lógico que especifica se você quer que PROCV localize uma correspondência exata ou aproximada.
Se procurar_intervalo for VERDADEIRO, ou for omitido, uma correspondência exata ou aproximada será retornada. Se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado.
Importante Se procurar_intervalo for VERDADEIRO, ou for omitido, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente; caso contrário, PROCV poderá não retornar o valor correto.
Se o argumento procurar_intervalo for FALSO, PROCV encontrará somente uma correspondência exata. Se houver dois ou mais valores na primeira coluna de matriz_tabela que não coincidem com o valor_procurado, o primeiro valor encontrado será utilizado. Se nenhuma correspondência exata for localizada, o valor de erro #N/D será retornado

Ficara assim: SE(F4="PR";PROCV(DIREITA(E4;2);Referencia!$A$4:$B$103;2;FALSO);SE(F4="SE";PROCV(DIREITA(E4;1);Referencia!$D$4:$E$13;2;FALSO);"Estado não Cadastrado"))

que coisa, vacilo meu,hehehhehe, esqueci que os dados devem estar ordenados. Obrigado pela ajuda alexandrevba , Patropi e Reinaldo, Voces são TOP. VLW