buscar o valor na matriz, se existir, retornar 1a coluna da respectiva linha

estats · 11 de agosto de 2013

Boa tarde senhores. Eis o desafio que anda me tirando o sono:

Na planilha 1, tenho a seguinte tabela com valores de códigos.

(Os códigos não se repetem dentro do mesmo local)

local cod valor

120 10 1

120 11 2

120 12 2

120 13 4

121 11 1

121 12 2

121 13 1

132 10 1

132 11 3

132 11 1

132 12 1

382 11 4

382 12 1

382 13 4

382 15 1

Na planilha 2, tenho a seguinte tabela: (mostra que locais pertencem a qual departamento)

depto local1 local2 local3 local4 localn

1040 120 121 122 123 124

1066 129 130 131

1090 142 296

Preciso inserir uma coluna em plan1 que me diga a que depto pertence cada local. Em suma: A função deve buscar o valor do local (plan1, coluna A) na plan2. Se existir,deve retornar o respectivo depto (plan2, col A).

Tentei índice, corresp, procv, proch... mas entreguei os pontos.

Será que algum fera das planilhas poderá me salvar ?

Obrigado!

vinibpeixoto · 12 de agosto de 2013

Olá, estats,

Uma fórmula matricial resolveria o seu problema.

Leia o tópico http://forum.clubedohardware.com.br/formula-procv/1145723 para compreender o comportamento da fórmula aplicada ao seu problema.

---

Antes de iniciar, você deve ter, na Plan2, o número das linhas dos departamentos. De acordo com o seu exemplo, você poderia adicionar a seguinte fórmula a partir da célula G2 da Plan2:

=LIN(A2)

A fórmula acima simplesmente retornaria o número da linha da célula referenciada. Os valores retornados seriam utilizados na fórmula posterior.

Na Plan1, bastaria adicionar a seguinte fórmula, a partir da célula D2:

{=ÍNDICE(Plan2!$A$2:$A$4;CORRESP(VERDADEIRO;CONT.SE(INDIRETO("Plan2!B"&Plan2!$G$2:$G$4&":F"&Plan2!$G$2:$G$4);A2)>0;0))}

Lembrando, mais uma vez, que a fórmula é matricial. Portanto, deve-se pressionar CTRL + SHIFT + ENTER, ao invés de um simples ENTER, ao finalizar a edição da fórmula.

Observações:

- A fórmula acima leva em consideração apenas 3 distintos departamentos, motivo pelo qual os intervalos sempre estão entre as linhas 2 e 4 ($A$2:$A$4 e $G$2:$G$4);

- O intervalo em que a fórmula verifica os locais de um determinado departamento é entre as colunas B e F da Plan2, através da função INDIRETO (INDIRETO("Plan2!B"&Plan2!$G$2:$G$4&":F"&Plan2!$G$2:$G$4)).

Fiz o upload de um exemplo em http://www.sendspace.com/file/nuujct.