Excel - Usando o VLOOKUP para criar uma tabela de resumo

Question

Preciso de uma pequena ajuda com algum Excel.

Employee  Locations     Hours   OT
Mr.One    Station 1     40      6
Mrs.Seven Station 2     30      6
Mr.Two    Station 3     30      4
Mr.Three  Station 4     40      4
Mrs.Eight Station 1     32      6
Mr.Four   Station 2     32      7
Mrs.Nine  Station 3     40      6
Mr.Five   Station 4     40      7
Mr.Six    Station 1     25      2
Mrs.Ten   Station 2     40      3
Mr.Eleven Station 3     60      1

Eu tenho uma planilha com duas planilhas. Uma é a planilha de dados (mostrada acima) e a outra planilha é um resumo que possui a coluna Locations como lista de validação de dados. Eu quero usar a lista de validação de dados para puxar todas as pessoas e informações de um local específico. Eu tentei usar VLOOKUP() , mas só sei como usá-lo para puxar uma pessoa de cada vez, não um grupo de registros específicos para um local.

    

Answer 1

Se você estiver usando o Excel 2007 ou posterior, poderá usar a solução a seguir (a função IFERROR é o fator limitante). Na tabela de resumo de amostra mostrada na captura de tela abaixo, usei essa fórmula, preencha várias linhas, para retornar os funcionários que corresponderam ao local escolhido na lista suspensa em B2:

=IFERROR(INDEX(Data!$A$1:$A$12,SMALL(IF(Data!$B$1:$B$12=Summary!$B$1,ROW(Data!$A$1:$A$12),2000000),ROW()-3)),"")

Esta fórmula deve ser inserida como uma fórmula de matriz. Faça isso pressionando Ctrl + Deslocar + Enter .

Na coluna de horas, essa fórmula de matriz foi preenchida:

=IFERROR(INDEX(Data!$C$1:$C$12,SMALL(IF(Data!$B$1:$B$12=Summary!$B$1,ROW(Data!$C$1:$C$12),2000000),ROW()-3)),"")

Uma coisa a notar sobre as fórmulas é o último argumento na SMALL function ROW()-3 . Isso retorna 1 para o primeiro registro na Linha 4. Se sua tabela de resumo iniciar em uma linha diferente, você precisará ajustar esse argumento para que ele retorne 1 para a primeira linha de sua tabela. Por exemplo, se sua tabela começar na Linha 2, você precisará alterar o argumento para ROW()-1 .