Sobre o XLOOKUP (ou PROCX)

Tudo o que precisas de saber para simplificar as tuas procuras no Excel

XLOOKUP

No top das 10 funções de Excel mais populares está incluída a função que, para muitos, é a mais temida do Excel: o VLOOKUP (ou PROCV em português).

Mas, para mim, o “novo super herói” do Excel é o XLOOKUP (ou PROCX em português) e esta função promete facilitar a vida de muitos usuários.

Esta função é uma espécie de mix de várias funções:
   LOOKUP, VLOOKUP ou HLOOKUP (PROC, PROCV ou PROCH em português);
   +
   IFNA (SEND em português);
   +
   SORT (ORDENAR em português).

Vantagens do XLOOKUP:
   Funciona em qualquer direção (para cima, para baixo, para a esquerda ou para a direita);
   Devolve as combinações exatas por predefinição;
   Podes escolher o resultado quando não há combinação;
   Devolve a 1.ª ou a última combinação;
   Transforma as procuras mais fáceis e simples.

Desvantagens do XLOOKUP:
   Pessoalmente só vejo uma – não está disponível na versão do Excel 2019.

Continua a ler e vê se concordas com estas vantagens ou se encontras alguma outra desvantagem.

Como funciona o XLOOKUP?

Esta função procura um intervalo ou uma matriz e devolve o item correspondente que está noutro intervalo ou matriz.

Torna-se bastante útil para localizar itens numa tabela. Por exemplo, procurar a taxa de imposto para determinado nível de rendimento:

1. exemplo XLOOKUP

Esta função tem 3 argumentos obrigatórios e 3 facultativos:

2. sintaxe XLOOKUP

E para perceber como funcionam, sou fã de exemplos, por isso cá vai:

Num 1.º exemplo imaginemos que temos de descobrir a zona de cada colaborador tendo em conta o departamento em que trabalham.

Os departamentos existentes na empresa são 4 e estão na mesma folha da lista de colaboradores, numa tabela à esquerda:

3. exemplo

Na prática, quando o departamento do colaborador é o Financeiro, queremos que o Excel nos devolva a zona Norte, e assim sucessivamente.

A ideia é:
1) introduzir a fórmula com o XLOOKUP numa célula (no caso, iremos introduzi-la na célula G7); e
2) copiar o conteúdo dessa célula para as células abaixo.

1 lookup_value ou valor_pesquisa

Aqui deves indicar o valor a procurar.

4. argumento 1 XLOOKUP
exemplo do 1.º argumento da função XLOOKUP (ou PROCX)

No nosso exemplo, o 1.º departamento a procurar é o Financeiro.

Como estamos na célula G7, vamos selecionar/identificar a célula F7.

2 lookup_array ou matriz_pesquisa

O 2.º argumento desta função é a lista ou o intervalo onde está o valor a pesquisar.

4. argumento 2 XLOOKUP
exemplo do 2.º argumento da função XLOOKUP (ou PROCX)

Ou seja, no exemplo queremos encontrar o departamento Financeiro na lista de departamentos que estão nas células B7 a B10.

Algumas considerações sobre as quais podes saber mais neste artigo:
    para fazeres uma referência a um intervalo, identifica a 1.ª célula do intervalo, coloca dois pontos e identifica a última célula desse intervalo;
   neste caso vais querer copiar a fórmula da célula G7 e a ideia é o intervalo dos departamentos não alterar – para que isso não aconteça, utiliza referências absolutas (ou seja, coloca o símbolo $ conforme vês na imagem).

return_array ou matriz_devolver

Neste argumento tens de indicar ao Excel onde está a lista ou o intervalo de dados que contém o valor a devolver.

4. argumento 3 XLOOKUP
exemplo do 3.º argumento da função XLOOKUP (ou PROCX)

No exemplo queremos que o Excel nos devolva as zonas correspondentes a cada departamento e que estão nas células C7 a C10.

Nota: as considerações referidas acima também se aplicam aqui.

Estes são os 3 argumentos obrigatórios.

Conforme referido na introdução deste artigo, uma das vantagens do XLOOKUP é que funciona em qualquer direção uma vez que se baseia em intervalos em vez de tabelas (como é o caso do VLOOKUP).

Outra vantagem é devolver as combinações exatas por predefinição.

Ou seja, se a tua procura consiste numa correspondência exata (isto é, a um valor que procuras existe um valor a devolver) não precisas de indicar mais nada para a função funcionar.

No entanto, existem argumentos adicionais que podes usar quando:
    queres escolher um resultado nos casos em que a função não encontra uma correspondência (4.º argumento);
   queres que o Excel faça uma correspondência aproximada, ou, por outras palavras, devolva o valor mais próximo ao item maior ou menor daquele que encontrar (5.º argumento);
    queres fazer uma procura de forma ascendente ou descendente (6.º argumento).

Voltando ao exemplo acima: quando terminas a função, reparas que existem colaboradores que não têm uma zona atribuída e que têm o erro #N/A.

5. N_A no XLOOKUP

Imaginemos que isto acontece porque não fazem parte da empresa, ou seja, são pessoas que colaboram com a empresa em regime de outsourcing.

Vejamos como é que o 4.º argumento nos ajuda a tratar dos #N/A!

4 [if_not_found] ou [se_não_for_encontrado]

Indica neste argumento o que queres que a função devolva nos casos em que não exista uma correspondência.
(caso não preenchas este argumento e não exista uma correspondência na pesquisa, a função devolve #N/A)

6. argumento 4 XLOOKUP
exemplo do 4.º argumento da função XLOOKUP (ou PROCX)

Quando o departamento não existe, queremos que o Excel nos devolva sempre a palavra Outsourcing.

Nota: como se trata de uma expressão de texto, temos de a colocar entre aspas.

Comentário sobre este argumento: A meu ver, este argumento só fará sentido quando queremos fazer uma correspondência exata.

Ou seja, este argumento acaba por ser um pouco redundante quando indicamos, no próximo argumento, que queremos outro tipo de correspondência (como será o caso do próximo exemplo).

Isto porque, quando queremos fazer uma correspondência aproximada, já estamos a dizer ao Excel o que fazer quando não encontra o resultado exato na coluna a devolver.

Para melhor te explicar o próximo argumento, precisamos de recorrer a um outro exemplo.

Neste exemplo, temos de descobrir a taxa de IRS que se irá aplicar a cada colaborador tendo em conta o rendimento obtido num determinado ano.

As taxas de IRS aplicáveis dependem do nível de rendimento e estão na mesma folha da lista de colaboradores, numa tabela à esquerda.

7. exemplo IRS

Na prática, quando o colaborador recebe entre 0 e 7.116 euros, a taxa de IRS é de 14,5%, e assim sucessivamente.

Ou seja, o valor de rendimentos recebido por cada colaborador (e que vamos procurar), na maioria das vezes, não está na tabela.

Tal como no exemplo anterior, a ideia é:
1) introduzir a fórmula com o XLOOKUP numa célula (no caso, a H7); e
2) copiar o conteúdo dessa célula para as células abaixo.

Nota: para este artigo não se tornar ainda mais massudo, não irei repetir os argumentos referidos anteriormente. Apenas te dou nota do seguinte:
    o 1.º valor a procurar está na célula G7;
   o intervalo onde está o valor a pesquisar pode estar nas células B8 a B16 ou C8 a C16 (podemos usar um ou outro dependendo do que colocarmos no 5.º argumento);
    o intervalo das taxas a devolver está nas células D8 a D16;
    como vamos fazer uma correspondência aproximada, não preenchemos o 4.º argumento.

5 [match_mode] ou [modo_corresp]

Neste argumento podes indicar ao Excel como é que queres que se faça a correspondência, ou seja, coloca:
   0 (zero) se queres uma correspondência exata (colocar 0 ou não preencher tem o mesmo efeito prático uma vez que este argumento é facultativo);
   -1 (menos um) se queres uma correspondência exata ou o próximo item menor;
   1 (um) se queres uma correspondência exata ou o próximo item maior.

8.1 argumento 5 XLOOKUP
exemplo do 5.º argumento da função XLOOKUP (ou PROCX)

Se colocarmos no 2.º argumento as células B8 a B16, teremos de colocar neste argumento -1 (correspondência exata ou o próximo item menor).

Vejamos o caso da Maria:
   a Maria tem um rendimento anual de 9.769 euros;
   no entanto, no intervalo de rendimentos selecionado, apenas temos os valores de 7.117 e 10.737 euros;
   ou seja, o Excel não tem uma correspondência exata para os 9.769 euros que a Maria recebe;
   então, o que queremos é que o Excel procure o valor imediatamente inferior aos 9.769 euros (no caso, para os 7.117 euros);
   e nos devolva a taxa correspondente (23%).

De notar que, por defeito, o Excel procura os valores ou as correspondências de cima para baixo.

Isto é, se o que procuramos está repetido no intervalo da procura, o Excel devolve a correspondência do 1.º valor que encontrar.
(se quiseres que o Excel faça a procura de baixo para cima, podes alterar esta predefinição no próximo argumento)

8.2 argumento 5 XLOOKUP
exemplo do 5.º argumento da função XLOOKUP (ou PROCX)

Por outro lado, se colocarmos no 2.º argumento as células C8 a C16, teremos de colocar neste argumento 1 (correspondência exata ou o próximo item maior).

Voltando ao caso da Maria, o que queremos é que o Excel procure o valor imediatamente superior aos 9.769 euros (no caso, para os 10.737 euros).

6 [search_mode] ou [modo_pesquisa]

Por fim, neste argumento indicas ao Excel como queres que seja feita a pesquisa:
   não coloques nada ou coloca 1 (um) se quiseres pesquisar do 1.º ao último item;
   coloca -1 (menos 1) se quiseres pesquisar do último item para o 1.º.

Como vimos, esta função é muito versátil e facilita e simplifica as procuras.

No entanto, as possibilidades vão para além do que refiro neste artigo.

Se quiseres saber mais como podes tirar partido desta e de outras funções no Excel, segue os conteúdos da Data Simplify e/ou entra em contacto comigo!