XLOOKUP() vs. VLOOKUP(): Uma comparação para usuários do Excel

Sintaxe de XLOOKUP() e VLOOKUP()

Agora que você tem uma compreensão básica de ambas as funções, vamos entender sua sintaxe e como usá-las em aplicativos reais. 

Sintaxe de VLOOKUP()

Vamos dar uma olhada na sintaxe do VLOOKUP(). 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Na fórmula acima:

• lookup_value é o valor que você deseja pesquisar. Pode ser um número, um texto ou uma referência a uma célula que contenha o valor da pesquisa.

• table_array é o intervalo de células que contém os dados. A primeira coluna desse intervalo deve conter o endereço search_key.

• col_index_num é o número da coluna no intervalo do qual você deseja recuperar o valor. 

• range_lookup é onde você insere um valor lógico (TRUE ou FALSE).

Sintaxe de XLOOKUP()

Vamos agora dar uma olhada na sintaxe do xlookup(). Como você pode ver, XLOOKUP() tem mais argumentos do que VLOOKUP().

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Na fórmula acima:

• lookup_value é o valor que você deseja pesquisar.

• lookup_array é o intervalo de células em que o valor de pesquisa é pesquisado.

• return_array é o intervalo de células que contém o valor que você deseja retornar.

• if_not_found (opcional) retorna um valor se nenhuma correspondência for encontrada. Se o valor não for especificado, você retornará #N/A.

• match_mode (opcional) determina o tipo de correspondência a ser realizada. 0 é para correspondência exata (padrão). -1 é para correspondência exata ou para o próximo item menor. 1 é para correspondência exata ou para o próximo item maior. 2 é para correspondência de caracteres curinga.

• search_mode (opcional) determina o modo de pesquisa a ser usado. 1 é usado para pesquisar do primeiro ao último (padrão). -1 é usado para pesquisar do último ao primeiro. 2 é usado para executar a pesquisa binária em ordem crescente. -2 é usado para executar a pesquisa binária em ordem decrescente.

Principais diferenças: XLOOKUP() vs. VLOOKUP()

Vamos agora explorar algumas diferenças importantes entre as funções XLOOKUP() e VLOOKUP().

Modo de correspondência exata 

XLOOKUP() O padrão é o modo de correspondência exata, mas VLOOKUP() requer a especificação de FALSE para uma correspondência exata.

Diferença de correspondência exata entre XLOOKUP() e VLOOKUP(). Fonte: Imagem do autor.

Em VLOOKUP(), não especifiquei o valor range_lookup (4º argumento), portanto, ele forneceu a correspondência mais próxima, o que não é o resultado desejado. XLOOKUP() Por outro lado, o Google, por padrão, retorna a correspondência exata.

Direção de pesquisa

VLOOKUP() é restrito à pesquisa apenas à direita da primeira coluna na matriz da tabela selecionada. Mas o site XLOOKUP() pode buscar valores em qualquer direção.

Diferença de direção de pesquisa entre VLOOKUP() e XLOOKUP(). Fonte: Imagem do autor.

Aqui, o VLOOKUP() não encontrou as notas porque só pode pesquisar dados à direita do lookup_value (nome do aluno). Em comparação, o XLOOKUP() encontra as notas para o Robin porque ele pode pesquisar em qualquer direção (esquerda ou direita).

Referências de matriz

Em VLOOKUP(), você precisa definir todo o intervalo de dados (matriz da tabela) e especificar o número da coluna (índice da coluna) que contém a saída desejada (coluna de retorno) em uma única fórmula. XLOOKUP() é mais flexível do que isso. Ele permite que você defina matrizes separadas para o valor de pesquisa e os dados que deseja retornar. 

Referências de matriz entre VLOOKUP() e XLOOKUP(). Fonte: Imagem do autor.

Pesquisa horizontal

VLOOKUP() não permite que você faça pesquisas horizontais. Se você quiser fazer uma pesquisa horizontal, terá de usar HLOOKUP(). XLOOKUP() consolida essas duas funções porque executa pesquisas verticais e horizontais, de modo que você não precisa usar funções separadas para diferentes direções de pesquisa.

Diferença de pesquisa horizontal entre VLOOKUP() e XLOOKUP(). Fonte: Imagem do autor.

Manipulação de inserções/exclusões de colunas 

As alterações na coluna afetam VLOOKUP() por causa do código rígido column_index_num. No entanto, o site XLOOKUP() não é afetado pelas alterações de coluna. Ele continua funcionando sem ajustes na fórmula.

XLOOKUP() não é afetado, enquanto VLOOKUP() falha. Fonte: Imagem do autor.

Classificação e pesquisa

Você pode classificar VLOOKUP() em ordem crescente, mas isso causaria dificuldades ao lidar com dados não classificados. No entanto, o XLOOKUP() pode usar o argumento search_mode para pesquisar em ordem ascendente e descendente.

Tratamento de inserção/exclusão em XLOOKUP() e VLOOKUP(). Fonte: Imagem do autor.

Mensagens de erro personalizadas

VLOOKUP() mostra um sinal de erro #N/A quando nenhuma correspondência é encontrada. XLOOKUP() tem um parâmetro opcional if_not_found que permite que você personalize o texto de saída se um valor não for encontrado. 

Personalização de mensagens de erro em XLOOKUP() e VLOOKUP(). Fonte: Imagem do autor.

Você pode ver que o site VLOOKUP() não conseguiu encontrar o aluno em sua lista e exibiu um erro #N/A. Da mesma forma, a função XLOOKUP() não conseguiu localizar o nome do aluno. No entanto, diferentemente de VLOOKUP(), XLOOKUP() retorna uma mensagem específica quando não há correspondência.

Retornar vários valores

VLOOKUP() pode retornar apenas um valor de cada vez. Mas o site XLOOKUP() pode recuperar valores de várias colunas simultaneamente. É por isso que você pode usar uma fórmula XLOOKUP() em vez de várias fórmulas VLOOKUP().

Retorno de vários valores com XLOOKUP() e VLOOKUP(). Fonte: Imagem do autor.

Modo de pesquisa

VLOOKUP() examina uma lista desde o início e retorna apenas o primeiro valor que corresponde ao que você deseja. Mas o XLOOKUP() pode examinar uma lista em qualquer direção (de cima para baixo ou de baixo para cima) e encontrar itens rapidamente em listas extensas.

Diferença entre VLOOKUP() e XLOOKUP(). Fonte: Imagem do autor.

Aqui, o site VLOOKUP() recupera a primeira ocorrência, que é do primeiro semestre. Entretanto, com XLOOKUP(), uso o código do modo de pesquisa -1 para pesquisar de baixo para cima.

Exemplos práticos e casos de uso

Vamos agora dar uma olhada em alguns exemplos práticos e casos de uso.

Usando uma pesquisa sem precisar reorganizar uma tabela

Tenho uma lista de nomes e pontuações de alunos e quero encontrar a pontuação de cada aluno com base em seu nome. Para isso, usarei as funções XLOOKUP() e VLOOKUP() para mostrar a você as diferenças. 

Ao usar o site XLOOKUP(), insiro a seguinte fórmula:

=XLOOKUP(D8,B2:B5,A2:A5)

Nessa fórmula:

• D8 contém o valor de pesquisa que é Charlie.

• B2:B5 contém os nomes dos alunos. A função XLOOKUP() procurará o valor em D8 dentro desse intervalo.

• A2:A5 é o intervalo que contém as pontuações correspondentes aos nomes dos alunos. 

Ao usar o site VLOOKUP(), insiro a seguinte fórmula:

=VLOOKUP(D4,A1:B5,1,0)

Nessa fórmula:

• D4 contém o valor de pesquisa que é Charlie.

• A1:B5 Você pode pesquisar em todas as colunas(Pontuações, Nome do aluno ).

• 1 retorna o valor da 1ª coluna do intervalo (Scores).

• 0 recupera a correspondência exata.

VLOOKUP() retorna um erro #N/A, mas XLOOKUP() trata disso. Fonte: Imagem do autor.

Como você pode ver, o site VLOOKUP() mostra um erro #N/A porque não pode pesquisar à esquerda.

Criando uma saída personalizada quando um valor não é encontrado

Tenho uma lista de alunos com suas identificações, nomes e notas. Durante a pesquisa, digitei acidentalmente uma ID de aluno que não estava na lista. Para lidar com esse caso, quero que uma mensagem personalizada seja exibida sempre que o ID do aluno não for encontrado. Usarei as funções XLOOKUP() e VLOOKUP() para mostrar a você as diferenças. 

Ao usar o site XLOOKUP(), insiro a seguinte fórmula:

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 

Nessa fórmula:

• E9 contém o valor que você deseja pesquisar. No meu caso, é 14256.

• A2:A5 é o intervalo de células que contém o valor de pesquisa (ID do aluno).

• B2:B5 busca a pontuação do ID 14526.

• Student not Found é exibido se a pontuação não estiver na lista. 

Ao usar o site VLOOKUP(), insiro a seguinte fórmula:

(=VLOOKUP(E6, A2:C5,2,0)) 

Nessa fórmula:

• E6 refere-se à ID do aluno, que é o que estou procurando.

• A2:C5 refere-se ao intervalo que contém todos os dados da tabela.

• 2 indica o número da coluna da qual os dados serão obtidos.

• 0 recupera a correspondência exata.

XLOOKUP() retorna uma mensagem, mas VLOOKUP() não. Fonte: Imagem do autor.

Você pode ver que 14256 não existe no intervalo A2:A5, portanto, XLOOKUP() retorna a mensagem personalizada Student not found . E VLOOKUP(), por outro lado, gera um erro #N/A. 

Pesquisando de baixo para cima

Quero descobrir o salário de Sarah no ano mais recente. Para isso, usarei as funções XLOOKUP() e VLOOKUP() para mostrar a você as diferenças. 

Ao usar o site XLOOKUP(), insiro a seguinte fórmula:

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)

Nessa fórmula:

• F8 contém o valor de pesquisa Você.

• B2:B10 olha para o intervalo e procura por Sarah.

• C2:C10 recupera os dados.

• -1 você pode pesquisar na parte inferior da coluna. 

Ao usar o site VLOOKUP(), insiro a seguinte fórmula:

=VLOOKUP(F4, B2:C10,2,0)

Nessa fórmula:

• F4 contém o valor de pesquisa Você.

• B2:C10 procura o valor de pesquisa dentro desse intervalo.

• 2 indica o número da coluna Salário da qual os dados serão obtidos.

• 0 recupera a correspondência exata.

Diferença entre VLOOKUP() e XLOOKUP() no modo de pesquisa. Fonte: Imagem do autor.

Você pode ver que XLOOKUP() encontra o salário desejado para o ano desejado, enquanto VLOOKUP() exibe apenas o primeiro valor correspondente. Consegui fazer isso porque o site XLOOKUP() me permite pesquisar de baixo para cima.

Desempenho e compatibilidade

Vamos agora comparar o desempenho e a compatibilidade das duas funções. 

Desempenho em grandes conjuntos de dados

VLOOKUP() pode ser mais lento ao lidar com grandes conjuntos de dados, especialmente se a coluna de pesquisa não estiver classificada e o argumento de pesquisa de intervalo estiver definido como FALSE para uma correspondência exata. Isso ocorre porque o VLOOKUP() varre o conjunto de dados sequencialmente até encontrar uma correspondência, o que pode consumir muito tempo em tabelas grandes. O XLOOKUP() oferece melhor desempenho em conjuntos de dados grandes. Ele pode lidar com correspondências exatas de forma mais eficiente e pesquisar horizontal e verticalmente sem classificação. 

Compatibilidade com diferentes versões do Excel

Agora, você deve estar pensando em abandonar o VLOOKUP() depois de ver os benefícios do XLOOKUP(). Mas deixe-me esclarecer: embora o XLOOKUP() seja um recurso muito mais rápido e excelente, há alguns momentos em que ele não está disponível. Isso ocorre porque o XLOOKUP() é um recurso mais recente que só funciona no Excel 2021 e no Microsoft 365 (a partir de 2019). Se você colaborar com pessoas que usam versões mais antigas do Excel, precisará usar VLOOKUP() e outras funções em vez de XLOOKUP().

Tabela de resumo

Vamos criar uma tabela de resumo para facilitar a referência. 

Considerações finais

XLOOKUP() supera o desempenho do VLOOKUP(), especialmente ao lidar com grandes conjuntos de dados ou requisitos de pesquisa complexos. Sua flexibilidade, incluindo pesquisa bidirecional, recuperação de vários resultados e adaptabilidade às alterações de dados, faz dele uma ferramenta útil. No entanto, o site VLOOKUP() ainda tem seu lugar, principalmente para usuários de versões mais antigas do Excel ou para aqueles que preferem uma abordagem mais simples para pesquisas básicas. A melhor opção depende, em última análise, de suas necessidades específicas e da versão do Excel.

Se você é novo no Excel ou deseja construir uma base sólida, nosso curso Introdução ao Excel e o programa de habilidades Fundamentos do Excel são ótimos pontos de partida para dominar o básico.