Note: The other languages of the website are Google-translated. Back to English

Como retornar vários valores correspondentes com base em um ou vários critérios no Excel?

Normalmente, pesquisar um valor específico e retornar o item correspondente é fácil para a maioria de nós usando a função VLOOKUP. Mas, você já tentou retornar vários valores correspondentes com base em um ou mais critérios, conforme mostrado a seguir? Neste artigo, apresentarei algumas fórmulas para resolver essa tarefa complexa no Excel.

Retorna vários valores correspondentes com base em um ou vários critérios com fórmulas de matriz


Retorna vários valores correspondentes com base em um ou vários critérios com fórmulas de matriz

Por exemplo, desejo extrair todos os nomes com idade de 28 anos e que vêm dos Estados Unidos, aplique a seguinte fórmula:

1. Copie ou insira a fórmula abaixo em uma célula em branco onde deseja localizar o resultado:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Nota: Na fórmula acima, B2: B11 é a coluna da qual o valor correspondente é retornado; F2, C2: C11 são a primeira condição e os dados da coluna que contém a primeira condição; G2, D2: D11 são a segunda condição e os dados da coluna que contém essa condição, altere-os de acordo com sua necessidade.

2. Então aperte Ctrl + Shift + Enter para obter o primeiro resultado correspondente e, em seguida, selecione a primeira célula da fórmula e arraste a alça de preenchimento para baixo para as células até que o valor de erro seja exibido, agora, todos os valores correspondentes são retornados conforme a captura de tela mostrada abaixo:

Tips: Se você só precisa retornar todos os valores correspondentes com base em uma condição, aplique a fórmula de matriz abaixo:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Mais artigos relativos:

  • Retornar vários valores de pesquisa em uma célula separada por vírgulas
  • No Excel, podemos aplicar a função VLOOKUP para retornar o primeiro valor correspondido de células de uma tabela, mas, às vezes, precisamos extrair todos os valores correspondentes e, em seguida, separados por um delimitador específico, como vírgula, hífen, etc ... em um único célula como a seguinte captura de tela mostrada. Como podemos obter e retornar vários valores de pesquisa em uma célula separada por vírgulas no Excel?
  • Vlookup e retorna vários valores correspondentes de uma só vez na planilha do Google
  • A função normal do Vlookup na planilha do Google pode ajudá-lo a encontrar e retornar o primeiro valor correspondente com base em um determinado dado. Mas, às vezes, pode ser necessário vlookup e retornar todos os valores correspondentes conforme a imagem mostrada a seguir. Você tem alguma maneira fácil e boa de resolver essa tarefa no Planilhas Google?
  • 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 e retornar vários valores verticalmente no Excel
  • 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 dados correspondentes de retorno entre dois valores no Excel
  • No Excel, podemos aplicar a função normal Vlookup para obter o valor correspondente com base em um dado dado. Mas, às vezes, queremos vlookup e retornar o valor correspondente entre dois valores, conforme mostrado na captura de tela a seguir, como você poderia lidar com essa tarefa no Excel?

 


  • 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

 

Comentários (25)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Eu tentei exatamente essa mesma fórmula; copiado 100%. A única coisa que mudei foram os dados que estavam sendo correspondidos e retornados. Quando uso esta fórmula, o Excel diz "Você inseriu muitos argumentos para esta função).=INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume Relatório'!$A$3:$A$100)*COUNTIF($A$3,'Relatório de volume 2020'!$D$3:$D$100),ROW('Relatório de volume 2020'!$A$3:$G$100)- MIN(ROW('Relatório de Volume 2020'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
Este comentário foi feito pelo moderador no site
Oi, você poderia dar seus dados e erro de fórmula como um screesnhot aqui?
Este comentário foi feito pelo moderador no site
Olá, como posso usá-lo para a condição horizontal.
Este comentário foi feito pelo moderador no site
Qual é o "0" após o +1 na fórmula? Isso não está no exemplo um.
Este comentário foi feito pelo moderador no site
Oi eu tinha tentado a mesma fórmula. estou obtendo resultado, mas quando dou o CSE, ele não fornece várias respostas
Este comentário foi feito pelo moderador no site

Este comentário foi feito pelo moderador no site
Em relação ao retorno de vários valores correspondentes com base em um ou vários critérios com fórmulas de matriz: por que, se eu tiver os dados em qualquer outro lugar, exceto começando em A1, ele não funcionará, mesmo que eu atualize todas as referências de célula na fórmula?
Este comentário foi feito pelo moderador no site
No primeiro exemplo, que alteração na fórmula seria necessária para devolver todos os que tinham menos de 28 anos?
Este comentário foi feito pelo moderador no site
Oi,

Eu queria saber se é possível inserir um 2º critério, mas do mesmo intervalo que o 1º critério,

Por exemplo, com o exemplo usado acima, eu gostaria de pesquisar os nomes de pessoas da América e da França Então a célula F3 teria a França, Scarlett e Andrew também preencheriam a lista na Coluna G

Obrigado pela ajuda antecipadamente.
Este comentário foi feito pelo moderador no site
Olá Nick

Feliz em ajudar. Se você deseja obter os nomes de pessoas da América e da França, aconselho a usar nossa fórmula duas vezes para obter o resultado. Por favor, veja a captura de tela, em F2 e G2 são os valores "Estados Unidos" e "França". Aplicar fórmula =SEERRO(ÍNDICE($B$2:$B$11, PEQUENO(SE($F$2=$D$2:$D$11, LINHA($D$2:$D$11)-LINHA($D$2)+1 ), ROW(1:1))),"" ) para obter os resultados para a América. E aplique a fórmula =SEERRO(ÍNDICE($B$2:$B$11, PEQUENO(SE($G$2=$D$2:$D$11, LINHA($D$2:$D$11)-LINHA($D$2)+ 1), ROW(1:1))),"" ) para obter os resultados para a França. É simples. Por favor, tente.

Atenciosamente,
Mandy
Este comentário foi feito pelo moderador no site
Quando uso a segunda fórmula e arrasto para baixo, nada aparece. O resultado da fórmula (fx) diz que deveria estar retornando algo, mas está em branco. Como faço para corrigir isso?
Este comentário foi feito pelo moderador no site
Olá Alysia,

Feliz em ajudar. Tentei a segunda fórmula no artigo e arrastei a fórmula para baixo, o restante dos resultados foram retornados. Acho que pode haver duas razões para o seu problema. Primeiro, talvez você esqueça de pressionar as teclas Ctrl + Shift + Enter para inserir a fórmula. Em segundo lugar, o resultado correspondente é apenas um, portanto, nenhum outro resultado não é retornado. Por favor, faça um cheque.

Atenciosamente,
Mandy
Este comentário foi feito pelo moderador no site
Olá,
tentei usar a fórmula e ela gera um valor de 0 ou a imagem anexada
Este comentário foi feito pelo moderador no site
Olá, Milku
Sua captura de tela mostrou o software WPS da versão MAC, então não tenho certeza se nossa fórmula está disponível.
Eu carreguei um arquivo do Excel para aqui, você pode tentar ver se ele pode calcular corretamente em seu ambiente.
Obrigado!
Este comentário foi feito pelo moderador no site
Olá,
o que seria necessário para expandir a primeira fórmula no seguinte caso:
Alguns IDs estão em branco (por exemplo, a célula A5 está em branco) e eu gostaria de uma condição adicional que gerasse linhas somente quando os IDs não estivessem em branco. (Assim, a saída deve ser James e Abdul.
Obrigado!
Este comentário foi feito pelo moderador no site
Olá, Jô,
Para resolver seu problema, aplique a fórmula abaixo:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Por favor, tenha um ry, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Oi,

se na célula H1 eu escrevesse "Nome" e quisesse vinculá-lo à fórmula, como isso funcionaria?
Então eu poderia escrever "ID" na célula H1 e obteria automaticamente como resultado: AA1004; DD1009; PP1023 (para a primeira fórmula)

Obrigado com antecedência!
Este comentário foi feito pelo moderador no site
Olá, Maria
Desculpe, não consigo entender o objetivo do seu primeiro problema, você poderia explicar seu problema de forma mais clara e detalhada? Ou você pode inserir uma captura de tela aqui para descrever seu problema.
Quanto à segunda pergunta, você só precisa alterar a referência da célula assim:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Lembre-se de pressionar Ctrl + Shift + Enter chaves juntas.
Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Ei, obrigado pela fórmula. Funcionou para valores / texto "fixos" como critérios. Porém, um dos critérios que estou tentando usar é uma condição (valores <>0 ), mas não funciona a fórmula descrita. Vocês sabem o que devo mudar para adaptar a fórmula para que eu possa ter uma condição como um dos critérios, por favor?

melhor,

João
Este comentário foi feito pelo moderador no site
Olá, Marcus
Para resolver seu problema, veja este artigo:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Existem algumas explicações detalhadas sobre esta tarefa. Você só precisa mudar a criteira para a sua.
Obrigado!
Este comentário foi feito pelo moderador no site
Oi,

Primeiramente, obrigado por compartilhar!

Você pode fornecer uma solução para o caso abaixo:

Tenho 3 colunas (A: contendo informações de referência, B: contendo informações a serem pesquisadas, C: resultado da pesquisa)

O URL da imagem é fornecido abaixo

https://ibb.co/VHCd09K

Coluna A-------------------------Coluna B------------Coluna C
Nome do arquivo -------------------------Nome----------------Nome do arquivo, nome do documento, Nome do elemento, nome
Elemento Alterado------Elemento--------------Elemento Alterado, Nome do Elemento, ID do Elemento
Localização da coluna
Nome do Documento
Nome do Elemento
Nome
Categoria
Garantia
Declive
ID do elemento

O que eu preciso é pesquisar na coluna A por qualquer correspondência parcial com a célula B2 (Nome) ou B3 (Elemento) e obter o resultado em uma célula,

Obrigado, Behzad
Este comentário foi feito pelo moderador no site
Olá, Behzad
Talvez a função definida pelo usuário abaixo possa ajudá-lo.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Depois de copiar e colar este código, use esta fórmula:=ConcatPartLookUp(B2,$A$2:$A$8) para obter o resultado que você precisa.
Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Oi,

Obrigado por postar esses exemplos.
Estou tentando implementar isso na minha própria planilha, mas não consigo fazer funcionar (talvez porque estou usando uma versão europeia do Excel)?

Quero obter as datas dos dias em que tive meus turnos ou que trabalhei 'algumas' (>0) horas para um cliente.

Então em I3 é o nome e em J3 o mês. K3 e L3 são os turnos (1 é trabalhado) e horas (não sei como definir isso, deve ser maior que zero)

Meus resultados esperados estão em:
Turnos: I7 e I8
horas: J7

Então eu trabalhei mais de 0 horas para 'pessoa 2' em outubro em 3-10-2022
teve turnos para a pessoa 2 em '10-10-2022' e 28-10-2022

Quando eu adiciono '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' na minha planilha do Excel, não permite o vírgula entre as diferentes partes da fórmula.
Então eu preciso alterá-los para ';'.
Mas quando eu tento, sempre diz: '#NAME?'

Então alguém pode me ajudar com isso?

Atenciosamente,

Meia
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Este comentário foi feito pelo moderador no site
Olá, se houver valores duplicados (por exemplo, dois adams), como posso garantir que ele retorne apenas 1 adam e não 2?
Este comentário foi feito pelo moderador no site
Olá, Bobby,
Para extrair apenas valores correspondentes exclusivos, você deve aplicar a fórmula abaixo:
Após colar a fórmula, pressione Ctrl + Shift + Enter juntas para obter o resultado correto.
=SEERRO(ÍNDICE($B$2:$B$5, CORRESP(0, CONT.SE(H1:$H$1, $B$2:$B$5)+SE($D$2:$D$5<>$G$2, 1 , 0)+SE($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Por favor, tente, espero que possa ajudá-lo!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL