É fácil procurar um valor com uma chave exclusiva em uma tabela. Podemos simplesmente usar a função PROCV. Mas quando você não tem essa coluna exclusiva em seus dados e precisa pesquisar em várias colunas para corresponder a um valor, VLOOKUP não ajuda.
Portanto, para pesquisar um valor em uma tabela com vários critérios, usaremos a fórmula INDEX-MATCH-INDEX.
Fórmula genérica para pesquisa de múltiplos critérios
= INDEX (intervalo_procurado, CORRESPONDÊNCIA (1, INDEX ((critérios1 = intervalo1) * (critérios2 = intervalo2) * (critériosN = intervaloN), 0,1), 0))
intervalo_de_procura: É o intervalo do qual você deseja recuperar o valor.
Criteria1, Criteria2, Criteria N: Esses são os critérios que você deseja corresponder em intervalo1, intervalo2 e intervalo N. Você pode ter até 270 critérios - pares de intervalo.
Intervalo1, intervalo2, intervaloN: Esses são os intervalos nos quais você corresponderá aos seus respectivos critérios.
Como vai funcionar? Vamos ver…
INDEX e MATCH com Exemplo de Critérios Múltiplos
Aqui eu tenho uma tabela de dados. Quero puxar o nome do cliente usando Data de Reserva, Construtor e Área. Portanto, aqui tenho três critérios e um intervalo de pesquisa.
Escreva esta fórmula na célula I4 e pressione Enter.
= ÍNDICE (E2: E16, CORRESPONDÊNCIA (1, ÍNDICE ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0))
Como funciona:
Já sabemos como funcionam as funções INDEX e MATCH no EXCEL, então não vou explicar isso aqui. Vamos falar sobre o truque que usamos aqui.
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16): A parte principal é esta. Cada parte desta declaração retorna uma matriz de verdadeiro falso.
Quando os valores booleanos são multiplicados, eles retornam um array de 0 e 1. A multiplicação funciona como operador AND. Hense quando todos os valores são verdadeiros, ele retorna 1 senão 0
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16) Isso tudo retornará
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} * {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE} * {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Que vai se traduzir em
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
ÍNDICE((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): A função INDEX retornará a mesma matriz ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) para a função CORRESPONDÊNCIA como matriz de pesquisa.
PARTIDA(1,ÍNDICE((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): A função CORRESP procurará 1 na matriz {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. E retornará o número do índice do primeiro 1 encontrado na matriz. Qual é 8 aqui.
ÍNDICE(E2: E16,PARTIDA(1,ÍNDICE((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)): Finalmente, INDEX retornará o valor do intervalo dado (E2: E16) no encontrado índice (8).
Simples????. Desculpe, não poderia deixar mais simples.
Solução Array
Se você puder pressionar CTRL + SHIFT + ENTER conseqüentemente, poderá eliminar a função INDEX interna. Basta escrever esta fórmula e pressionar CTRL + SHIFT + ENTER.
= ÍNDICE (E2: E16, CORRESPONDÊNCIA (1, (I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0))
Fórmula de matriz genérica para pesquisa de múltiplos critérios
= INDEX (intervalo_procura, CORRESPONDÊNCIA (1, (critérios1 = intervalo1) * (critérios2 = intervalo2) * (critériosN = intervaloN), 0))
A fórmula funciona da mesma forma que a explicação acima.
Tentei o meu melhor para explicar da forma mais simples possível. Mas se eu não fui claro o suficiente, deixe-me saber na seção de comentários abaixo. A propósito, você não precisa saber como funciona o motor para dirigir um carro. Você só precisa saber como dirigi-lo. E você sabe disso muito bem.
Como pesquisar os 5 principais valores com valores duplicados usando INDEX-MATCH no Excel
Como PROCURAR Múltiplos Valores no Excel
Como VLOOKUP com Dynamic Col Index no Excel
Como usar PROCV de duas ou mais tabelas de pesquisa no Excel
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade
Como usar a função PROCV no Excel
Como usar a função CONT.SE no Excel
Como usar a função SUMIF no Excel