Pesquisa de critérios múltiplos com INDEX e MATCH
Ao lidar com um grande banco de dados em uma planilha do Excel com várias colunas e legendas de linha, é sempre difícil encontrar algo que atenda a vários critérios. Neste caso, você pode usar uma fórmula de matriz com ae ÍNDICE e JOGO funções.
Como realizar uma pesquisa com vários critérios?
Para descobrir o PRODUTOS isso é branco e médiacom um preço de $18 conforme mostrado na imagem acima, você pode aproveitar a vantagem da lógica booleana para gerar uma matriz de 1s e 0s para mostrar as linhas que atendem aos critérios. A função MATCH irá então encontrar a posição da primeira linha que atende a todos os critérios. Depois disso, INDEX encontrará o ID do produto correspondente na mesma linha.
Sintaxe genérica
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Nota: Esta é uma fórmula de matriz que requer que você insira com Ctrl + Shift + Entrar.
- intervalo_de_retorno: O intervalo de onde você deseja que a fórmula de combinação retorne o ID do produto. Aqui se refere à faixa de ID do produto.
- valor_critérios: Os critérios usados para localizar a posição do ID do produto. Aqui se refere aos valores nas células H4, H5 e H6.
- intervalo_de_critérios: Os intervalos correspondentes onde o critérios_valores estão listadas. Aqui se refere às faixas de cor, tamanho e preço.
- tipo_correspondência 0: Força MATCH a encontrar o primeiro valor que é exatamente igual ao lookup_value.
Para encontrar o produto que é branco e médiacom um preço de $18, copie ou insira a fórmula abaixo na célula H8 e pressione Ctrl + Shift + Entrar para obter o resultado:
= INDEX (B5: B10, MATCH (1, ("Branco"=C5: C10) * ("Médio"=D5: D10) * (18=E5: E10), 0))
Ou use uma referência de célula para tornar a fórmula dinâmica:
= INDEX (B5: B10, MATCH (1, (H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10), 0))
Explicação da fórmula
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): A fórmula compara a cor na célula H4 contra todas as cores da gama C5: C10; compara o tamanho em H5 contra todos os tamanhos em D5: D10; compara o preço em H6 contra todos os preços em E5: E10. O resultado inicial é assim:
{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE} * {FALSE; FALSE; TRUE; TRUE; TRUE; FALSE} * {FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.
A multiplicação irá transformar os TRUEs e FALSEs em 1s e 0s:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Após a multiplicação, teremos uma única matriz como esta:
{0;0;0;0;1;0}. - MATCH (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = MATCH (1,{0;0;0;0;1;0}, 0): O match_type 0 pede à função MATCH para encontrar a correspondência exata. A função retornará então a posição de 1 na matriz {0;0;0;0;1;0}, o que é 5.
- ÍNDICE(B5: B10,MATCH (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = ÍNDICE (B5: B10,5): A função INDEX retorna o 5o valor no intervalo de ID do produto B5: B10, o que é 30005.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
Fórmulas Relacionadas
Pesquisar o valor de correspondência mais próximo com vários critérios
Em alguns casos, pode ser necessário pesquisar o valor de correspondência mais próximo ou aproximado com base em mais de um critério. Com a combinação das funções INDEX, MATCH e IF, você pode fazer isso rapidamente no Excel.
Correspondência aproximada com INDEX e MATCH
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, dar notas aos alunos, calcular a postagem com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções INDEX e MATCH para recuperar o resultados que precisamos.
Valores de pesquisa de outra planilha ou pasta de trabalho
Se você sabe como usar a função VLOOKUP para pesquisar valores em uma planilha, os valores vlookup de outra planilha ou pasta de trabalho não serão um problema para você. O tutorial mostrará como visualizar valores de outra planilha no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.
