Como fazer uma correspondência de índice com vários critérios no Excel

Há alguns anos, trabalhei em uma análise de campanha de marketing em que tive que comparar o desempenho das vendas em várias regiões. Os dados estavam espalhados em várias planilhas do Excel, e eu precisava extrair números específicos de vendas de produtos em um único relatório resumido. No início, tentei pesquisar e copiar os dados manualmente, mas não foi tão fácil quanto eu pensava. Se você errar uma linha, todo o relatório poderá ser desfeito.

Foi quando encontrei o site INDEX MATCH().. Precisei de algumas tentativas para acertar a fórmula, mas ela se tornou parte da minha rotina quando vi a facilidade com que ela localizava e obtinha os números exatos de que eu precisava. Com apenas duas funções, eu podia extrair exatamente os dados de que precisava, independentemente de sua dispersão em planilhas.

Neste artigo, explicarei como você pode fazer o mesmo usando as funções INDEX() e MATCH(). Sempre há mais para aprender com o Excel. Se você for um iniciante, recomendo nosso curso de Introdução ao Excel. Se você tiver mais experiência, experimente nosso curso Funções avançadas do Excel.

Uma atualização sobre INDEX MATCH

INDEX MATCH é é uma forma abreviada de falar sobre a combinação de duas funções do combinação de duas funções do Excel que trabalham juntas para realizar pesquisas avançadas. Também poderíamos nos referir a essa ideia como INDEX(MATCH()), mas escolherei INDEX MATCH neste artigo. Agora, vamos dar uma olhada em cada uma delas:

A função INDEX() recupera o valor de uma célula com base em sua posição em um intervalo especificado. Aqui está a sintaxe:

=INDEX(array, row_num, [column_num])

Aqui:

• array é o intervalo de células do qual você deseja recuperar um valor.

• row_num é o número da linha na matriz a partir da qual você deve retornar um valor.

• column_num (opcional) é o número da coluna na matriz a partir da qual você deve retornar um valor.

A função MATCH() identifica a posição relativa de um valor em um intervalo. Sua sintaxe é:

=MATCH(lookup_value, lookup_array, [match_type])

Aqui:

• lookup_value é o valor que você deseja encontrar.

• lookup_array é o intervalo em que a função procura o valor.

match_type é opcional. 1 (padrão) encontra o valor menor ou igual a lookup_value (a matriz deve ser classificada em ordem crescente). 0 encontra uma correspondência exata (a matriz não precisa ser classificada). -1 encontra o menor valor maior ou igual a lookup_value (a matriz deve ser classificada em ordem decrescente).

Como combinar INDEX() com MATCH()

Ao aninhar MATCH() em INDEX(), você pode criar uma pesquisa dinâmica. Vamos entender isso com um exemplo: Suponha que você queira encontrar a posição de David Wilson no conjunto de dados. Em vez de codificar o número da linha em INDEX(), use MATCH() para determiná-lo:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

Na fórmula acima, MATCH("David Wilson", A2:A6, 0) retorna 4, que é a posição da linha. E o site INDEX(C2:C6, 4) recupera o valor da 4ª linha do intervalo C2:C6, que é Seattle.

Combine INDEX() com MATCH(). Imagem do autor.

Para tornar isso ainda mais dinâmico, você pode substituir o David Wilson codificado por uma referência de célula. Dessa forma, a fórmula se ajusta automaticamente com base no valor em D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Substitua o valor codificado no INDEX MATCH. Imagem do autor.

partida de índice vs. VLOOKUP()

Agora que você sabe como INDEX() e MATCH() funcionam individualmente e como a combinação deles torna as pesquisas mais dinâmicas, vamos ver por que INDEX MATCH é uma escolha melhor do que VLOOKUP().

• Diferentemente de VLOOKUP(),, que exige que a coluna de pesquisa esteja à esquerda, INDEX MATCH permite que você recupere dados de qualquer coluna, independentemente de sua posição.

• INDEX MATCH processa somente o intervalo de células necessário, em comparação com o VLOOKUP(),, que varre tabelas inteiras. 

• As fórmulas que usam VLOOKUP() podem falhar se as colunas forem inseridas ou excluídas, pois elas dependem de índices de coluna estáticos. Por outro lado, o site INDEX MATCH faz referência a intervalos dinâmicos para garantir que suas fórmulas permaneçam intactas apesar das mudanças estruturais nos dados.

• Com o INDEX MATCH,, você não precisa contar os números das colunas manualmente. Especifique a coluna de pesquisa e a coluna de retorno, e você terá terminado. 

INDEX MATCH com vários critérios

Muitas vezes, preciso trabalhar com conjuntos de dados que contêm entradas duplicadas, e encontrar valores neles é extremamente difícil. Mas agora eu uso o INDEX MATCH porque ele lida com esses cenários com muita facilidade, ao contrário de outras fórmulas de pesquisa padrão. Vou mostrar a você como eu uso isso, passo a passo.

Configurar dados para vários critérios

Primeiro, crie seu conjunto de dados e verifique se ele está bem organizado em uma tabela com cabeçalhos claros para cada coluna. Cada linha deve representar um registro exclusivo, e cada coluna deve conter um atributo de dados específico.

Por exemplo, aqui está um conjunto de dados de amostra:

Conjunto de dados para critérios múltiplos INDEX MATCH. Imagem do autor.

Escreva a fórmula para vários critérios

Depois que os dados estiverem devidamente organizados, é hora de escrever a fórmula. A fórmula INDEX MATCH recupera um valor de outra coluna ao identificar uma linha que atende a várias condições. Isso é feito combinando testes lógicos na função MATCH() e incorporando-os à função INDEX().

Aqui está a sintaxe básica para isso: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Aqui: 

• Return_range é o intervalo do qual o valor será retornado.

• Criteria1, Criteria2, … são as condições a serem satisfeitas.

• Range1, Range2, … são intervalos correspondentes aos critérios.

Agora que temos um conjunto de dados configurado, vamos examinar atentamente os dois métodos para responder à nossa pergunta: como usar o INDEX MATCH com vários critérios.

Usar colunas auxiliares para critérios complexos

Se o seu conjunto de dados tiver várias condições, use colunas auxiliares para simplificar o processo. Ele combinará todas as condições em uma única coluna para facilitar as pesquisas. Por exemplo, estou usando o mesmo conjunto de dados para criar uma coluna auxiliar, combinando as colunas First Name e Salary:

=A2&B2

Crie uma coluna auxiliar. Imagem do autor.

Essa coluna auxiliar simplifica minha fórmula INDEX MATCH. Em vez de escrever uma fórmula de matriz complexa com várias condições, fiz referência à coluna auxiliar em minha fórmula para uma abordagem mais simples:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH com coluna auxiliar. Imagem do autor.

Combine vários critérios com uma fórmula de matriz

Se você não preferir colunas auxiliares, poderá usar fórmulas de matriz para obter o mesmo resultado. Eles permitem que você avalie vários critérios diretamente na função MATCH(). Por exemplo, aqui está como eu encontro o salário de Alice no departamentode RH:

Etapa 1: Escreva a função MATCH() com condições lógicas:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

Nessa fórmula, 1 garante que a função MATCH() procure linhas em que todas as condições sejam verdadeiras. (F4=A2:A11) verifica se o valor em F4 corresponde a qualquer valor no intervalo A2:A11. (F5=B2:B11) verifica se o valor em F5 corresponde a qualquer valor no intervalo B2:B11. O operador * atua como uma lógica AND, garantindo que todas as condições sejam atendidas.

Etapa 2: Envolva essa função MATCH() dentro da função INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Etapa 3: Finalize a fórmula. Se você estiver usando uma versão mais antiga do Excel, pressione Ctrl+Shift+Enterpara transformá-la em uma fórmula de matriz. Em versões mais recentes, pressione Enter.

Matriz INDEX MATCH com vários critérios. Imagem do autor.

Usos avançados do INDEX MATCH com vários critérios

Você pode fazer muito mais com a função INDEX MATCH. Vamos ver como: 

Use INDEX MATCH com intervalos nomeados e intervalos dinâmicos

Eu uso intervalos nomeados no Excel para definir nomes significativos, como resultados ou totalSales, em vez de referências padrão como A1:A10. Dessa forma, fica mais fácil gerenciar fórmulas em diferentes planilhas. 

Para nomear um intervalo de células, selecione as células e pressione Ctrl + F3 (para Windows) ou Cmd + F3 (para Mac) para abrir o Name Manager. Em seguida, clique em New, digite um nome e clique em OK.

Dê um nome ao intervalo. Imagem do autor.

A única diferença entre um intervalo nomeado e um intervalo dinâmico é que um intervalo nomeado se refere a um grupo fixo de células, enquanto um intervalo dinâmico se ajusta automaticamente quando os dados são adicionados ou removidos.

Para definir uma faixa dinâmica, selecione as células. Naguia Formulas, clique em Name Manager ou pressione Ctrl + F3 para abrir o Excel Name Manager e clique em New. A caixa de diálogo New Name será exibida. Agora, no campo Name , digite o nome que você deseja. Em seguida, nocampo Refers to , insira a fórmula para o intervalo dinâmico .

Defina um intervalo dinâmico. Imagem do autor. 

Agora vamos dar uma olhada em um exemplo: Eu defini dois intervalos dinâmicos e um estático:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Agora, eu uso esses intervalos na fórmula INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

E você pode ver que a fórmula se torna muito mais fácil de entender com nomes claros.

Use intervalos dinâmicos e nomeados com INDEX MATCH. Imagem do autor.

INDEX MATCH aninhado para pesquisas complexas

Além do trabalho básico, você pode usar as funções aninhadas do INDEX MATCH para lidar também com pesquisas complexas. Por exemplo, tenho um conjunto de dados que mostra as vendas por categoria de produto em diferentes regiões. 

Conjunto de dados brutos. Imagem do autor.

Quero encontrar vendas de móveis no leste. Mas, para isso, preciso fazer a correspondência entre a categoria do produto (linha) e a região (coluna), o que um INDEX MATCH básico não consegue fazer. É por isso que uso a seguinte fórmula INDEX MATCH aninhada aqui:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Veja como isso funciona: O INDEX() extrai um valor do intervalo B2:D4, mas precisa de um número de linha e um número de coluna para indicar exatamente onde procurar. Portanto, o primeiro MATCH(D6, A2:A4, 0) calcula o número da linha. Se D6contiver Furniture, ele pesquisará a coluna A2:A4 e a encontrará na segunda linha .

Em seguida, o site MATCH(D7, B1:D1, 0) determina o número da coluna. Se D7 disser East (Leste), ele examinará B1:D1 e o encontrará na segunda coluna .

Quando o INDEX() souber a linha e a coluna, ele exibirá os valores de saída. Em nosso caso, as vendas da Furniture no Leste são de 450.

Use o INDEX MATCH aninhado. Imagem do autor.

Eu uso essa fórmula em vez de pesquisar manualmente em linhas e colunas, pois ela trata tudo com precisão. 

Desafios comuns e dicas de solução de problemas

Quando comecei a usar o INDEX MATCH,, enfrentei vários desafios e não quero que você tenha as mesmas frustrações. Portanto, vou orientá-lo sobre os desafios mais comuns e mostrar como superá-los.

Tratamento de erros em fórmulas INDEX MATCH

Erros como #N/A e #VALUE! podem parecer frustrantes no início, mas são muito fáceis de corrigir. Vamos ver como você pode identificar o que está causando o problema e as etapas simples para resolvê-lo.

O erro #N/A ocorre quando a função MATCH() não encontra um valor. Isso ocorre porque o valor de pesquisa não existe na matriz de pesquisa ou os dados contêm espaços ocultos. Por exemplo, uma vez fiz referência à coluna errada ao extrair Nomes de funcionários:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

#Erro N/A em INDEX MATCH. Imagem do autor.

Para corrigir esses problemas, confirme se o valor de pesquisa existe na matriz e use a função TRIM() para limpar os espaços:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

#N/A erro corrigido em INDEX MATCH. Imagem do autor.

#VALUE! O erro aparece quando a fórmula não é definida como uma fórmula de matriz. Por exemplo, se eu usar a função MATCH() e incluir mais de um intervalo, o Excel a verá como uma fórmula de matriz. No entanto, se não estiver configurado corretamente, o Excel apresentará um erro #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

#Erro de valor em INDEX MATCH. Imagem do autor.

Para resolver isso, pressione Ctrl + Shift + Enter depois que você inserir a fórmula. Dessa forma, o Excel envolverá a fórmula em chaves {}, indicando que agora ela é uma fórmula de matriz. Mas não digite as chaves manualmente, pois isso quebrará a fórmula.

#Erro de valor corrigido em INDEX MATCH. Imagem do autor.

Otimizar o desempenho com grandes conjuntos de dados

Em conjuntos de dados maiores, minhas fórmulas ficavam lentas de tempos em tempos e, por isso, eu tinha que esperar a atualização dos cálculos. Se você também estiver enfrentando problemas semelhantes, experimente estas dicas: 

• Limite o intervalo de pesquisa: Restrinja os intervalos apenas ao que for necessário. Por exemplo, em vez de A:A, use A1:A100 para reduzir o tempo de computação.

• Use colunas auxiliares: Pré-calcular critérios complexos com colunas auxiliares. Isso reduzirá a carga computacional das fórmulas de matriz.

• Ativar o modo de cálculo manual: Altere o Excel para o modo de cálculo manual para evitar recálculos constantes. Depois de fazer as alterações, pressione F9 para atualizar as fórmulas manualmente.

• Evite funções voláteis: Minimize usando funções voláteis como NOW(), RAND() e TODAY() em combinação com INDEX MATCH. Essas funções acionam recálculos sempre que a pasta de trabalho é atualizada.

Considerações finais 

INDEX MATCH Você pode economizar tempo e simplificar a análise de dados complexos. Se você trabalha com conjuntos de dados maciços, vale a pena experimentá-los. Porém, a melhor maneira de solidificar seu entendimento é por meio da prática. Portanto, eu diria para você lidar com alguns conjuntos de dados e fazer experiências com o que aprendeu. Foi assim que aprimorei minhas habilidades. 

Para aprofundar seu conhecimento, confira nosso curso Funções avançadas do Excel para dominar uma gama mais ampla de ferramentas poderosas. Mas se você quiser desenvolver um conhecimento abrangente sobre análise de dados no Excel, recomendo nosso curso Análise de dados no Excel. Ele abrange tudo, desde a preparação de dados até a visualização.