A função VLOOKUP (Procv), embora seja uma das funções mais utilizadas no Excel e bastante útil em diversas situações, apresenta algumas limitações, que podem ser ultrapassadas com a utilização de outras funções.
Uma das grandes limitações da função VLOOKUP (Procv) é o facto de apenas permitir procurar valores da esquerda para a direita, ou seja, a coluna que contém o valor que precisa de procurar tem de estar localizada à esquerda da coluna que contém o valor a devolver.
Nesse sentido, há duas formas de contornar esta limitação e daí a evolução da função Vlookup (Procv) no Excel:
- Combinar as funções INDEX (Índice) e MATCH (Corresp);
- Função XLOOKUP (Procx).
Conhece alguma destas funções? De seguida pode perceber melhor como funciona cada uma.
As limitações do Vlookup (Procv)
Para exemplificar as limitações da função Vlookup (Procv), iremos considerar a base de dados indicada abaixo:
O objetivo será extrair desta base de dados algumas informações específicas, conforme os exemplos seguintes:
Exemplo 1 – Qual o comercial associado aos clientes indicados abaixo?
Neste caso, como a coluna “Comercial” (informação que queremos obter) está à direita da coluna “Cliente”, é possível utilizar a função Vlookup (Procv), com os argumentos necessários:
Conforme a sintaxe indicada abaixo:
=VLOOKUP(lookup_value,table_array,col_index,num,[range_lookup])
=PROCV(valor_proc,matriz_tabela,núm_índice_coluna,[intervalo_pesquisa])
A célula com o valor a pesquisar – J2, o intervalo que contém os dados – C3:G52, nº da coluna que contém o valor que queremos – 3, correspondência exata – 0.
Se ainda não estiver à vontade com esta função e quiser ver mais exemplos, pode ler este artigo.
E obtemos o seguinte resultado:
Exemplo 2 – Qual o país de cada uma das cidades indicadas?
Como a coluna “País” (informação que queremos obter) está à esquerda da coluna “Cidade”, não podemos utilizar a função Vlookup (Procv) pois esta apenas nos permite procurar valores da esquerda para a direita:
Para contornar esta limitação, conforme referido anteriormente, podemos utilizar a primeira opção sugerida: combinar as funções INDEX (Índice) e MATCH (Corresp).
Combinar as funções INDEX (Índice) e MATCH (Corresp)
A função INDEX (Índice) devolve a interseção de uma linha com uma coluna.
Para o exemplo da cidade “Berlim” (que se encontra na primeira linha da base de dados), em que queremos descobrir o país (resultado estará na coluna “País”), a função INDEX (Índice) irá devolver a interseção entre ambas, Alemanha (indicado a verde), conforme imagem abaixo:
Na sintaxe da função INDEX (Índice) temos de considerar a seguinte informação:
=INDEX(array, row_num, [column_num])
=ÍNDICE(matriz, núm_linha, [núm_coluna])
Para o intervalo, consideramos a base de dados toda; o número da linha (que contém a informação da cidade); e o número da coluna onde se encontra o resultado que se pretende obter:
No entanto, para não ter de introduzir manualmente os números das linhas e colunas, pode (e deve!) utilizar a função MATCH (Corresp).
A função MATCH (Corresp) devolve a posição (e não o conteúdo) de um item num intervalo.
Na sintaxe da função MATCH (Corresp) tem de incluir os seguintes argumentos:
=MATCH(lookup_value, lookup_array,[ match_type])
=CORRESP(valor_proc, matriz_proc, [tipo_corresp])
Ou seja, qual o item a procurar (que pode ser um valor ou referência a uma célula), o intervalo onde se pretende pesquisar o item e o tipo de correspondência (que, na maior parte dos casos, é 0 ou FALSE, que corresponde à correspondência exata).
Para nunca se esquecer de como utilizar, lembre-se que quando quer descobrir o número de uma linha, seleciona uma coluna. Quando quer descobrir o número de uma coluna, seleciona uma linha (normalmente a linha do cabeçalho):
Deste modo, pode utilizar os resultados da função MATCH (Corresp) como argumentos da função INDEX (Índice), decompondo (como indicado acima) ou diretamente:
Assim, com esta combinação de funções conseguimos ultrapassar a limitação da função VLOOKUP (Procv) de não conseguir procurar da direita para a esquerda.
Nota: A função MATCH (Corresp) é também muito útil para utilizar no terceiro argumento da função Vlookup (Procv), pois assim não precisa colocar manualmente o número da coluna e evita erros (caso adicione novas colunas ou altere a ordem, por exemplo).
A função XLOOKUP (Procx)
A função XLOOKUP (Procx) apenas está disponível na versão Office 2019 e 365 e representa a fase mais avançada da evolução da função Vlookup (Procv) no Excel.
A função XLOOKUP (Procx) permite obter o mesmo resultado que a combinação das funções INDEX (Índice) e MATCH (Corresp), mas de forma muito mais simples.
É a mais recente alternativa à combinação das funções INDEX (Índice) e MATCH (Corresp) e permite ultrapassar não só a limitação da função VLOOKUP (Procv) de só procurar um valor da esquerda para a direita, mas ainda outras:
- Pesquisa em qualquer coluna. O valor a pesquisar não tem de estar na primeira coluna da tabela como no VLOOKUP (Procv);
- Pode pesquisar por ordem inversa.
Em muitos casos, com apenas esta função pode juntar o que faria com as funções VLOOKUP (Procv), HLOOKUP (Proch), MATCH (Corresp) e INDEX (Índice).
Vamos a mais um exemplo.
Exemplo 3 – Qual o comercial de cada cidade?
A função XLOOKUP (PROCX) tem a seguinte sintaxe:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=PROCX(valor_ pesquisa, matriz_ pesquisa, matriz_devolver, [se_não_for_encontrado], [modo_corresp], [modo_pesquisa])
Isto é, precisa do item a procurar (para este exemplo: a cidade específica), intervalo onde se pretende pesquisar o item (coluna “Cidade”), intervalo que contém os valores que serão devolvidos (coluna “Comercial”), valor a apresentar se não for encontrada nenhuma correspondência, tipo de correspondência (0, se for um valor exato), modo de pesquisa (se procura do início para o fim ou ao contrário).
Como pode ver, é muito mais fácil obter o resultado pretendido usando esta função. No entanto, caso não a tenha disponível na sua versão, tem sempre a alterativa de usar a combinação INDEX+MATCH (Índice+Corresp).
Na Live Funções EXCELentes, cuja gravação ainda está disponível no Youtube, falei sobre estas funções. Por isso, se preferir pode ter acesso a este conteúdo em vídeo, a partir do minuto 57:08):
Gostava de conhecer alguns exemplos de como pode utilizar esta função? Conte-me nos comentários se já teve dificuldades em utilizar a função XLOOKUP (Procx) ou até mesmo se já a utiliza.