Por que INDEX e MATCH são melhores que VLOOKUP no Excel

Anteriormente, explicamos aos iniciantes como usar as funções básicas do VLOOKUP (Inglês VLOOKUP, a abreviação significa "função de pesquisa vertical"). E usuários experientes viram várias fórmulas mais complicadas.

E neste artigo vamos tentar dar informações sobre outro método de trabalhar com pesquisa vertical.

Você pode estar se perguntando: “Por que isso é necessário?”. E isso é necessário para mostrar todos os métodos de pesquisa possíveis. Além disso, várias restrições de PROCV muitas vezes impedem a obtenção do resultado desejado. Nesse sentido, INDEX( ) MATCH( ) é muito mais funcional e diversificado, além de ter menos restrições.

CORRESPONDÊNCIA DE ÍNDICE Básico

Como o objetivo deste guia é mostrar o quão bom é esse recurso, Vejamos as informações básicas sobre os princípios de sua operação. E mostraremos exemplos e também consideraremos o porquê, é melhor que VLOOKUP().

Sintaxe e uso da função INDEX

Esta função ajuda a encontrar o valor desejado entre as áreas de pesquisa especificadas com base no número da coluna ou linha. Sintaxe:

=ÍNDICE(matriz, número da linha, número da coluna):

• array – a área em que a busca será realizada;
• número da linha – o número da linha a ser pesquisada na matriz especificada. Se o número da linha for desconhecido, o número da coluna deve ser especificado;
• número da coluna – o número da coluna a ser encontrada na matriz especificada. Se o valor for desconhecido, é necessário um número de linha.

Um exemplo de uma fórmula simples:

=ÍNDICE(A1:S10,2,3)

A função pesquisará no intervalo de A1 a C10. Os números mostram de qual linha (2) e coluna (3) mostrar o valor desejado. O resultado será a célula C2.

Bem simples, certo? Mas quando você trabalha com documentos reais, é improvável que você tenha informações sobre números de colunas ou células. É para isso que serve a função MATCH().

Sintaxe e uso da função MATCH

A função MATCH() procura o valor desejado e mostra seu número aproximado na área de pesquisa especificada.

A sintaxe searchpos() se parece com isso:

=MATCH(valor para pesquisa, array para pesquisa, tipo de correspondência)

• valor de pesquisa – o número ou texto a ser encontrado;
• array pesquisado – a área onde a pesquisa será realizada;
• tipo de correspondência – especifica se deve-se procurar o valor exato ou os valores mais próximos a ele:
  • 1 (ou nenhum valor especificado) – retorna o maior valor igual ou menor que o valor especificado;
  • 0 – mostra uma correspondência exata com o valor pesquisado. Na combinação INDEX() MATCH() você quase sempre precisará de uma correspondência exata, então escrevemos 0;
  • -1 – Mostra o menor valor que é maior ou igual ao valor especificado na fórmula. A classificação é realizada em ordem decrescente.

Por exemplo, no intervalo B1:B3 New York, Paris, London estão registrados. A fórmula abaixo mostrará o número 3 porque Londres é a terceira da lista:

=EXPOSE(Londres,B1:B3,0)

Como trabalhar com a função INDEX MATCH 

Você provavelmente já começou a entender o princípio pelo qual se constrói o trabalho conjunto dessas funções. Em suma, então INDEX() procura o valor desejado entre as linhas e colunas especificadas. E MATCH() mostra os números desses valores:

=INDEX(coluna da qual o valor é retornado, MATCH(valor para pesquisar, coluna para pesquisar, 0))

Ainda está com dificuldade em entender como funciona? Talvez um exemplo explique melhor. Suponha que você tenha uma lista de capitais mundiais e sua população:

Para descobrir o tamanho da população de uma determinada capital, por exemplo, a capital do Japão, usamos a seguinte fórmula:

=ÍNDICE(C2:C10, CORRESP(Japão, A2:A10,0))

Explicação:

• A função MATCH() procura o valor – “Japão” no array A2:A10 e retorna o número 3, pois o Japão é o terceiro valor na lista. 
• Essa figura vai paranúmero da linha” na fórmula INDEX() e diz à função para imprimir um valor desta linha.

Então a fórmula acima se torna a fórmula padrão ÍNDICE(C2:C10,3). A fórmula pesquisa das células C2 a C10 e retorna dados da terceira célula desse intervalo, ou seja, C4, porque a contagem regressiva começa na segunda linha.

Não quer prescrever o nome da cidade na fórmula? Em seguida, escreva-o em qualquer célula, digamos F1, e use-o como referência na fórmula MATCH(). E você acaba com uma fórmula de pesquisa dinâmica:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Importante! Número de linhas em ordem INDEX() deve ser igual ao número de linhas em matriz considerada em MATCH(), caso contrário você obterá o resultado errado.

Espere um minuto, por que não usar a fórmula PROCV()?

=PROCV(F1, A2:C10, 3, Falso)

 Qual é o sentido de perder tempo tentando descobrir todas essas complexidades do INDEX MATCH?

Nesse caso, não importa qual função usar. Este é apenas um exemplo para entender como as funções INDEX() e MATCH() funcionam juntas. Outros exemplos mostrarão do que essas funções são capazes em situações em que o VLOOKUP é impotente. 

CORRESPONDÊNCIA DE ÍNDICE ou PROCV

Ao decidir qual fórmula de pesquisa usar, muitos concordam que INDEX() e MATCH() são muito superiores a VLOOKUP. No entanto, muitas pessoas ainda usam PROCV(). Em primeiro lugar, VLOOKUP() é mais simples e, em segundo lugar, os usuários não entendem completamente todas as vantagens de trabalhar com INDEX() e MATCH(). Sem esse conhecimento, ninguém concordará em gastar seu tempo estudando um sistema complexo.

Aqui estão as principais vantagens de INDEX() e MATCH() sobre VLOOKUP():

• Pesquise da direita para a esquerda. VLOOKUP() não pode pesquisar da direita para a esquerda, portanto os valores que você procura devem estar sempre nas colunas mais à esquerda da tabela. Mas INDEX() e MATCH() podem lidar com isso sem problemas. Este artigo lhe dirá como é na prática: como encontrar o valor desejado no lado esquerdo.

1. Adição ou remoção segura de colunas. A fórmula VLOOKUP() mostra resultados incorretos ao remover ou adicionar colunas porque VLOOKUP() precisa do número exato da coluna para ser bem-sucedido. Naturalmente, quando as colunas são adicionadas ou removidas, seus números também mudam. 

E nas fórmulas INDEX() e MATCH(), um intervalo de colunas é especificado, não colunas individuais. Como resultado, você pode adicionar e remover colunas com segurança sem precisar atualizar a fórmula a cada vez.

1. Sem limites nos volumes de pesquisa. Ao usar VLOOKUP(), o número total de critérios de pesquisa não deve exceder 255 caracteres ou você obterá um #VALUE! Portanto, se seus dados contiverem um grande número de caracteres, INDEX() e MATCH() são a melhor opção.
2. Alta velocidade de processamento. Se suas tabelas forem relativamente pequenas, é improvável que você perceba qualquer diferença. Mas, se a tabela contiver centenas ou milhares de linhas e, portanto, houver centenas e milhares de fórmulas, INDEX () e MATCH () lidarão com muito mais rapidez do que VLOOKUP (). O fato é que o Excel processará apenas as colunas especificadas na fórmula, em vez de processar a tabela inteira. 

O impacto no desempenho de VLOOKUP() será especialmente perceptível se sua planilha contiver um grande número de fórmulas como VLOOKUP() e SUM(). Verificações separadas das funções VLOOKUP() são necessárias para analisar cada valor em uma matriz. Portanto, o Excel precisa processar uma enorme quantidade de informações, e isso diminui significativamente o trabalho.

Exemplos de Fórmula 

Já descobrimos a utilidade dessas funções, então podemos passar para a parte mais interessante: a aplicação do conhecimento na prática.

Fórmula para pesquisar da direita para a esquerda

Como já mencionado, o VLOOKUP não pode realizar essa forma de pesquisa. Portanto, se os valores desejados não estiverem na coluna mais à esquerda, VLOOKUP() não produzirá um resultado. As funções INDEX() e MATCH() são mais versáteis, e a localização dos valores não tem grande importância para que funcionem.

Por exemplo, adicionaremos uma coluna de classificação ao lado esquerdo de nossa tabela e tentaremos descobrir qual classificação em termos de população a capital de Nosso País ocupa.

Na célula G1, escrevemos o valor a ser encontrado e, em seguida, usamos a seguinte fórmula para pesquisar no intervalo C1:C10 e retornar o valor correspondente de A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Pronto. Se você planeja usar essa fórmula para várias células, certifique-se de corrigir os intervalos usando endereçamento absoluto (por exemplo, $A$2: $A$10 e $C$2: 4C$10).

ÍNDICE MAIS EXPOSTO MAIS EXPOSTO  para pesquisar em colunas e linhas

Nos exemplos acima, usamos essas funções como substituto de VLOOKUP() para retornar valores de um intervalo predefinido de linhas. Mas e se você precisar fazer uma pesquisa matricial ou bilateral?

Parece complicado, mas a fórmula para esses cálculos é semelhante à fórmula padrão INDEX() MATCH(), com apenas uma diferença: a fórmula MATCH() deve ser usada duas vezes. A primeira vez para obter o número da linha e a segunda vez para obter o número da coluna:

=INDEX(array, MATCH(valor de pesquisa vertical, coluna de pesquisa, 0), MATCH(valor de pesquisa horizontal, linha de pesquisa, 0))

Vamos olhar para a tabela abaixo e tentar fazer uma fórmula ÍNDICE() EXPRESS() EXPRESS() para exibir dados demográficos em um país específico para um ano selecionado.

O país de destino está na célula G1 (pesquisa vertical) e o ano de destino está na célula G2 (pesquisa horizontal). A fórmula ficará assim:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Como esta fórmula funciona

Como acontece com qualquer outra fórmula complexa, elas são mais fáceis de entender dividindo-as em equações individuais. E então você pode entender o que cada função individual faz:

• CORRESP(G1,A2:A11,0) – procura um valor (G1) no intervalo A2:A11 e mostra o número desse valor, no nosso caso é 2;
• PESQUISA(G2,B1:D1,0) – procura um valor (G2) no intervalo B1:D1. Neste caso, o resultado foi 3.

Os números de linha e coluna encontrados são enviados para o valor correspondente na fórmula INDEX():

=ÍNDICE(B2:D11,2,3)

Como resultado, temos um valor que está em uma célula na interseção de 2 linhas e 3 colunas no intervalo B2:D11. E a fórmula mostra o valor desejado, que está na célula D3.

Pesquise por várias condições com INDEX e MATCH

Se você leu nosso guia para PROCV(), provavelmente já tentou várias fórmulas de pesquisa. Mas esse método de pesquisa tem uma limitação significativa – a necessidade de adicionar uma coluna auxiliar.

Mas a boa notícia é que Com INDEX() e MATCH(), você pode pesquisar várias condições sem precisar editar ou alterar sua planilha.

Aqui está a fórmula geral de pesquisa de várias condições para INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

A anotação: esta fórmula deve ser usada junto com o atalho de teclado CTRL+SHIFT+ENTER.

Suponha que você precise encontrar o valor que está procurando com base em 2 condições: Comprador и Produtos.

Isso requer a seguinte fórmula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Nesta fórmula, C2:C10 é o intervalo em que a pesquisa ocorrerá, F1 – esta condição, A2:A10— é o intervalo para comparar a condição, F2 – condição 2, V2: V10 – intervalo para comparação da condição 2.

Não se esqueça de pressionar a combinação no final do trabalho com a fórmula CTRL + SHIFT + ENTER – O Excel fechará automaticamente a fórmula com chaves, conforme mostrado no exemplo:

Se você não quiser usar uma fórmula de matriz para o seu trabalho, adicione outro INDEX() à fórmula e pressione ENTER, será semelhante ao exemplo:

Como essas fórmulas funcionam

Esta fórmula funciona da mesma forma que a fórmula padrão INDEX() MATCH(). Para pesquisar várias condições, basta criar várias condições False e True que representam as condições individuais corretas e incorretas. E essas condições se aplicam a todos os elementos correspondentes da matriz. A fórmula converte os argumentos False e True para 0 e 1, respectivamente, e gera um array onde 1 são os valores correspondentes que foram encontrados na string. MATCH() encontrará o primeiro valor que corresponde a 1 e o passará para a fórmula INDEX(). E, por sua vez, retornará o valor já desejado na linha especificada da coluna desejada.

Uma fórmula sem uma matriz depende da capacidade de INDEX() para lidar com eles por conta própria. O segundo INDEX() na fórmula corresponde a falsy (0), então ele passa a matriz inteira com esses valores para a fórmula MATCH(). 

Esta é uma explicação bastante longa da lógica por trás dessa fórmula. Para mais informações leia o artigo “INDEX MATCH com várias condições".

AVERAGE, MAX e MIN em INDEX e MATCH

O Excel tem suas próprias funções especiais para encontrar médias, máximos e mínimos. Mas e se você quiser obter dados da célula associada a esses valores? Nesse caso AVERAGE, MAX e MIN devem ser usados ​​em conjunto com INDEX e MATCH.

ÍNDICE CORRESP e MÁX.

Para encontrar o maior valor na coluna D e exibi-lo na coluna C, use a fórmula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

ÍNDICE CORRESP e MIN

Para encontrar o menor valor na coluna D e exibi-lo na coluna C, use a seguinte fórmula:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

ÍNDICE DE PESQUISA e SERPENTE

Para encontrar o valor médio na coluna D e exibir esse valor em C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Dependendo de como seus dados são gravados, o terceiro argumento para MATCH() é 1, 0 ou -1:

• se as colunas forem classificadas em ordem crescente, defina 1 (a fórmula calculará o valor máximo, que é menor ou igual ao valor médio);
• se a classificação for decrescente, então -1 (a fórmula produzirá o valor mínimo que é maior ou igual à média);
• se a matriz de pesquisa contiver um valor exatamente igual à média, defina-o como 0. 

 Em nosso exemplo, a população é classificada em ordem decrescente, então colocamos -1. E o resultado é Tóquio, pois o valor da população (13,189) é o mais próximo do valor médio (000).

PROCV() também pode realizar esses cálculos, mas apenas como uma fórmula de matriz: PROCV com AVERAGE, MIN e MAX.

INDEX MATCH e ESND/IFERROR

Você provavelmente já percebeu que se a fórmula não encontrar o valor desejado, ela gera um erro #N / D. Você pode substituir a mensagem de erro padrão por algo mais informativo. Por exemplo, defina o argumento na fórmula No XNUMXth:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Com esta fórmula, se você inserir dados que não estão na tabela, o formulário fornecerá a mensagem especificada.

Se você quiser capturar todos os erros, exceto por No XNUMXth pode ser usado SE ERRO:

=SEERRO(ÍNDICE(C2:C10,CORRESP(F1,A2:A10,0)), “Algo deu errado!”)

Mas lembre-se de que mascarar erros dessa maneira não é uma boa ideia, porque erros padrão relatam violações na fórmula.

Esperamos que você tenha achado nosso guia de uso da função INDEX MATCH() útil.