Última chance! Formação ilimitada com 50% de desconto
A promoção termina em
Geralmente, quando alguém aprende Excel pela primeira vez, é bem provável que o VLOOKUP seja apresentado como o principal método para tratamento de dados. O VLOOKUP é, de fato, uma função poderosa que resolve muitas tarefas de manipulação de dados, como mostramos em Data Wrangling with VLOOKUP in Spreadsheets. No mesmo tutorial, também cobrimos o básico da combinação INDEX-MATCH, outro método para fazer buscas verticais em tabelas que traz várias vantagens em relação ao VLOOKUP — com a contrapartida de uma fórmula um pouco mais complexa.
\nEssas vantagens incluem recursos como:
\n- \n
- Referências de coluna dinâmicas: permitem inserir e excluir colunas do seu conjunto de dados sem quebrar a função de busca. \n
- Maior velocidade em conjuntos de dados grandes: se suas tabelas são extensas, você provavelmente vai notar que o INDEX-MATCH é bem mais rápido que o VLOOKUP, pois o INDEX-MATCH considera apenas a coluna a ser retornada e a coluna de busca. \n
- Localização dos valores de busca: o VLOOKUP não consegue retornar valores que estejam à esquerda da primeira coluna selecionada no intervalo da tabela, enquanto o INDEX-MATCH pode olhar horizontalmente também, eliminando essa limitação. \n
- Tamanho do valor de busca: o VLOOKUP não busca valores com mais de 255 caracteres, mas o INDEX-MATCH consegue. \n
Além disso, o INDEX-MATCH permite fazer correspondência com base no valor de múltiplas colunas e também com diferenciação entre maiúsculas e minúsculas (case sensitive) — algo muito útil quando você lida com certos IDs que mudam conforme o uso de letras maiúsculas e minúsculas. Por isso, este tutorial vai se aprofundar nos recursos avançados da combinação INDEX-MATCH e mostrar exemplos práticos de como tirar proveito deles.
\nO básico do INDEX-MATCH
\nNa forma mais simples, o INDEX-MATCH pode ser usado como o VLOOKUP, para realizar buscas verticais simples com base em uma chave em comum. A estrutura básica da fórmula é a seguinte:
\n=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- \n
- column_range: o intervalo da coluna cujo valor você quer retornar. \n
- lookup_value: o valor que você quer buscar (a chave). \n
- lookup_column_range: o intervalo da coluna que contém as chaves que você quer corresponder. \n
- match_type: 0 ou 1. O 0 indica correspondência exata e 1 indica correspondência aproximada. É análogo ao parâmetro FALSE/TRUE do VLOOKUP. \n
Vamos ilustrar como o INDEX-MATCH funciona usando duas tabelas simples. A primeira contém alguns nomes e chaves únicas. A segunda contém as mesmas chaves (não necessariamente na mesma ordem) e algumas descrições. A tarefa é unir as descrições da segunda tabela aos nomes da primeira usando INDEX-MATCH. Veja as tabelas abaixo:
\n\n\n\n\n\n\n\n\n
\n
\n![\"table](\"https://media.datacamp.com/legacy/image/upload/v1633674267/toy-names_jra1ky.png\") | ![\"table](\"https://media.datacamp.com/legacy/image/upload/v1633674404/toy-keys_xx799u.png\") |
Como você pode ver, falta a descrição na primeira tabela. Agora vamos unir o nome à sua descrição usando INDEX-MATCH:
\n\nFácil, né? Como mostrado no vídeo, o primeiro passo é selecionar, dentro do INDEX, o column_range que contém os dados que queremos retornar. Neste caso, é a coluna Description (M2:M8). O segundo passo é selecionar, dentro do MATCH, o lookup_value, ou seja, a célula B2. Por fim, adicionamos ao MATCH o lookup_column_range (L2:L8) e definimos 0 como match_type, pois queremos uma correspondência exata do lookup_value. Depois é só apertar ENTER e arrastar a fórmula para baixo para finalmente unir os nomes às descrições.
\nAssim como o VLOOKUP, o INDEX-MATCH também pode ser usado para unir tabelas que estão em planilhas diferentes. Veja o exemplo no vídeo abaixo:
\n\nObservação: você deve ter notado que os intervalos de coluna que seleciono nos vídeos aparecem cercados por sinais $. Isso é para fixar os intervalos, evitando que mudem quando você arrasta as fórmulas vertical ou horizontalmente. Vou manter essa notação ao longo do tutorial.
\nAté aqui, tudo se comporta como um VLOOKUP simples, só que com um pouco mais de trabalho. Ainda assim, como mencionado no início, há várias vantagens em usar INDEX-MATCH. Uma das mais úteis é a possibilidade de usar referências de coluna dinâmicas — ou seja, você pode inserir colunas no intervalo onde está fazendo a busca sem afetar a própria busca.
\nSe adicionarmos uma nova coluna no nosso exemplo e estivermos usando VLOOKUP, teremos de reescrever a fórmula na tabela da esquerda para apontar para a descrição, que passaria a ser a coluna 3. Com o INDEX-MATCH, que trabalha com referências dinâmicas, isso não é necessário. A comparação está no vídeo abaixo:
\n\nO exemplo acima mostra como a coluna que fazia a busca com INDEX-MATCH se ajustou quando a nova coluna com os tipos de naves foi adicionada, enquanto a coluna que usava VLOOKUP não se ajustou. Essa vantagem do INDEX-MATCH sobre o VLOOKUP brilha de verdade quando você precisa buscar dados em grandes tabelas do Excel, como relatórios de vendas. Imagine que você tenha vários dashboards no Excel, todos apontando para uma única tabela com os dados. Se você usa VLOOKUP e um dia precisar inserir uma coluna no meio para adicionar um novo atributo, terá de atualizar as chamadas de VLOOKUP em todos os dashboards que dependem daquela tabela. Já se você construiu os dashboards usando INDEX-MATCH como método de busca, não vai passar por esse perrengue.
\nINDEX-MATCH avançado: correspondência que diferencia maiúsculas e minúsculas
\nReferências de coluna dinâmicas são muito úteis, mas não são a única vantagem do INDEX-MATCH. Também é possível aprimorar a sintaxe básica para realizar buscas que diferenciem maiúsculas e minúsculas (case sensitive). Para isso, vamos incluir a função EXACT dentro do MATCH. A sintaxe detalhada é a seguinte:
\n{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- \n
- data_range: o intervalo de dados de interesse que inclui a coluna de busca e a coluna desejada que você quer retornar. \n
- lookup_value: o valor que você quer buscar (a chave) \n
- lookup_column_range: o intervalo da coluna que contém as chaves que você quer corresponder \n
- match_type: 0 ou 1. O 0 indica correspondência exata e 1 indica correspondência aproximada. É análogo ao parâmetro FALSE/TRUE do VLOOKUP \n
- desired_column_number: o mesmo conceito do número do índice de coluna no VLOOKUP. É o índice da coluna que contém o dado que você quer obter. \n
Fazer buscas que diferenciam maiúsculas e minúsculas com essa versão do INDEX-MATCH é muito útil ao corresponder chaves com letras que mudam apenas pelo uso de maiúsculas/minúsculas (por exemplo, ter duas chaves diferentes, \"00567UUp\" e \"00567UUP\"). Situações assim aparecem, por exemplo, no Salesforce CRM ao tentar corresponder Account IDs ou IDs de oportunidades. Aqui não vamos usar dados do Salesforce, mas sim uma versão adaptada das nossas tabelas de exemplo no vídeo abaixo para mostrar como a fórmula funciona na prática.
\nObservação: repare que a combinação de funções acima está entre chaves. Isso indica que é uma fórmula matricial e deve ser inserida pressionando Ctrl+Shift+Enter.
\n\nComo você viu, a sintaxe pode ficar longa, mas o resultado é o esperado. Ela busca os valores como um INDEX-MATCH normal, só que levando em conta o caso das letras. A \"mágica\" acontece na função EXACT, que retorna uma matriz de TRUE e FALSE. Onde houver TRUE nessa matriz, significa que existe um valor exatamente com o mesmo caso. Depois disso, o MATCH encontra a posição da primeira ocorrência de TRUE.
\nÉ essencial pressionar Ctrl+Shift+Enter ao finalizar a fórmula. Caso contrário, você receberá um erro #N/A difícil de diagnosticar.
\nINDEX-MATCH avançado: correspondência com base em múltiplos critérios
\nBuscar diferenciando maiúsculas e minúsculas no Excel é bem legal, mas há um recurso ainda melhor: fazer buscas considerando vários critérios ao mesmo tempo. Levar em conta múltiplos critérios é algo comum em funções condicionais de contagem ou soma, como COUNTIFS ou SUMIFS, mas é menos usual em funções de busca. Ainda assim, pode ser muito vantajoso em algumas situações. Por exemplo, em um arquivo de RH no Excel, você pode querer buscar o salário de um funcionário com base no primeiro E no último nome. Se considerar só uma das duas colunas, é possível retornar o salário da pessoa errada, já que pode haver vários funcionários com o mesmo primeiro ou último nome. Ao usar os dois, a chance de acerto é muito maior.
\nDito isso, veja a estrutura do INDEX-MATCH levando em conta múltiplos critérios:
\n{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
No geral, a fórmula tem os mesmos componentes do INDEX-MATCH tradicional, mas agora aceita $n$ valores de busca com seus respectivos intervalos. Se precisar relembrar os componentes, volte à sintaxe básica do INDEX-MATCH. O motivo de usarmos \"1\" no MATCH é que essa fórmula cria matrizes de 1s e 0s que representam as correspondências de cada critério desejado. Em seguida, o MATCH retorna a posição do primeiro 1 encontrado.
\nAssim como na fórmula sensível a maiúsculas/minúsculas, esta também está entre chaves, o que significa que você deve pressionar Ctrl+Shift+Enter ao finalizar, pois é uma fórmula matricial.
\nVamos ver um exemplo prático tentando selecionar naves espaciais que pertencem a uma determinada raça e que têm uma velocidade máxima de dobra específica no vídeo abaixo:
\n\nVoilà! Agora você tem as ferramentas para fazer buscas com base em múltiplas chaves. Lembre que, neste exemplo simples, usei apenas duas — a chave e a velocidade máxima —, mas, em teoria, você pode seguir adicionando critérios. Voltando ao exemplo de RH, você poderia corresponder por primeiro e último nome e também por cargo, reduzindo o risco de trazer o salário da pessoa errada.
\nConclusão
\nParabéns! Você agora domina o INDEX-MATCH e tudo o que dá para fazer com ele além de uma busca vertical simples. Se em algum momento você precisar cuidar de relatórios complexos no Excel, essa combinação de funções provavelmente vai virar sua melhor aliada. Como muitos, eu aprendi VLOOKUP na escola, mas, quando comecei a usar Excel a sério — como em buscas sensíveis a maiúsculas/minúsculas de IDs de contas no Salesforce —, percebi que o INDEX-MATCH era uma opção muito melhor no longo prazo, apesar de a fórmula ser mais trabalhosa e sujeita a erros ao digitar. Minha dica: experimente na próxima vez que precisar fazer uma busca vertical no Excel. Vai que você também pega gosto!
\nSe quiser aprender ainda mais, confira o curso Data Analysis with Spreadsheets da DataCamp.
