Ir para o conteúdo principal

Procure e retorne vários valores com base em um ou vários critérios

Normalmente, você pode usar a função Vlookup para obter o primeiro valor correspondente, mas, às vezes, você deseja retornar todos os registros correspondentes com base em um critério específico. Neste artigo, falarei sobre como vlookup e retornar todos os valores correspondentes verticalmente, horizontalmente ou em uma única célula.

Vlookup e retorna todos os valores correspondentes verticalmente

Vlookup e retorna todos os valores correspondentes horizontalmente

Vlookup e retorna todos os valores correspondentes em uma célula


Vlookup e retorna todos os valores correspondentes verticalmente

Para retornar todos os valores correspondentes verticalmente com base em um critério específico, aplique a seguinte fórmula de matriz:

1. Insira ou copie esta fórmula em uma célula em branco onde deseja produzir o resultado:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Note: Na fórmula acima, C2: C20 é a coluna que contém o registro correspondente que você deseja retornar; A2: A20 é a coluna que contém o critério; e E2 é o critério específico no qual você deseja retornar valores com base. Altere-os de acordo com sua necessidade.

2. Então aperte Ctrl + Shift + Enter juntas para obter o primeiro valor e, em seguida, arraste a alça de preenchimento para baixo para obter todos os registros correspondentes conforme necessário, consulte a captura de tela:

Dicas:

Para Vlookup e retornar todos os valores correspondentes com base em valores mais específicos verticalmente, aplique a fórmula abaixo e pressione Ctrl + Shift + Enter chaves.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )


Vlookup e retorna todos os valores correspondentes horizontalmente

Se você deseja que os valores correspondentes sejam exibidos em ordem horizontal, a fórmula de matriz abaixo pode ajudá-lo.

1. Insira ou copie esta fórmula em uma célula em branco onde deseja produzir o resultado:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Note: Na fórmula acima, C2: C20 é a coluna que contém o registro correspondente que você deseja retornar; A2: A20 é a coluna que contém o critério; e F1 é o critério específico no qual você deseja retornar valores com base. Altere-os de acordo com sua necessidade.

2. Então aperte Ctrl + Shift + Enter juntas para obter o primeiro valor e, em seguida, arraste a alça de preenchimento para a direita para obter todos os registros correspondentes conforme necessário, consulte a captura de tela:

Dicas:

Para procurar e retornar todos os valores correspondentes com base em valores mais específicos horizontalmente, aplique a fórmula abaixo e pressione Ctrl + Shift + Enter chaves.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")


Vlookup e retorna todos os valores correspondentes em uma célula

Para vlookup e retornar todos os valores correspondentes em uma única célula, você deve aplicar a seguinte fórmula de matriz.

1. Insira ou copie a fórmula abaixo em uma célula em branco:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Note: Na fórmula acima, C2: C20 é a coluna que contém o registro correspondente que você deseja retornar; A2: A20 é a coluna que contém o critério; e F1 é o critério específico no qual você deseja retornar valores com base. Altere-os de acordo com sua necessidade.

2. Então aperte Ctrl + Shift + Enter chaves juntas para obter todos os valores correspondentes em uma única célula, consulte a captura de tela:

Dicas:

Para procurar e retornar todos os valores correspondentes com base em valores mais específicos em uma única célula, aplique a fórmula abaixo e pressione Ctrl + Shift + Enter chaves.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Nota: Esta fórmula só foi aplicada com sucesso no Excel 2016 e versões posteriores. Se você não tem Excel 2016, consulte plítica de privacidade para baixá-lo.

Mais artigos relativos do Vlookup:

  • Vlookup e retornar vários valores da lista suspensa
  • No Excel, como você poderia visualizar e retornar vários valores correspondentes de uma lista suspensa, o que significa que quando você escolhe um item da lista suspensa, todos os seus valores relativos são exibidos de uma vez, conforme a captura de tela a seguir. Neste artigo, irei apresentar a solução passo a passo.
  • Vlookup para retornar em branco em vez de 0 ou N / A no Excel
  • Normalmente, quando você aplica a função vlookup para retornar o valor correspondente, se a célula correspondente estiver em branco, ela retornará 0 e, se o valor correspondente não for encontrado, você receberá um erro # N / A valor. Em vez de exibir o valor 0 ou # N / A, como você pode fazer com que ele mostre uma célula em branco?
  • Vlookup para retornar várias colunas da tabela do Excel
  • Na planilha do Excel, você pode aplicar a função Vlookup para retornar o valor correspondente de uma coluna. Mas, às vezes, você pode precisar extrair valores correspondentes de várias colunas, conforme mostrado a seguir. Como você poderia obter os valores correspondentes ao mesmo tempo de várias colunas usando a função Vlookup?
  • Valores de Vlookup em várias planilhas
  • No Excel, podemos facilmente aplicar a função vlookup para retornar os valores correspondentes em uma única tabela de uma planilha. Mas, você já pensou em como visualizar o valor em várias planilhas? Suponha que eu tenha as três planilhas a seguir com intervalo de dados e, agora, desejo obter parte dos valores correspondentes com base nos critérios dessas três planilhas.

  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations