Veja por que eu uso XLOOKUP

Como entusiasta de longa data do Excel, sempre gostei de usar VLOOKUP, uma das funções de pesquisa mais conhecidas do Excel. No entanto, a introdução do XLOOKUP pela Microsoft em 2019 mudou tudo. Depois que percebi o quão útil o XLOOKUP é, soube que nunca mais olharia para trás.

Usarei referências diretas de células para exemplificar meus pontos neste artigo, pois são mais claras do que referências estruturadas. Também não falarei explicitamente sobre PROCV porque — além de sua direção — ele funciona exatamente da mesma maneira que PROCV.

As sintaxes: XLOOKUP e VLOOKUP

Antes de explicar por que prefiro XLOOKUP a VLOOKUP, mostrarei como eles funcionam.

XLOOKUP

XLOOKUP tem seis argumentos:

=XLOOKUP(a,b,c,d,e,f)

onde

• um (obrigatório) é o valor de pesquisa,
• b (obrigatório) é a matriz de pesquisa,
• c (obrigatório) é a matriz de retorno,
• d (opcional) é o texto a ser retornado se o valor de pesquisa (um) não foi encontrado na matriz de pesquisa (b ),
• e (opcional) é o modo de correspondência, e
• f (opcional) é o modo de pesquisa.

Neste exemplo, quero que o Excel procure o ID do funcionário com base no nome na célula H1 e retorne o resultado para a célula H2.

Para fazer isso, digitarei a seguinte fórmula na célula H2:

=XLOOKUP(H1,B2:B12,A2:A12,"Invalid name",0,1)

Neste caso, a célula H1 contém o valor que o Excel precisa procurar (Mary), B2 a B12 é onde esse valor pode ser encontrado (nomes dos funcionários), A2 a A12 é onde o resultado correspondente será extraído (IDs dos funcionários ) e “Nome inválido” é o que desejo que o Excel retorne se o valor de pesquisa não estiver em nenhum lugar da matriz de pesquisa.

Eu optei pelos valores padrão para argumentos e e f porque quero uma correspondência exata e quero que o Excel pesquise no topo da matriz de pesquisa (mais sobre isso mais tarde).

PROCV

Aqui está a sintaxe VLOOKUP, que possui quatro argumentos:

=VLOOKUP(a,b,c,d)

onde

• um (obrigatório) é o valor de pesquisa,
• b (obrigatório) é a matriz de pesquisa e retorno,
• c (obrigatório) é o número do índice da coluna e
• d (opcional) é o modo de correspondência.

Neste exemplo, quero que o Excel pesquise a nacionalidade com base no ID do funcionário na célula H4 e retorne o resultado para a célula H5.

Para fazer isso, na célula H5, digitarei

=VLOOKUP(H4,A2:E12,5,FALSE)

como a célula H4 contém o valor da pesquisa (ID 3264), as células A2 a E12 são onde o Excel precisa encontrar esse valor e o retorno correspondente, a quinta coluna (o país) é o array em que o resultado será encontrado, e eu quero uma correspondência exata (FALSO).

Não há necessidade de contar colunas

Uma diferença importante entre VLOOKUP e XLOOKUP é que o primeiro exige que eu especifique um número de índice da coluna onde o resultado será encontrado, enquanto o último não. Isso ocorre porque VLOOKUP combina as matrizes de pesquisa e retorno em um argumento, enquanto XLOOKUP as define em dois argumentos separados.

PROCV

Ter que especificar o número do índice da coluna em VLOOKUP pode levar a vários problemas:

• É fácil acidentalmente contar incorretamente as colunas, especialmente se sua matriz de pesquisa tiver centenas de colunas.

• Adicionar ou remover colunas pode afetar a precisão do número do índice da coluna.

• A matriz de pesquisa deve estar na coluna mais à esquerda e a matriz de retorno deve estar à direita. Isso restringe a versatilidade do VLOOKUP.

XLOOKUP

Por outro lado, XLOOKUP contém o array de pesquisa como um argumento separado, o que significa que você pode apreciar os seguintes benefícios:

• Não há contagem envolvida! Basta usar o mouse para selecionar a matriz de retorno quando chegar a essa parte da fórmula. Isso economiza tempo e ajuda na precisão.

• Como a matriz de retorno está dentro de um intervalo especificado de células, remover ou adicionar colunas à planilha não afetará a fórmula XLOOKUP.

• A matriz de retorno pode estar em qualquer lado da matriz de pesquisa, o que significa que XLOOKUP é mais versátil que VLOOKUP.

Opções de correspondência mais aproximadas

Tanto VLOOKUP quanto XLOOKUP podem retornar uma correspondência exata (o valor correspondente exato em uma linha) e uma correspondência aproximada (um valor correspondente próximo).

PROCV

Mais especificamente, a correspondência aproximada de VLOOKUP (indicada por TRUE na fórmula) pesquisa a matriz de pesquisa até encontrar um valor maior que o valor de pesquisa. Em seguida, ele retorna o valor que está uma linha acima.

Considere a pontuação de 65 do Aluno D no exemplo abaixo. VLOOKUP pega o valor de pesquisa 65, analisa a matriz de pesquisa, encontra o primeiro valor maior que o valor de pesquisa (nesse caso, 70) e retorna a nota da linha acima (nota C).

Isto apresenta duas desvantagens. Primeiro, a matriz de pesquisa deve ser listada em ordem crescente. Segundo, preciso adicionar uma linha FAIL à matriz de pesquisa, pois não tenho a opção em VLOOKUP de indicar um argumento não correspondente.

XLOOKUP

Por outro lado, XLOOKUP oferece três alternativas para a correspondência aproximada unidimensional do VLOOKUP:

• -1: retorna o próximo menor valor na matriz de pesquisa se não houver correspondência exata.

• 1: retorna o próximo maior valor na matriz de pesquisa se não houver correspondência exata.

• 2: usa curingas para permitir pesquisas mais flexíveis.

Novamente, tomemos o Aluno D como exemplo. Com uma pontuação de 65, o Excel verá que o próximo menor valor na matriz de pesquisa é 60 e o próximo maior valor é 70. Como o aluno ainda não atingiu o limite para a nota B (70), preciso do Excel para pegue o próximo menor valor na matriz de pesquisa (60) para retornar a nota C da matriz de retorno. Então, vou digitar -1 como a opção de correspondência na fórmula.

Isso significa que a matriz de pesquisa não precisa estar em ordem – o Excel vasculha toda a matriz de pesquisa para encontrar os valores mais altos ou mais baixos mais próximos, se não houver uma correspondência exata. Também posso omitir a linha FAIL da minha matriz de pesquisa porque, se a pontuação de um aluno não corresponder a nenhuma nota, posso usar o quarto argumento na sintaxe XLOOKUP para retornar a palavra FAIL.

Mais modos de pesquisa (direções)

Enquanto VLOOKUP pesquisa do primeiro ao último, retornando o primeiro valor correspondente, XLOOKUP oferece quatro opções de pesquisa.

PROCV

Na maioria dos cenários de pesquisa, pesquisar a matriz de pesquisa do primeiro ao último retornará os resultados necessários. Por exemplo, se você tiver um diretório de números de telefone e nomes de pessoas, usar VLOOKUP para localizar o número de telefone com base no nome inserido funcionará perfeitamente, pois o nome dessa pessoa provavelmente aparecerá apenas uma vez.

XLOOKUP

No entanto, XLOOKUP permite escolher a orientação da pesquisa:

• 1: Pesquise do primeiro ao último

• -1: Pesquise do último ao primeiro

• 2: Pesquisa binária (com a matriz de pesquisa em ordem crescente)

• -2 Pesquisa binária (com a matriz de pesquisa em ordem decrescente)

A vantagem de uma pesquisa do último ao primeiro é que você pode encontrar a ocorrência mais recente de um valor em uma matriz de pesquisa listada em ordem de data. Para conseguir isso com VLOOKUP, você teria que primeiro inverter a ordem dos dados.

Definir a saída de erro

Um recurso realmente útil do XLOOKUP é o “argumento se não encontrado”, que o VLOOKUP não possui.

PROCV

Se um valor não for encontrado em uma fórmula VLOOKUP de correspondência exata, o Excel retornará a temida mensagem de erro #N/A. Para corrigir isso, sempre incorporei funções VLOOKUP na função IFERROR para poder definir a saída se VLOOKUP não encontrar uma correspondência.

=IFERROR(VLOOKUP(B6,$E$2:$F$8,2,TRUE)," ")

​​Embora esta seja uma solução razoável, ela torna a escrita de fórmulas muito mais complexa e pode ocultar problemas que podem afetar a precisão da sua análise de dados.

XLOOKUP

Como o XLOOKUP já vem com um argumento “se não encontrado”, você pode definir o que acontece se o valor não aparecer na pesquisa, evitando que você tenha que incorporar a fórmula dentro do IFERROR.

Retornar uma matriz derramada

Uma das propriedades mais teimosas do VLOOKUP é que ele só pode retornar uma única correspondência, enquanto o XLOOKUP pode retornar um intervalo.

XLOOKUP

Neste exemplo, digitando

=XLOOKUP(I1,A2:A7,B2:F7)

procura o valor na célula I1 (neste caso, Quizpicable Me), procura esse valor nas células A2 a A7 e retorna todos os valores correspondentes como uma matriz dividida.

PROCV

Se eu tentasse replicar isso usando VLOOKUP, digitaria

=VLOOKUP(I1,A2:F7,2:6)

mas isso retorna um #REF! erro porque o terceiro argumento (número de índice da coluna) pode ser apenas um único dígito, não um intervalo. Isso significa que XLOOKUP é muito mais adaptável, pois pode retornar um único valor ou um intervalo, dependendo dos parâmetros adicionados à fórmula.

---

Como as versões anteriores a 2019 do Excel não suportam XLOOKUP, não descarte VLOOKUP e HLOOKUP completamente! Ainda pode haver casos em que você precise usá-los, como se você estivesse enviando uma planilha para alguém que não atualiza seu pacote Office há alguns anos.