Calc VLOOKUP vs XLOOKUP: Escolha a fórmula de pesquisa correta

Use a função XLOOKUP para a maioria das novas pesquisas se a sua planilha for aberta no LibreOffice 24.8 ou posterior; por padrão, ela realiza uma correspondência exata e permite que você escolha intervalos de pesquisa e de resultados separados. Use a função VLOOKUP quando precisar de compatibilidade com versões mais antigas do Calc ou quando o valor pesquisado estiver na primeira coluna de uma tabela compacta. Se você mantiver a função VLOOKUP, inclua o quarto argumento como 0 ou FALSO para uma correspondência exata.

Essas funções resolvem o mesmo problema básico: encontrar uma chave, como um código de produto, e retornar informações relacionadas de outra célula. As diferenças importantes são como cada fórmula define seus intervalos, o que ela assume quando uma opção é omitida e se o arquivo precisa funcionar em softwares mais antigos.

Qual é a diferença prática?

A função PROCV pesquisa na primeira coluna de uma tabela retangular e retorna um valor de uma coluna numerada à sua direita. A função PROXOLÁVEL separa o intervalo de pesquisa do intervalo de retorno, de modo que os valores retornados podem estar em qualquer um dos lados das chaves. A função PROXOLÁVEL também aceita um valor personalizado para correspondências ausentes e pode pesquisar em ordem inversa.

Por exemplo, suponha que A2:A5 contenha IDs de produtos e B2:B5 contenha preços. A célula F2 contém o ID a ser encontrado. No Calc, a fórmula VLOOKUP para correspondência exata é:

=VLOOKUP(F2;A2:B5;2;0)

A fórmula XLOOKUP equivalente é:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

Ambas as funções retornam o preço correspondente. O Calc geralmente exibe ponto e vírgula entre os argumentos da função de acordo com as configurações de localidade; se as fórmulas em sua instalação usam vírgulas, use o separador esperado pelo Calc. Os exemplos usam resultados de uma única célula e intervalos de pesquisa unidimensionais.

Quando você deve escolher a função XLOOKUP?

Escolha a função XLOOKUP para uma nova pasta de trabalho quando souber que os destinatários usam o LibreOffice 24.8 ou posterior. A função tornou-se disponível no LibreOffice 24.8, de acordo com a página de ajuda oficial do XLOOKUP . Seu modo de correspondência padrão é exato, portanto, uma chave exata ausente não retorna silenciosamente um valor próximo.

O padrão da função é =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). Os três primeiros argumentos identificam a chave, o intervalo unidimensional de pesquisa e o intervalo de retorno. Um quarto argumento, como , "Not found"substitui o resultado usual #N/A quando nenhuma correspondência é encontrada.

Retorna um valor de uma coluna à esquerda.

A função PROCV não pode retornar um valor à esquerda da primeira coluna da tabela. A função PROX pode, porque os intervalos de pesquisa e de resultado são independentes. Por exemplo, se os IDs estiverem em C2:C100 e os nomes dos departamentos estiverem em A2:A100, use:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Esse layout também evita um índice de coluna frágil. Se alguém inserir uma coluna em uma tabela VLOOKUP, o índice fixo pode apontar para um campo diferente. O intervalo de resultados explícito do XLOOKUP facilita a inspeção da coluna de retorno desejada.

Retorna a entrada correspondente mais recente.

Quando existem chaves duplicadas e você deseja a última correspondência, a função XLOOKUP oferece suporte à pesquisa reversa com seu modo de pesquisa. Para um intervalo vertical, o último argumento -1pesquisa do último para o primeiro:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Esta função retorna o resultado associado à última ocorrência exata. Use-a somente quando a ordem das linhas tiver um significado definido, como os registros mais recentes aparecerem por último. Se duplicatas devem ser tratadas como um erro, limpe ou valide os dados em vez de escolher uma ocorrência implicitamente.

Quando a função PROCV ainda é a melhor opção?

Mantenha a função PROCV (VLOOKUP) quando o arquivo precisar ser aberto em uma versão mais antiga do LibreOffice que não inclua a função PROX (XLOOKUP), ou quando o aplicativo de destino não reconhecer a função PROX (XLOOKUP). A documentação oficial do Calc descreve os argumentos de tabela e índice da função PROCV, bem como seu argumento opcional de intervalo classificado; consulte a referência oficial das funções de planilha .

Para uma correspondência exata, o padrão é =VLOOKUP(key;table;column_index;0). O índice conta as colunas a partir da borda esquerda da tabela selecionada: 1 significa a primeira coluna, 2 significa a segunda. A chave de pesquisa deve estar nessa primeira coluna e o campo retornado deve estar à sua direita.

O quarto argumento é importante. Se você o omitir ou defini-lo como VERDADEIRO, a função PROCV usa correspondência aproximada e espera que a primeira coluna esteja classificada em ordem crescente. Esse comportamento pode ser útil para intervalos como faixas de imposto de renda, onde cada linha marca um limite inferior. É arriscado para IDs, nomes e listas não classificadas. Para esses casos, especifique 0 ou FALSO.

Como se comparam os comportamentos de correspondência e de erro?

• Correspondência exata: a função XLOOKUP usa correspondência exata por padrão. A função VLOOKUP exige que o quarto argumento seja definido como 0 ou FALSE para garantir uma pesquisa exata.
• Chave ausente: a função XLOOKUP pode retornar uma mensagem ou valor escolhido em seu quarto argumento. A função VLOOKUP geralmente retorna #N/A; utilize a função SE/ERRO se precisar de uma mensagem personalizada.
• Correspondência aproximada: Ambos os métodos suportam correspondência baseada em intervalo, mas os dados e o modo selecionado devem estar de acordo com a regra pretendida. Classifique os dados conforme necessário e verifique os casos extremos.
• Direção da pesquisa: a função XLOOKUP pode pesquisar de baixo para cima com o argumento de modo de pesquisa. A função VLOOKUP retorna a primeira linha correspondente em seu uso normal de cima para baixo.

Para fornecer uma resposta personalizada para chave ausente com a função PROCV, use a opção =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). Isso mantém a configuração de correspondência exata visível e trata apenas o erro de chave não disponível. Evite envolver a fórmula em um manipulador de erros genérico, a menos que você também queira ocultar problemas não relacionados, como uma referência de intervalo inválida.

O que você deve verificar antes de compartilhar uma planilha?

A ajuda do LibreOffice informa que a função XLOOKUP está disponível desde a versão 24.8. Ela também observa que a XLOOKUP não faz parte do padrão OpenFormula do OpenDocument versão 1.3 e identifica um namespace de função da Microsoft para interoperabilidade. Isso significa que o formato do arquivo, por si só, não garante que todos os aplicativos de planilha mais antigos ou de terceiros calcularão a função. Para ambientes com softwares mistos, teste a planilha no aplicativo de destino mais antigo ou use uma fórmula já estabelecida, como a PROCV com correspondência exata, onde o formato for adequado.

Observe também uma condição documentada para fórmulas matriciais: quando a matriz de resultados da função XLOOKUP é um intervalo de células, a função deve ser inserida como uma fórmula matricial. Os exemplos simples acima retornam um único valor correspondente. Se você deseja que uma fórmula retorne várias células, consulte a ajuda específica da versão e teste como o aplicativo de destino lida com o resultado matricial.

Como você pode verificar o resultado?

1. Testar uma chave existente e comparar o valor retornado com a linha de origem.
2. Teste uma chave que não existe. Confirme se a função XLOOKUP exibe a opção alternativa escolhida ou se a função VLOOKUP retorna a mensagem esperada #N/A ou SENA.
3. Para correspondência exata com a função PROCV, confirme se o último argumento é 0 ou FALSO. Se você usar intencionalmente uma correspondência aproximada, verifique a ordem de classificação e os valores imediatamente acima, sobre e abaixo de um limite.
4. Para a função XLOOKUP com uma coluna de retorno à esquerda ou uma pesquisa reversa, teste uma chave com um resultado conhecido e, quando existirem duplicados, confirme qual ocorrência deve ser retornada.
5. Salve e abra novamente a planilha no aplicativo Calc ou planilha mais antigo que seus destinatários utilizam. Um resultado correto no seu computador não comprova que uma versão mais antiga seja compatível com a mesma função.

Para a maioria das novas planilhas do Calc, a função XLOOKUP oferece intervalos mais claros e valores padrão de correspondência exata mais seguros. Use a função VLOOKUP quando a compatibilidade com versões anteriores for importante ou quando o layout com a primeira coluna à direita já for adequado, e torne o modo de correspondência explícito.