Encontre o valor correspondente em uma linha e retorne o nome da coluna II

Question

Meu qeustion é semelhante, mas diferente de Encontrar o valor correspondente em uma linha e retornar o nome da coluna?

Eu tenho uma planilha com duas guias:

1. A guia A tem uma coluna, que é uma lista de endereços de e-mail (há muitas outras colunas nessa guia)
2. O separador B tem 4 colunas, cada uma com endereços de email de diferentes grupos. Cada coluna possui endereços de e-mail exclusivos (sem dupes entre colunas). Então, o Grupo 1 tem 50 e-mails na Coluna A e o Grupo 2 tem 100 e-mails na Coluna B, etc.

Eu gostaria de inserir uma fórmula em uma nova coluna na Aba A, que olha para os e-mails em cada uma das 4 colunas da Aba B e os compara com o endereço de e-mail na Aba A. Se uma correspondência for encontrada, escreva o cabeçalho da coluna.

Muito obrigado por qualquer ajuda > ;-) Estou aberto a quaisquer ideias sobre como configurar isso de forma diferente para facilitar ...

    

Answer 1

E você pode ter um ainda mais curto com SUMPRODUCT :

=INDEX(Sheet2!$A$1:$D$1,SUMPRODUCT((A2=Sheet2!$A:$D)*1*{1,2,3,4}))

E também notei que se nenhuma correspondência for encontrada (assim como com as outras fórmulas postadas antes desta resposta), ela retornará o índice da coluna da própria fórmula (ou seja, se a fórmula estiver na coluna B, ela será retornar o nome do cabeçalho da coluna B). Uma maneira de evitar isso é usar IF da seguinte forma:

=INDEX(Sheet2!$A$1:$D$1,IF(SUMPRODUCT((A2=Sheet2!A2:D8)*1)=0,NA(),SUMPRODUCT((A2=Sheet2!A2:D8)*1*{1,2,3,4})))

É um pouco mais longo ^^;

    

Answer 2

A seguinte fórmula de matriz deve funcionar. Insira-o na célula da linha 2 da nova coluna na Aba A e, em seguida, copie-a para a parte inferior da lista de e-mails da Aba A.

  =INDEX(Sheet2!$A$1:$D$1,1,SUM((A2=Sheet2!$A$2:$A$50)*1+(A2=Sheet2!$B$2:$B$100)*2+(A2=Sheet2!$C$2:$C$50)*3+(A2=Sheet2!$D$2:$D$50)*4))

Como uma fórmula de matriz, ela precisa ser inserida com a combinação de teclas Controle - Shift - Enter .

Para detalhar as partes da fórmula, assuma por um momento que há apenas uma coluna de dados de e-mail na guia B, por exemplo, coluna A:

• $A2=Sheet2!$A$2:$A$50 compara o email na célula A2 da guia A com todos os emails na coluna B da guia B, produzindo uma matriz de resultados de correspondência, como {False,True,False...False} . Como os e-mails na guia B são exclusivos, pode haver no máximo 1 True na matriz.

• Em seguida, essa matriz é multiplicada por 1 - ($A2=Sheet2!$A$2:$A$50)*1 - para produzir outra matriz '{0,1,0 ... 0}', que pode ser interpretada como "O valor em A2 é uma correspondência com o valor na segunda linha do intervalo de dados na primeira coluna da Guia B.

• Somando essa matriz produz o valor 1, que é usado para selecionar o valor na primeira coluna da linha de cabeçalho da guia B: INDEX($A$1:$D$1,1,SUM(($A2=Sheet2!$A$2:$A$50)*1)) .

• Podemos fazer a mesma comparação de matriz com as colunas B, C e D da guia B, multiplicando os resultados por 2, 3 e 4, respectivamente, para indicar uma correspondência na segunda, terceira etc. coluna da aba B.

Answer 3

Você também pode fazer isso com uma fórmula sem matriz, usando Countif () em vez de A2 = Range:

=INDEX(Sheet2!$A$1:$D$1,(COUNTIF(Sheet2!$A:$A,Sheet1!$A2)*1)+(COUNTIF(Sheet2!$B:$B,Sheet1!A2)*2)+(COUNTIF(Sheet2!$C:$C,Sheet1!A2)*3)+(COUNTIF(Sheet2!$D:$D,Sheet1!A2)*4))