Índice + corresp multicritério com intervalo de datas e SE


#1

Olá

Preciso puxar valores pagos por planos de acordo com a vigência. Esses valores pagos podem mudar em intervalos de datas diferentes.
Fiz um teste onde existem apenas 3 exemplos, mas serão mais de 10 planos com os valores em vigências diferentes que podem mudar e ficar com valores diferentes em outro intervalo de tempo.

Para puxar o Valor por plano usei Índice + corresp
=ÍNDICE(Tabela6[[#Tudo];[Valor Pago]];CORRESP(G7&I7;Tabela6[[#Tudo];[Plano]]&Tabela6[[#Tudo];[Código da Vigência]];0))

Criei código de vigências para unificar o intervalo das datas utilizando a fórmula SE
=SE(E(H7>=$C$7;H7<=$D$7);$B$7;SE(E(H7>=$C$8;H7<=$D$8);$B$8;SE(E(H7>=$C$9;H7<=$D$9);$B$9))),
mas conforme os valores e as vigências forem mudando, isso ficará enorme e pouco inteligente. Preciso de uma forma para automatizar isso e tornar mais prático.

Exemplo (A, B, C, D, E, G, H, I e J são as colunas relacionadas)

A…B…C …D… E
|Plano|…Código da Vigência|…Início Vigência…|Fim Vigência|…Valor Pago|
|Golden|…V0001|…12/10/2017|…30/06/2018|…R$ 100,00|
|Golden|…V0002|…01/07/2018|…31/12/2018|…R$ 110,00|
|Assim|…V0003|…01/01/2019|…30/06/2019|…R$ 120,00|

G… H…I…J
|Plano |…Data|…Código da Vigência|…Valor Pago|
|Golden|…30/10/2017|…V0001|…R$ 100,00|
|Golden|…07/08/2018|…V0002|…R$ 110,00|
|Assim|…05/05/2019|…V0003|…R$ 120,00|
|Golden|…29/06/2018|…V0001|…R$ 100,00|
|Assim|…18/01/2019|…V0003|…R$ 120,00|


#2

Olá @CMout.
Fiz uma rotina que pode te ajudar usando as colunas que vc indicou.
Ela atualiza a coluna H e J de acordo com o que estiver na coluna A até E. Coloquei até 1000 linhas, mas vc pode colocar o numero que for necessário.

Qualquer coisa é só falar.
abs

cmouth.xlsm (21,9 KB)


#3

Tinha conseguido com a fórmula
=SOMARPRODUTO(([Data]>=Tabela63[Início Vigência])([Data]<=Tabela63[Fim Vigência])(Tabela63[Valor Pago])([Plano]=Tabela63[Plano])(Tabela63[Coluna1]=[Coluna1]))

Deu certo, mas como a sua ideia funcionou bem também, vou usá-la em outra aplicação com o mesmo conceito.

Obrigada @lporto, ajudou bastante!