vlookup usando dois nomes de colunas em planilhas diferentes

Question

Eu tenho uma pasta de trabalho, com duas planilhas separadas. Eu quero saber se os valores que aparecem na planilha B também aparecem na planilha A, em caso afirmativo, quero retornar um "YES". Se não, eu quero retornar um "NÃO". Baseado nas colunas A e B.

Na planilha A, eu tenho o seguinte conjunto de dados:

   NAME             DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001

Na planilha B, tenho o seguinte conjunto de dados:

   NAME            DOB
1  Bob Builder   1/1/2001 
2  Patrik Str    2/2/2001
3  Thunder Ct    3/3/2001
4  peter Grif    4/4/2001
5  Bob Builder   8/8/2011 
6  Patrik Str    2/25/2001

Eu tentei =IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no") , mas ele está procurando apenas a coluna A não B. Isso funciona bem apenas porque ele observa a coluna A, mas também há outros alunos que têm o mesmo nome, mas diferentes DOB.

A função vlookup ou match no Excel pode ser usada comparando o cabeçalho do nome da coluna como nome e DOB ao invés de A, B.

    

Answer 1

Você pode usar esta fórmula:

=IF(SUMPRODUCT(--('Worksheet A'!$A$2:$A$5='Worksheet B'!A2)*(--'Worksheet A'!$B$2:$B$5='Worksheet B'!B2))=1,"Yes","No")

$A$2:$A$5 a matriz de nome na primeira planilha altera-a até a última célula na coluna Name e $ consertará, para que você possa arrastá-la sem alterar o código A2 sozinho é o primeiro nome na segunda planilha
$ B $ 2: $ B $ 5 a matriz de DOB na primeira planilha a altera até a última célula na coluna DOB e $ consertará para que você possa arrastá-la sem alterar o código B2 a data correspondente de A2 na segunda planilha
O Sumproduct resultará em 1 quando A2 e B2 corresponderem aos dados correspondentes. Se 1 for o resultado, ele dará "Sim" se não houver correspondência para "Não" Você pode arrastar a fórmula para baixo

=IF(SUMPRODUCT(--(Name=A2)*(--DOB=B2))=1,"Yes","No")
Se você usar Definir nome em worksheet A e fornecer Name à coluna de nome sem o cabeçalho
Também dê DOB para a coluna de DOB em worksheet A sem o cabeçalho
você pode usar a fórmula acima e arrastá-la

Caso você prefira usar Match , use o seguinte:

=IF(ISNA(MATCH(1,(--(Name=A2)*(--(DOB=B2))),0)),"No","Yes")
e pressione Ctrl + Shift + Enter ao mesmo tempo ( Array Formula )
Você pode arrastar também

    

Answer 2

Faça uma terceira coluna em cada folha com a fórmula

= A2 & " " & B2

Use essa coluna para correspondência.

    

Answer 3

Usando tabelas e referências estruturadas, pode ser bastante simples. Para cada tabela de dados, Insert ► Tables ► Table

E esse método também permite que você se refira aos cabeçalhos das colunas pelo nome.

Os nomes padrão serão Table1 e Table2 - a primeira tabela que você criar será 1 e a segunda 2 , mas você poderá renomear em Fórmula ► Gerenciador de nomes

Em seguida, para a Tabela 2 em C1, adicione um Cabeçalho de coluna (por exemplo, "Na Tabela 1". Uma terceira coluna para a tabela deve aparecer.

C2: =IF(SUMPRODUCT(([@NAME]=Table1[NAME])*([@DOB]=Table1[DOB])),"YES","NO")

A fórmula será automaticamente propagada para baixo e fornecerá seus resultados.

Outra opção, que funciona assumindo que a coluna NAME é a coluna 1, seria:

C2: =IF(VLOOKUP([@NAME],Table1,2,FALSE)=[@DOB],"YES","NO")

Com referências estruturadas em tabelas, @NAME refere-se à entrada na mesma linha da fórmula, sob o cabeçalho da coluna "NAME"; Table1[NAME] refere-se à coluna inteira (exceto o cabeçalho) na Tabela1. [NAME] por si só se referiria a coluna inteira na mesma tabela.

Outra vantagem do uso de Tabelas é que a tabela (e referências) expande / contrai automaticamente à medida que você adiciona / exclui linhas ou colunas adjacentes.