Guru do Excel e Power BI

Prezados, bom dia.

Estou com um problema em uma fórmula e não acho solução na internet em nenhum canto. Estou usando a função Desloc e coresp para funcionarem como um procv. O problema é que eu estou identificando um erro, que voces poderão ver nas imagens abaixo:



A fórmula na barra é correspondente à célula J6. O problema é que este resultado deveria estar aparecendo em J2.

eu sei consertar o erro e solucionar por outros meios. o que busco é uma forma de, com essa forma arrumar isso. Poderiam me ajudar?

----
Formula Explicada:

=OFFSET(INDIRECT("F"&MATCH($B$20;F:F;0));0;1;COUNTIF(F:F;$B$20);1)

COUNTIF
COUNTIF(F:F;$B$20) = No caso, conto quantos valores são "Minas Gerais". (15 no total)

MATCH
MATCH($B$20;F:F;0) = Identifico na tabela aonde está o primeiro valor "Minas Gerais". ( 6 )

INDIRECT
INDIRECT("F"&6) = Utilizo o resultado da formula match para transformar isso em uma célula juntando com a letra F. Assim a formula me retorna que o primeiro valor "Minas Geais" está em F6 e, pela função de conta, sei que temos 15 "Minas Gerais".

OFFSET
OFFSET(F6;0;1;15;1) = Me retorna o valor em G que está ao lado de "Minas Gerais".

A formula está funcionando, mas como fazer para não ficar com os espaços em branco no inicio da coluna J?

Desde já agradeço a ajuda de todos.

Amigo, anexa um modelo da sua planilha em Excel que fica mais fácil de ajudar, para anexar, vá em + resposta, adicionar anexo.

Abs

Boa tarde.
Por que não usar esta outra fórmula?
Fórmula matricial. Finalize com ctrl+shift+enter.

Good luck!

FelipeMGarcia escreveu:Amigo, anexa um modelo da sua planilha em Excel que fica mais fácil de ajudar, para anexar, vá em + resposta, adicionar anexo.

Abs

Amigo, estou anexando a planilha.

FelipeMGarcia escreveu:=OFFSET(INDIRECT("F"&MATCH($B$20;F:F;0));0;1;COUNTIF(F:F;$B$20);1)

Felipe, muito obrigado pela solução. Eu usei essa fórmula para resolver esse problema. O motivo da criação desse tópico é para entender o porquê de a minha primeira fórmula não estar funcionando, para fins de estudo.

Joca, a solução não foi minha, mas segue uma opção para ver se te atende:

=SE(LINS($1:1)>SOMA(SE(FREQÜÊNCIA(SE($F$2:$F$43=$B$19;CORRESP($F$2:$F$43&$G$2:$G$43;$F$2:$F$43&$G$2:$G$43;0));LIN($G$2:$G$43)-LIN($G$2)+1);1));"";ÍNDICE($G$2:$G$43;AGREGAR(15;6;(LIN($G$2:$G$43)-LIN($G$2)+1)/($F$2:$F$43=$B$19);LINS($1:1))))

Não esqueça que é matricial, coloque na célula J1 e arraste para baixo depois de apertar CTRL+SHIFT+ENTER

Joca,

Segue o arquivo.