Fórmula do Excel que retorna linha se o valor for encontrado em uma coluna

Question

Eu tenho uma lista de entradas DNS que eu preciso classificar para obter os bons registros.

Na planilha1, eu tenho um dump dos dados brutos, na coluna 1 é um ID de zona que é um número.

Na planilha2, tenho uma coluna formada pelos IDs da zona que desejo manter.

Na planilha3, estou procurando uma maneira de obter a coluna 1 da planilha1, para ver se ela corresponde a um dos valores da planilha2 na coluna 1. Se isso acontecer, o resultado deverá ser a linha inteira na planilha 3.

Isso é possível? Exemplo de dados abaixo:

Folha1 - 4 colunas

1   foo            A     IP_Address

1   foomaster    CNAME   IP_Address

392 jimmy          A     IP_Address

Folha2

Na coluna1 está uma lista de IDs de zona aceitável que eu quero.

Folha3

Se o valor da sheet1-column1 existir na sheet2-column1, cole a linha inteira da sheet1.

    

Answer 1

Uma maneira rápida e suja de fazer isso é com = COUNTIF () . Se o valor for encontrado, retorne o valor da célula A1, B1, C1 etc., preenchendo a fórmula à direita.

Na Folha 3, Célula A1 , digite o seguinte:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),Sheet1!A1)

Agora use os recursos de preenchimento à direita ( Ctrl + R ) e de preenchimento ( Ctrl + D ) para aplicar a fórmula a quantas células forem necessárias, dependendo do número de colunas + linhas esperadas nos dados brutos que você tiver na Planilha 1. Se a pesquisa for bem-sucedida, ela preencherá os dados dessa linha na Planilha 1.

Se a pesquisa não for bem sucedida, a linha retornará FALSE. Se uma célula na planilha 1 não tiver dados, ela retornará 0. Se desejado, você pode retornar um texto em branco ("") em vez de um FALSE ou um 0 com uma fórmula como:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A1),IF(ISBLANK(Sheet1!A1),"",Sheet1!A1),"")

Para dizer isso novamente, isso é rápido e sujo e terá implicações de desempenho se você tiver um conjunto de dados grande. Normalmente, é melhor colocar seus dados brutos em um banco de dados - você pode usar uma Tabela Dinâmica ou consultas SQL simples para extrair os dados necessários no formato necessário.

    

Answer 2

Ok, mas você precisa de uma terceira folha para esse truque. Nesta terceira folha, você precisará arrastar esta fórmula para a direita e para baixo, para corresponder ao tamanho da tabela ( sheet1 ) onde os valores que você estará procurando residem. Sheet2 será onde tentaremos encontrar os ditos valores.

=index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0))

Então, isso é como umvlookup arrastável, no qual você estará preenchendo uma nova tabela do mesmo tamanho que sheet1 , caso não encontre nenhuma correspondência, ela retornará #N/A , se você quiser manipular que, em seguida, sorround em um IFERROR como este

=IFERROR(index('sheet2'!A$1:A$1000,Match('sheet1'$D1:$D1000,'sheet2'!$A$1:$A$1000,0));"NO MATCH")

Em seguida, você precisará filtrar e excluir as entradas "NO MATCH". Mas esta é a fórmula que eu pessoalmente uso para esse tipo de coisa.

Talvez o , , ' , etc; A sintaxe não é a mesma para você por causa da localidade e de diferentes versões do Excel.

Felicidades