Combinação de funções INDEX & MATCH no Excel (10 exemplos fáceis)

O Excel tem muitas funções - cerca de 450+ delas.

E muitos deles são simplesmente fantásticos. A quantidade de trabalho que você pode fazer com algumas fórmulas ainda me surpreende (mesmo depois de ter usado o Excel por mais de 10 anos).

E entre todas essas funções incríveis, a combinação de funções INDEX MATCH se destaca.

Eu sou um grande fã do combo INDEX MATCH e eu deixei isso bem claro muitas vezes.

Eu até escrevi um artigo sobre Index Match Vs VLOOKUP que gerou um pouco de debate (você pode verificar a seção de comentários para alguns fogos de artifício).

E hoje, estou escrevendo este artigo focado exclusivamente em Index Match para mostrar alguns cenários simples e avançados onde você pode usar esta poderosa combinação de fórmulas e fazer o trabalho.

Observação: Existem outras fórmulas de pesquisa no Excel - como VLOOKUP e HLOOKUP, e essas são ótimas. Muitas pessoas consideram VLOOKUP mais fácil de usar (e isso também é verdade). Acredito que INDEX MATCH é a melhor opção em muitos casos. Mas como as pessoas acham difícil, ele é menos usado. Então, estou tentando simplificar usando este tutorial.

Agora, antes de mostrar como a combinação de INDEX MATCH está mudando o mundo dos analistas e cientistas de dados, deixe-me apresentar a você as partes individuais - funções INDEX e MATCH.

Função INDEX: Encontra o valor com base nas coordenadas

A maneira mais fácil de entender como a função Índice funciona é pensar nela como um satélite GPS.

Assim que você informar ao satélite as coordenadas de latitude e longitude, ele saberá exatamente para onde ir e encontrar essa localização.

Portanto, apesar de ter um número impressionante de combinações lat-long, o satélite saberia exatamente para onde olhar.

Rapidamente fiz uma busca pelo meu local de trabalho e foi isso que encontrei.

De qualquer forma, chega de geografia.

Assim como um satélite precisa de coordenadas de latitude e longitude, a função INDEX no Excel precisaria do número da linha e da coluna para saber a qual célula você está se referindo.

E essa é a função INDEX do Excel em uma casca de noz.

Então, deixe-me definir em palavras simples para você.

A função INDEX usará o número da linha e o número da coluna para encontrar uma célula no intervalo fornecido e retornar o valor nela.

Por si só, INDEX é uma função muito simples, sem utilidade. Afinal, na maioria dos casos, é improvável que você conheça os números das linhas e colunas.

Mas…

O fato de você poder usá-lo com outras funções (dica: MATCH) que podem encontrar o número da linha e o número da coluna torna INDEX uma função Excel extremamente poderosa.

Abaixo está a sintaxe da função INDEX:

= INDEX (matriz, núm_linha, [núm_col]) = ÍNDICE (matriz, núm_linha, [núm_col], [núm_área])

• variedade - uma intervalo de células ou uma constante de matriz.
• row_num - o número da linha a partir da qual o valor deve ser obtido.
• [col_num] - o número da coluna da qual o valor deve ser obtido. Embora este seja um argumento opcional, se row_num não for fornecido, ele precisa ser fornecido.
• [area_num] - (Opcional) Se o argumento da matriz for composto de vários intervalos, esse número será usado para selecionar a referência de todos os intervalos.

A função INDEX tem 2 sintaxes (apenas FYI).

O primeiro é usado na maioria dos casos. O segundo é usado apenas em casos avançados (como fazer uma pesquisa de três vias), que abordaremos em um dos exemplos posteriormente neste tutorial.

Mas se você é novo nessa função, lembre-se da primeira sintaxe.

Abaixo está um vídeo que explica como usar a função INDEX

Função MATCH: Encontra a posição baseada em um valor de pesquisa

Voltando ao meu exemplo anterior de longitude e latitude, MATCH é a função que pode encontrar essas posições (no mundo das planilhas do Excel).

Em uma linguagem simples, a função MATCH do Excel pode encontrar a posição de uma célula em um intervalo.

E com base em que ele encontraria a posição de uma célula?

Com base no valor de pesquisa.

Por exemplo, se você tem uma lista conforme mostrado abaixo e deseja encontrar a posição do nome ‘Marca’ nela, então você pode usar a função CORRESPONDÊNCIA.

A função retorna 3, pois essa é a posição da célula com o nome Mark nela.

A função MATCH começa procurando de cima para baixo para o valor de pesquisa (que é ‘Mark’) no intervalo especificado (que é A1: A9 neste exemplo). Assim que encontrar o nome, ele retornará a posição naquele intervalo específico.

Abaixo está a sintaxe da função MATCH no Excel.

= MATCH (lookup_value, lookup_array, [match_type])

• lookup_value - O valor para o qual você está procurando uma correspondência em lookup_array.
• lookup_array - O intervalo de células no qual você está procurando por lookup_value.
• [tipo de partida] - (Opcional) Isso especifica como o Excel deve procurar um valor correspondente. Pode ter três valores -1, 0 ou 1.

Compreendendo o argumento do tipo de correspondência na função MATCH

Há uma coisa adicional que você precisa saber sobre a função MATCH, e é sobre como ela analisa os dados e encontra a posição da célula.

O terceiro argumento da função MATCH pode ser 0, 1 ou -1.

Abaixo está uma explicação de como esses argumentos funcionam:

• 0 - procurará uma correspondência exata do valor. Se uma correspondência exata for encontrada, a função MATCH retornará a posição da célula. Caso contrário, ele retornará um erro.
• 1 - encontra o maior valor menor ou igual ao valor de pesquisa. Para que isso funcione, seu intervalo de dados precisa ser classificado em ordem crescente.
• -1 - encontra o menor valor maior ou igual ao valor de pesquisa. Para que isso funcione, seu intervalo de dados precisa ser classificado em ordem decrescente.

Abaixo está um vídeo que explica como usar a função MATCH (junto com o argumento do tipo de correspondência)

Para resumir e colocar em palavras simples:

• INDEX precisa da posição da célula (número da linha e da coluna) e fornece o valor da célula.
• MATCH encontra a posição usando um valor de pesquisa.

Vamos combiná-los para criar uma central elétrica (INDEX + MATCH)

Agora que você tem um entendimento básico de como as funções INDEX e MATCH funcionam individualmente, vamos combinar esses dois e aprender sobre todas as coisas maravilhosas que eles podem fazer.

Para entender isso melhor, tenho alguns exemplos que usam a combinação INDEX MATCH.

Vou começar com um exemplo simples e, em seguida, mostrar alguns casos de uso avançados também.

Clique aqui para baixar o arquivo de exemplo

Exemplo 1: Uma pesquisa simples usando a combinação INDEX MATCH

Vamos fazer uma pesquisa simples com INDEX / MATCH.

Abaixo está uma tabela onde tenho as notas de dez alunos.

A partir desta tabela, quero encontrar as marcas para Jim.

Abaixo está a fórmula que pode fazer isso facilmente:

= ÍNDICE ($ A $ 2: $ B $ 11, CORRESPONDÊNCIA ("Jim", $ A $ 2: $ A $ 11,0), 2)

Agora, se você está pensando que isso pode ser feito facilmente usando uma função VLOOKUP, você está certo! Este não é o melhor uso da grandiosidade do INDEX MATCH. Apesar de ser fã de INDEX MATCH, é um pouco mais difícil do que PROCV. Se buscar dados de uma coluna à direita é tudo que você deseja fazer, eu recomendo que você use PROCV.

A razão pela qual mostrei este exemplo, que também pode ser feito facilmente com VLOOKUP, é para mostrar como INDEX MATCH funciona em uma configuração simples.

Agora, deixe-me mostrar um benefício do INDEX MATCH.

Suponha que você tenha os mesmos dados, mas em vez de tê-los em colunas, você os tem em linhas (como mostrado abaixo).

Quer saber, você ainda pode usar a combinação INDEX MATCH para obter as notas de Jim.

Abaixo está a fórmula que lhe dará o resultado:

= ÍNDICE ($ B $ 1: $ K $ 2,2, CORRESPONDÊNCIA (“Jim”, $ B $ 1: $ K $ 1,0))

Observe que você precisa alterar o intervalo e as partes da linha / coluna para fazer esta fórmula funcionar também para dados horizontais.

Isso não pode ser feito com VLOOKUP, mas você ainda pode fazer isso facilmente com HLOOKUP.

A combinação INDEX MATCH pode lidar facilmente com dados horizontais e verticais.

Clique aqui para baixar o arquivo de exemplo

Exemplo 2: pesquisa à esquerda

É mais comum do que você pensa.

Muitas vezes, pode ser necessário buscar os dados de uma coluna que está à esquerda da coluna que contém o valor de pesquisa.

Algo conforme mostrado abaixo:

Para descobrir as vendas de Michael, você terá que fazer uma pesquisa à esquerda.

Se você está pensando em PROCV, deixe-me interromper você aí.

PROCV não é feito para procurar e buscar os valores à esquerda.

Você ainda pode fazer isso usando PROCV?

Sim você pode!

Mas isso pode se transformar em uma fórmula longa e feia.

Portanto, se você quiser fazer uma pesquisa e buscar dados das colunas à esquerda, é melhor usar a combinação INDEX MATCH.

Abaixo está a fórmula que obterá o número de vendas de Michael:

= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Outro ponto aqui para INDEX MATCH. VLOOKUP pode buscar os dados apenas nas colunas que estão à direita da coluna que possui o valor de pesquisa.

Exemplo 3: pesquisa bidirecional

Até agora, vimos os exemplos em que queríamos buscar os dados da coluna adjacente à coluna que tem o valor de pesquisa.

Mas na vida real, os dados geralmente se estendem por várias colunas.

INDEX MATCH pode lidar facilmente com uma pesquisa bidirecional.

Abaixo está um conjunto de dados das notas do aluno em três disciplinas diferentes.

Se você quiser obter rapidamente as notas de um aluno em todas as três disciplinas, pode fazer isso com INDEX MATCH.

A fórmula abaixo fornecerá as marcas para Jim para todos os três assuntos (copie e cole em uma célula e arraste para preencher outras células ou copie e cole em outras células).

= ÍNDICE ($ B $ 2: $ D $ 11, CORRESPONDÊNCIA ($ F $ 3, $ A $ 2: $ A $ 11,0), CORRESPONDÊNCIA (G $ 2, $ B $ 1: $ D $ 1,0))

Deixe-me também explicar rapidamente essa fórmula.

A fórmula INDEX usa B2: D11 como intervalo.

A primeira PARTIDA usa o nome (Jim na célula F3) e busca a posição dele na coluna de nomes (A2: A11). Este se torna o número da linha a partir do qual os dados precisam ser obtidos.

A segunda fórmula MATCH usa o nome do assunto (na célula G2) para obter a posição desse nome de assunto específico em B1: D1. Por exemplo, Matemática é 1, Física é 2 e Química é 3.

Uma vez que essas posições MATCH são alimentadas na função INDEX, ela retorna a pontuação com base no nome do aluno e no nome da disciplina.

Esta fórmula é dinâmica, o que significa que se você alterar o nome do aluno ou da disciplina, ainda funcionará e buscará os dados corretos.

Uma grande coisa sobre o uso de INDEX / MATCH é que mesmo se você trocar os nomes dos assuntos, ele continuará a dar o resultado correto.

Exemplo 4: valor de pesquisa de várias colunas / critérios

Suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja buscar as marcas para ‘Mark Long’.

Como os dados estão em duas colunas, não posso pesquisar por Mark e obter os dados.

Se eu fizer dessa forma, vou obter os dados de marcas para Mark Frost e não para Mark Long (porque a função MATCH me dará o resultado para MARK que ela atende).

Uma maneira de fazer isso é criar uma coluna auxiliar e combinar os nomes. Assim que tiver a coluna auxiliar, você pode usar PROCV e obter os dados das marcas.

Se você estiver interessado em aprender como fazer isso, leia este tutorial sobre como usar VLOOKUP com vários critérios.

Mas com a combinação INDEX / MATCH, você não precisa de uma coluna auxiliar. Você pode criar uma fórmula que lida com vários critérios na própria fórmula.

A fórmula abaixo dará o resultado.

= ÍNDICE ($ C $ 2: $ C $ 11, CORRESPONDÊNCIA ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Deixe-me explicar rapidamente o que essa fórmula faz.

A parte MATCH da fórmula combina o valor de pesquisa (Mark e Long), bem como toda a matriz de pesquisa. Quando $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 é usado como a matriz de pesquisa, ele realmente verifica o valor de pesquisa em relação à sequência combinada de nome e sobrenome (separados pelo símbolo de barra vertical).

Isso garante que você obtenha o resultado correto sem usar nenhuma coluna auxiliar.

Você pode fazer esse tipo de pesquisa (onde há várias colunas / critérios) com VLOOKUP também, mas você precisa usar uma coluna auxiliar. A combinação INDEX MATCH torna um pouco mais fácil fazer isso sem nenhuma coluna auxiliar.

Exemplo 5: obter valores de toda a linha / coluna

Nos exemplos acima, usamos a função INDEX para obter o valor de uma célula específica. Você fornece o número da linha e da coluna, e ele retorna o valor nessa célula específica.

Mas você pode fazer mais.

Você também pode usar a função INDEX para obter os valores de uma linha ou coluna inteira.

E como isso pode ser útil, você pergunta!

Suponha que você queira saber a pontuação total de Jim nas três disciplinas.

Você pode usar a função INDEX para obter primeiro todas as notas de Jim e, em seguida, usar a função SUM para obter um total.

Vamos ver como fazer isso.

Abaixo, tenho as pontuações de todos os alunos em três disciplinas.

A fórmula abaixo me dará a pontuação total de Jim em todas as três disciplinas.

= SOMA (ÍNDICE ($ B $ 2: $ D $ 11, CORRESPONDÊNCIA ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Deixe-me explicar como essa fórmula funciona.

O truque aqui é usar 0 como o número da coluna.

Quando você usa 0 como o número da coluna na função INDEX, ele retornará todos os valores da linha. Da mesma forma, se você usar 0 como o número da linha, ele retornará todos os valores da coluna.

Portanto, a parte abaixo da fórmula retorna uma matriz de valores - {97, 70, 73}

ÍNDICE ($ B $ 2: $ D $ 11, CORRESPONDÊNCIA ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Se você apenas inserir a fórmula acima em uma célula do Excel e pressionar Enter, verá um erro #VALUE! erro. Isso ocorre porque ele não está retornando um único valor, mas uma matriz de valores.

Mas não se preocupe, a matriz de valores ainda está lá. Você pode verificar isso selecionando a fórmula e pressionando a tecla F9. Ele mostrará o resultado da fórmula que, neste caso, é uma matriz de três valores - {97, 70, 73}

Agora, se você envolver esta fórmula INDEX na função SUM, ela lhe dará a soma de todas as notas marcadas por Jim.

Você também pode usar o mesmo para obter as notas mais altas, mais baixas e médias de Jim.

Assim como fizemos para um aluno, você também pode fazer isso para uma disciplina. Por exemplo, se você deseja a pontuação média em um assunto, pode manter o número da linha como 0 na fórmula INDEX e ela fornecerá todos os valores da coluna desse assunto.

Clique aqui para baixar o arquivo de exemplo

Exemplo 6: Encontre a nota do aluno (técnica de correspondência aproximada)

Até agora, usamos a fórmula MATCH para obter a correspondência exata do valor de pesquisa.

Mas você também pode usá-lo para fazer uma correspondência aproximada.

Agora, o que diabos é Partida Aproximada?

Deixe-me explicar.

Quando você está procurando coisas como nomes ou ids, está procurando uma correspondência exata. Mas às vezes, você precisa saber a faixa em que se encontram seus valores de pesquisa. Isso geralmente acontece com os números.

Por exemplo, como professor da turma, você pode querer saber qual é a nota de cada aluno em uma disciplina, e a nota é decidida com base na pontuação.

Abaixo está um exemplo, onde eu quero a nota para todos os alunos e a classificação é decidida com base na tabela à direita.

Então, se um aluno obtiver menos de 33, a nota é F e se ele / ela obtiver menos de 50, mas mais de 33, é E, e assim por diante.

Abaixo está a fórmula que fará isso.

= ÍNDICE ($ F $ 3: $ F $ 8, CORRESPONDÊNCIA (B2, $ E $ 3: $ E $ 8,1), 1)

Deixe-me explicar como essa fórmula funciona.

Na função MATCH, usamos 1 como o argumento [match_type]. Este argumento retornará o maior valor menor ou igual ao valor de pesquisa.

Isso significa que a fórmula MATCH atravessa o intervalo de marcas e, assim que encontra um intervalo de marcas que é igual ou menor que o valor das marcas de pesquisa, ele para aí e retorna sua posição.

Portanto, se o valor da marca de pesquisa for 20, a função MATCH retornará 1 e se for 85, retornará 5.

E a função INDEX usa esse valor de posição para obter a nota.

IMPORTANTE: para que isso funcione, seus dados precisam ser classificados em ordem crescente. Se não for, você pode obter resultados errados.

Observe que o acima também pode ser feito usando a fórmula VLOOKUP abaixo:

= PROCV (B2, $ E $ 3: $ F $ 8,2, VERDADEIRO)

Mas a função MATCH pode dar um passo adiante quando se trata de uma correspondência aproximada.

Você também pode ter dados decrescentes e pode usar a combinação INDEX MATCH para encontrar o resultado. Por exemplo, se eu alterar a ordem da tabela de notas (conforme mostrado abaixo), ainda posso encontrar as notas dos alunos.

Para fazer isso, tudo que preciso fazer é alterar o argumento [match_type] para -1.

Abaixo está a fórmula que usei:

= ÍNDICE ($ F $ 3: $ F $ 8, CORRESPONDÊNCIA (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP também pode fazer uma correspondência aproximada, mas apenas quando os dados são classificados em ordem crescente (mas não funciona se os dados são classificados em ordem decrescente).

Exemplo 7: pesquisas que diferenciam maiúsculas de minúsculas

Até agora, todas as pesquisas que fizemos não fizeram distinção entre maiúsculas e minúsculas.

Isso significa que se o valor de pesquisa era Jim, JIM ou Jim, não importava. Você obterá o mesmo resultado.

Mas e se você quiser que a pesquisa faça distinção entre maiúsculas e minúsculas.

Este é geralmente o caso quando você tem grandes conjuntos de dados e uma possibilidade de repetição ou nomes / ids distintos (com a única diferença sendo o caso)

Por exemplo, suponha que eu tenha o seguinte conjunto de dados de alunos onde há dois alunos com o nome Jim (a única diferença é que um é inserido como Jim e outro como Jim).

Observe que há dois alunos com o mesmo nome - Jim (célula A2 e A5).

Como uma pesquisa normal não funcionaria, você precisa fazer uma pesquisa com distinção entre maiúsculas e minúsculas.

Abaixo está a fórmula que lhe dará o resultado certo. Como esta é uma fórmula de matriz, você precisa usar Control + Shift + Enter.

= ÍNDICE ($ B $ 2: $ B $ 11, CORRESPONDÊNCIA (VERDADEIRO, EXATO (D3, A2: A11), 0), 1)

Deixe-me explicar como essa fórmula funciona.

A função EXACT verifica uma correspondência exata do valor de pesquisa (que é ‘jim’ neste caso). Ele passa por todos os nomes e retorna FALSE se não for uma correspondência e TRUE se for uma correspondência.

Portanto, a saída da função EXATA neste exemplo é - {FALSO; FALSO; FALSO; VERDADEIRO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}

Observe que há apenas um TRUE, que é quando a função EXACT encontrou uma correspondência perfeita.

A função MATCH então encontra a posição TRUE na matriz retornada pela função EXACT, que é 4 neste exemplo.

Uma vez que temos a posição, a função INDEX a usa para encontrar as marcas.

Exemplo 8: Encontre a correspondência mais próxima

Vamos avançar um pouco agora.

Suponha que você tenha um conjunto de dados onde deseja encontrar a pessoa que tem a experiência de trabalho mais próxima da experiência necessária (mencionada na célula D2).

Embora as fórmulas de pesquisa não sejam feitas para isso, você pode combiná-las com outras funções (como MIN e ABS) para fazer isso.

Segue abaixo a fórmula que vai encontrar a pessoa com a experiência mais próxima da desejada e retornar o nome da pessoa. Observe que a experiência precisa ser mais próxima (que pode ser menor ou maior).

= ÍNDICE ($ A $ 2: $ A $ 15, CORRESPONDÊNCIA (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Como esta é uma fórmula de matriz, você precisa usar Control + Shift + Enter.

O truque nesta fórmula é alterar o valor de pesquisa e a matriz de pesquisa para encontrar a diferença mínima de experiência nos valores necessários e reais.

Antes de explicar a fórmula, vamos entender como você faria isso manualmente.

Você percorrerá cada célula da coluna B e encontrará a diferença na experiência entre o que é necessário e o que uma pessoa tem. Depois de ter todas as diferenças, você encontrará a que for mínima e buscará o nome dessa pessoa.

Isso é exatamente o que estamos fazendo com esta fórmula.

Deixe-me explicar.

O valor de pesquisa na fórmula MATCH é MIN (ABS (D2-B2: B15)).

Esta parte dá a você a diferença mínima entre a experiência dada (que é de 2,5 anos) e todas as outras experiências. Neste exemplo, ele retorna 0,3

Observe que usei o ABS para ter certeza de que estou procurando o mais próximo (que pode ser mais ou menos do que a experiência fornecida).

Agora, esse valor mínimo se torna nosso valor de pesquisa.

O array de pesquisa na função MATCH é ABS (D2- $ B $ 2: $ B $ 15).

Isso nos dá uma matriz de números da qual 2,5 (a experiência necessária) foi subtraída.

Portanto, agora temos um valor de pesquisa (0,3) e uma matriz de pesquisa ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

A função MATCH encontra a posição de 0,3 nesta matriz, que também é a posição do nome da pessoa que tem a experiência mais próxima.

Este número de posição é então usado pela função INDEX para retornar o nome da pessoa.

Leitura Relacionada: Encontre a correspondência mais próxima no Excel (exemplos usando fórmulas de pesquisa)

Clique aqui para baixar o arquivo de exemplo

Exemplo 9: Use INDEX MATCH com caracteres curinga

Se quiser pesquisar um valor quando houver uma correspondência parcial, você precisará usar caracteres curinga.

Por exemplo, abaixo está um conjunto de dados do nome da empresa e suas capitalizações de mercado e você deseja obter a capitalização de mercado. dados para as três empresas à direita.

Uma vez que essas não são correspondências exatas, você não pode fazer uma pesquisa regular neste caso.

Mas você ainda pode obter os dados corretos usando um asterisco (*), que é um caractere curinga.

Abaixo está a fórmula que fornecerá os dados combinando os nomes das empresas na coluna principal e buscando o valor de mercado para eles.

= ÍNDICE ($ B $ 2: $ B $ 10, CORRESPONDÊNCIA (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Deixe-me explicar como essa fórmula funciona.

Como não há correspondência exata de valores de pesquisa, usei D2 & ”*” como o valor de pesquisa na função MATCH.

Um asterisco é um caractere curinga que representa qualquer número de caracteres. Isso significa que Apple * na fórmula significaria qualquer string de texto que comece com a palavra Apple e possa ter qualquer número de caracteres depois dela.

Então quando Maçã* é usado como o valor de pesquisa e a fórmula MATCH o procura na coluna A, ela retorna a posição de ‘Apple Inc.’, uma vez que começa com a palavra Apple.

Você também pode usar caracteres curinga para localizar strings de texto em que o valor de pesquisa esteja entre eles. Por exemplo, se você usar * Apple * como valor de pesquisa, ele encontrará qualquer string que contenha a palavra apple em qualquer lugar.

Observação: essa técnica funciona bem quando você tem apenas uma instância de correspondência. Mas se você tiver várias instâncias de correspondência (por exemplo, Apple Inc e Apple Corporation, a função MATCH retornará apenas a posição da primeira instância de correspondência.

Exemplo 10: pesquisa de três vias

Este é um uso avançado do INDEX MATCH, mas ainda o cobrirei para mostrar a você o poder dessa combinação.

Lembre-se de que disse que a função INDEX tem duas sintaxes:

= INDEX (matriz, núm_linha, [núm_col]) = ÍNDICE (matriz, núm_linha, [núm_col], [núm_área])

Até agora, em todos os nossos exemplos, usamos apenas o primeiro.

Mas para uma pesquisa de três vias, você precisa usar a segunda sintaxe.

Deixe-me primeiro explicar o que significa um olhar triplo.

Em uma pesquisa bidirecional, usamos a fórmula INDEX MATCH para obter as marcas quando temos o nome do aluno e o nome da disciplina. Por exemplo, buscar as marcas de Jim em matemática é uma pesquisa bidirecional.

Um olhar triplo acrescentaria outra dimensão a ele. Por exemplo, suponha que você tenha um conjunto de dados conforme mostrado abaixo e deseja saber a pontuação de Jim em Matemática no exame de meio de período, então esta seria uma pesquisa de três vias.

Abaixo está a fórmula que dará o resultado.

= ÍNDICE (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), CORRESPONDÊNCIA ($ F $ 5, $ A $ 3: $ A $ 7,0), CORRESPONDÊNCIA (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Teste de unidade", 1, IF (G $ 3 = "Médio prazo", 2,3))))

A fórmula acima verificou três coisas - o nome do aluno, a matéria e o exame. Depois de encontrar o valor correto, ele o retorna na célula.

Deixe-me explicar como essa fórmula funciona dividindo-a em partes.

• matriz - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Em vez de usar uma única matriz, neste caso, usei três matrizes entre parênteses.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): A função MATCH é usada para encontrar a posição do nome do aluno na célula $ F $ 5 na lista de nomes do aluno.
• col_num - CORRESPONDÊNCIA (G $ 4, $ B $ 2: $ D $ 2,0): A função MATCH é usada para encontrar a posição do nome do sujeito na célula $ B $ 2 na lista de nomes do sujeito.
• [area_num] - IF (G $ 3 = ”Teste de unidade”, 1, IF (G $ 3 = ”Médio prazo”, 2,3)): O valor do número da área diz à função INDEX qual das três matrizes usar para buscar o valor. Se o exame for Unidade Term, a função IF retornaria 1 e a função INDEX usaria a primeira matriz para buscar o valor. Se o exame for intermediário, a fórmula IF retornará 2, caso contrário, retornará 3.

Este é um exemplo avançado de uso de INDEX MATCH, e é improvável que você encontre uma situação em que precise usá-lo. Mas ainda é bom saber o que as fórmulas do Excel podem fazer.

Clique aqui para baixar o arquivo de exemplo

Por que INDEX / MATCH é melhor do que VLOOKUP?

Ou é?

Sim, é - na maioria dos casos.

Apresentarei meu caso em breve.

Mas antes de fazer isso, deixe-me dizer o seguinte - VLOOKUP é uma função extremamente útil e eu adoro isso. Ele pode fazer muitas coisas no Excel e eu mesmo o uso de vez em quando. Dito isto, não significa que não possa haver nada melhor, e INDEX / MATCH (com mais flexibilidade e funcionalidades) é melhor.

Então, se você quiser fazer alguma pesquisa básica, é melhor usar VLOOKUP.

INDEX / MATCH é VLOOKUP com esteróides. E depois de aprender INDEX / MATCH, você pode sempre preferir usá-lo (especialmente por causa da flexibilidade que ele tem).

Sem forçar demais, deixe-me explicar rapidamente os motivos pelos quais INDEX / MATCH é melhor do que VLOOKUP.

INDEX / MATCH pode olhar para a esquerda (bem como para a direita) do valor de pesquisa

Eu cobri isso em um dos exemplos acima.

Se você tiver um valor que está à esquerda do valor de pesquisa, você não pode fazer isso com VLOOKUP

Pelo menos não apenas com VLOOKUP.

Sim, você pode combinar PROCV com outras fórmulas e fazer isso, mas fica complicado e confuso.

INDEX / MATCH, por outro lado, é feito para pesquisar em todos os lugares (seja à esquerda, à direita, para cima ou para baixo)

INDEX / MATCH pode trabalhar com faixas verticais e horizontais

Novamente, com total respeito a VLOOKUP, ele não foi feito para fazer isso.

Afinal, o V em VLOOKUP significa vertical.

VLOOKUP só pode percorrer dados verticais, enquanto INDEX / MATCH pode percorrer dados verticalmente e também horizontalmente.

Claro, existe a função HLOOKUP para cuidar da pesquisa horizontal, mas não é VLOOKUP então … certo?

Eu gosto do fato de que o combo INDEX MATCH é flexível o suficiente para trabalhar com dados verticais e horizontais.

PROCV não pode funcionar com dados decrescentes

Quando se trata de correspondência aproximada, VLOOKUP e INDEX / MATCH estão no mesmo nível.

Mas INDEX MATCH pega o ponto, pois também pode lidar com dados que estão em ordem decrescente.

Eu mostro isso em um dos exemplos neste tutorial onde temos que encontrar a nota dos alunos com base na tabela de notas. Se a tabela for classificada em ordem decrescente, VLOOKUP não funcionará (mas INDEX MATCH sim).

INDEX / MATCH pode ser um pouco mais rápido

Eu serei verdadeiro. Eu não fiz esse teste sozinho.

Estou contando com a sabedoria de um mestre do Excel - Charley Kyd.

A diferença de velocidade em VLOOKUP e INDEX / MATCH é quase imperceptível quando você tem pequenos conjuntos de dados. Mas se você tiver milhares de linhas e muitas colunas, isso pode ser um fator decisivo.

Em seu artigo, Charley Kyd afirma:

“Na pior das hipóteses, o método INDEX-MATCH é quase tão rápido quanto VLOOKUP; no seu melhor, é muito mais rápido. ”

INDEX / MATCH é independente da posição real da coluna

Se você tiver um conjunto de dados conforme mostrado abaixo enquanto busca a pontuação de Jim em Física, você pode fazer isso usando PROCV.

E para fazer isso, você pode especificar o número da coluna como 3 em PROCV.

Está tudo bem.

Mas e se eu excluir a coluna Matemática.

Nesse caso, a fórmula VLOOKUP será interrompida.

Por quê? - Porque foi codificado para usar a terceira coluna e, quando excluo uma coluna intermediária, a terceira coluna se torna a segunda coluna.

Usar INDEX / MATCH, neste caso, é melhor porque você pode tornar o número da coluna dinâmico usando MATCH. Portanto, em vez de um número de coluna, ele verifica o nome do assunto e o usa para retornar o número da coluna.

Certamente você pode fazer isso combinando VLOOKUP com MATCH, mas se você combinar mesmo assim, por que não fazer com INDEX, que é muito mais flexível.

Ao usar INDEX / MATCH, você pode inserir / excluir colunas com segurança em seu conjunto de dados.

Apesar de todos esses fatores, há um motivo pelo qual VLOOKUP é tão popular.

E é um grande motivo.

VLOOKUP é mais fácil de usar

PROCV leva no máximo quatro argumentos. Se você conseguir entender esses quatro, está pronto para ir.

E como a maioria dos casos básicos de pesquisa também são tratados por PROCV, ela rapidamente se tornou a função mais popular do Excel.

Eu o chamo de funções do Rei do Excel.

INDEX / MATCH, por outro lado, é um pouco mais difícil de usar. Você pode pegar um jeito quando começar a usá-lo, mas para um iniciante, PROCV é muito mais fácil de explicar e aprender.

E este não é um jogo de soma zero.

Então, se você é novo no mundo da pesquisa e não sabe como usar VLOOKUP, é melhor aprender isso.

Eu tenho um guia detalhado sobre como usar VLOOKUP no Excel (com muitos exemplos)

Minha intenção neste artigo não é lançar duas funções incríveis uma contra a outra. Eu queria mostrar a vocês o poder do combo INDEX MATCH e todas as grandes coisas que ele pode fazer.

Espero que você tenha achado este artigo útil.

Deixe-me saber sua opinião na seção de comentários, e caso você encontre algum erro neste tutorial, por favor me avise.