Filtre os valores por categoria na nova tabela com o vlookup

Question

Eu tenho os seguintes dados que contém uma lista de agentes e seu respectivo gerente:

Preciso filtrar os agentes pelo gerente e colocá-los em uma nova tabela para cada gerente?

Eu tentei com o vlookup como visto na imagem, mas parece que assim que a função encontrar um valor, ele irá se ater àquele.

As tabelas azuis são o resultado que quero obter. Eu acho que tem que ser uma função para cada nova tabela - tudo bem!

Eu poderia tentar usar o VBA, com um loop for, mas achei que preferia não reinventar a roda, se possível.

Obrigado!

    

Answer 1

VLOOKUP() sempre encontrará a primeira ocorrência do lookup_value.

INDEX(array, row_num,[column_num]) poderia listar os Agentes se você atribuísse o row_num de cada um como foi preenchido.

Vamos começar obtendo um array (lista) dos números de linha dos Agents for Mark. Esta expressão

IF($E$2:$E$9=I$1,ROW($E$1:$E$8))

verifica se a coluna E é igual a I$1 (Mark) e, onde quer que seja, retorna um número da lista de números de linha das linhas 1 a 8. Nas posições em que a coluna E não é igual a Mark, o expressão retorna False . Portanto, a matriz real que essa expressão fornece é {1,FALSE,3,FALSE,5,FALSE,7,FALSE} . Observe que o número que é retornado para cada um dos agentes de Mark é, na verdade, um a menos que seu número de linha.

Em seguida, queremos listar esses números conforme a fórmula é preenchida, ignorando os valores FALSE . SMALL(array, k) retorna o k menor valor de array . À medida que é preenchido, ROW(I1) incrementos e os números são listados em ordem:

SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1))

Agora, podemos usar essa expressão para a função row_num em INDEX() para obter os nomes dos agentes de Mark:

=INDEX($F$2:$F$9,SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1)))

Esta expressão retorna o valor em F2:F9 correspondente ao número fornecido por SMALL() . Então, como está preenchido, lista os nomes dos agentes de Mark. Você pode usar essa fórmula para listar os agentes, mas veja a nota abaixo.

Se for preenchido mais do que o número de agentes que o Mark possui, isso dará um erro #N/A! . Esses podem ser convertidos em espaços em branco envolvendo a fórmula inteira em IFERROR() :

=IFERROR(INDEX($F$2:$F$9,SMALL(IF($E$2:$E$9=I$1,ROW($E$1:$E$8)),ROW(I1))),"")

Esta fórmula, preenchida e diretamente de I2, fornece os resultados mostrados abaixo.

Note que esta é uma fórmula de matriz, por isso deve ser introduzida com CTRL Shift Enter , em vez de apenas Enter .

Esta é uma explicação bastante prolixa para a fórmula, mas espero que ajude. Boa sorte.