XLOOKUP(): a função de busca mais versátil do Excel

Se você já usou o VLOOKUP() e travou porque precisava buscar valores à esquerda, o XLOOKUP() resolve esse problema. É a função de busca mais nova do Excel. Ela pode substituir o VLOOKUP(), o HLOOKUP() e até a maioria das combinações de INDEX() e MATCH() com uma única fórmula simples.

XLOOKUP() consegue buscar em qualquer direção, retornar mais de uma coluna de uma vez e exibir uma mensagem personalizada quando um valor estiver ausente. 

Neste guia, vamos começar pela sintaxe. Depois, vamos criar fórmulas reais passo a passo, usando um único conjunto de dados do começo ao fim. Também vamos abordar correspondências exatas e aproximadas, como retornar mais de uma coluna, como tratar erros e como o XLOOKUP() se compara ao VLOOKUP() e ao INDEX()/MATCH(). 

Como usar XLOOKUP() no Excel

Vamos construir uma fórmula real de XLOOKUP() do zero. 

Nosso conjunto de dados tem cinco produtos, cada um com um ID, um nome, uma categoria e um preço, nas colunas de A a D, com cabeçalhos na linha 1.

Conjunto de dados para XLOOKUP(). Imagem do autor.

Digamos que queremos encontrar o preço de "P104". Vamos ver como fazer isso passo a passo.

Buscar um único valor

Clique em uma célula vazia onde você quer que o resultado apareça. Vou usar a F2. E digite a seguinte fórmula:

=XLOOKUP("P104", A2:A6, D2:D6)

Nessa fórmula: 

• "P104" é o lookup_value, o que estamos buscando

• A2:A6 é o lookup_array, o intervalo onde o Excel buscou

• D2:D6 é o return_array, o intervalo de onde o Excel trouxe a resposta

O Excel procura "P104" na coluna A (“Product ID”), encontra na linha 5 e retorna o valor correspondente da coluna D: US$ 45.

Em vez de digitar "P104" diretamente, você pode referenciar uma célula, assim dá para mudar o valor de busca sem editar a fórmula. No nosso caso, a célula A5 contém "P104", então a fórmula fica:

=XLOOKUP(A5, A2:A6, D2:D6)

Use o XLOOKUP() no Excel. Imagem do autor.

Agora você pode digitar qualquer Product ID em A5 e o preço é atualizado automaticamente.

Retornar um valor de outra coluna

Aqui está a diferença real entre XLOOKUP() e VLOOKUP(). Você não fica preso a retornar apenas uma coluna à direita da coluna de busca.

Digamos que, em vez do preço, você queira a categoria de "P102". A configuração é a mesma; basta apontar o return_array para a coluna C (“Category”):

=XLOOKUP(A3, A2:A6, C2:C6)

Aqui, A3 retorna Home. Se mudar para A5, atualiza para Electronics. Do mesmo jeito, altere o return_array para puxar o nome do produto, o preço ou qualquer outra coluna, sempre usando o mesmo lookup_value e lookup_array.

XLOOKUP() retornando valor de outra coluna. Imagem do autor.

Com o VLOOKUP(), você teria que contar colunas a partir da coluna de busca e ajustar o índice de coluna toda vez que quisesse um resultado diferente. Com o XLOOKUP(), você apenas aponta diretamente para a coluna desejada. 

Copiar a fórmula por várias linhas

Até aqui, buscamos um valor por vez. Mas a maioria das planilhas reais precisa buscar muitos valores de uma vez, por exemplo, uma lista inteira de IDs de pedido, cada um precisando do preço correspondente.

Veja como configurar: a coluna A, começando em A2, já contém nossa lista de Product IDs. Queremos que os preços correspondentes apareçam ao lado de cada um, na coluna F.

Para isso, clique em F2 e digite a fórmula, referenciando A2 como valor de busca, assim:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Agora clique de novo em F2 e arraste a alça de preenchimento (o pequeno quadrado no canto inferior direito da célula) até o fim das suas linhas.

O Excel copia a fórmula para baixo, e cada linha agora retorna o preço correto para seu próprio Product ID. O ID de A2 recebe seu preço em F2, o de A3 em F3 e assim por diante.

Copie a fórmula para outras células. Imagem do autor.

Repare nos cifrões em A2:A6 e D2:D6. Isso é uma referência absoluta. Ela trava o intervalo, evitando que mude quando você copia a fórmula para baixo. 

Isso é importante aqui, já que cada linha precisa buscar na mesma lista de produtos, só mudando o valor de busca a cada vez.

Como usar correspondência exata e aproximada no XLOOKUP()

Por padrão, o XLOOKUP() procura uma correspondência exata. Se não encontrar, retorna um erro, a menos que você tenha configurado o argumento if_not_found como vimos antes. 

Mas o XLOOKUP() também pode encontrar a correspondência mais próxima, usando o argumento match_mode. 

Correspondência exata (padrão)

Você não precisa digitar nada extra para isso. Todas as fórmulas que criamos até agora já usam correspondência exata, pois é o comportamento padrão do XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

O Excel procura o valor exato em A2 e só retorna um resultado se encontrar essa correspondência precisa em algum lugar do intervalo de busca.

Se quiser escrever isso explicitamente, adicione um zero como quinto argumento:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() retorna correspondência exata. Imagem do autor.

Percebeu o espaço antes do 0? Ele pula o if_not_found, já que estamos deixando em branco aqui, e vai direto para match_mode. Na maioria dos casos, você pode simplesmente omitir esse argumento e deixar o Excel usar o padrão de correspondência exata.

Correspondência aproximada

Agora, imagine que seus dados não tenham um valor para cada possível busca. Pense numa tabela de comissão em que vendedores ganham taxas diferentes conforme a faixa de vendas em que se enquadram. Você não terá uma linha para cada valor em dólares, apenas o ponto inicial de cada faixa.

É aqui que a correspondência aproximada ajuda. 

Defina match_mode como -1 e o XLOOKUP() encontra o maior valor que seja menor ou igual ao valor buscado.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Suponha que seu intervalo de busca tenha limites de vendas de US$ 0, US$ 5.000 e US$ 10.000, cada um atrelado a uma taxa de comissão diferente. 

Um vendedor com US$ 7.500 em vendas não corresponde exatamente a nenhuma linha. Com match_mode igual a -1, o XLOOKUP() encontra US$ 5.000, o maior limite que não ultrapassa US$ 7.500, e retorna a taxa de comissão daquela linha.

Se você definir match_mode como 1, o XLOOKUP() inverte a lógica: encontra o menor valor que seja maior ou igual ao valor buscado. Isso é útil para coisas como faixas de peso de envio, em que você arredonda para cima para o próximo nível, não para baixo.

XLOOKUP() retorna correspondência aproximada. Imagem do autor.

Observação: a correspondência aproximada pressupõe que seu intervalo de busca esteja ordenado — crescente para -1, decrescente para 1. Se não estiver ordenado corretamente, o XLOOKUP() pode retornar um resultado errado sem exibir erro, o que é pior. Portanto, sempre confira a ordenação antes de usar esse modo.

Como buscar valores à esquerda com XLOOKUP()

O VLOOKUP() só pesquisa a coluna mais à esquerda do intervalo e retorna um valor de uma coluna à sua direita; não consegue fazer o inverso. Seu terceiro argumento não é um intervalo, e sim um índice de coluna contado a partir da borda esquerda. 

Não há como dizer “vá uma coluna para a esquerda”, então, se o valor de que você precisa estiver à esquerda da coluna de busca, você fica preso a reorganizar os dados ou adicionar uma coluna auxiliar só para a busca funcionar.

O XLOOKUP() não tem essa restrição, já que você aponta intervalos separados para a busca e para o retorno.

Veja nossa lista de produtos: Product ID está na coluna A e Product Name na coluna B. 

Suponha que você tenha um nome de produto e queira encontrar seu ID — o inverso do que o VLOOKUP() consegue fazer.

Em C9, digite Bluetooth Speaker (um nome que existe na lista). Em E2, digite:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

O XLOOKUP() busca "Bluetooth Speaker" na coluna B, encontra na linha 5 e retorna "P104" da coluna A, mesmo que a coluna A esteja à esquerda da coluna B. 

XLOOKUP() busca valores à esquerda, diferente do VLOOKUP(). Imagem do autor.

Como retornar várias colunas com XLOOKUP()

XLOOKUP() pode retornar várias colunas de uma vez, em uma única fórmula, usando o recurso de matrizes dinâmicas. Para isso, aponte o return_array para um intervalo em vez de uma única coluna, e o XLOOKUP() “espilha” a linha correspondente por quantas células forem necessárias. 

Vamos testar com nossa lista de produtos. 

Em C9, digito um Product ID, por exemplo, "P104". Em E2, digito:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Perceba que o return_array aqui é B2:D6, com três colunas — Product Name, Category e Price. Ao pressionar Enter, o XLOOKUP() encontra "P104" na coluna A e retorna Bluetooth Speaker, Electronics e US$ 45 de uma só vez, “espilhando” em E9, F9 e G9.

XLOOKUP() retorna múltiplas colunas com uma única fórmula. Imagem do autor.

É isso que “intervalo de espilhamento” significa. E9 tem a fórmula; F9 e G9 se preenchem sozinhas, sem fórmulas próprias. 

Clique em F9 e a barra de fórmulas mostra a fórmula original esmaecida, não algo digitado ali. Tente digitar em F9 enquanto o espilhamento de E9 estiver ativo e o Excel mostrará um erro de spill. 

Vamos ver como corrigir isso na seção de erros comuns.

Como tratar valores ausentes no XLOOKUP()

Por padrão, o XLOOKUP() exibe o erro #N/A quando não encontra correspondência. O argumento if_not_found resolve isso ao mostrar o que você quiser em vez do erro padrão.  

Suponha que em C9 eu digite um Product ID que não existe nos dados, como "P999".

Exibindo uma mensagem personalizada

Em G2, digito:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Como "P999" não existe na coluna A, o XLOOKUP() ignora o erro e retorna "Not Found". Troque a frase para o que fizer sentido, como "Check Product ID" ou "No Match".

Mostre uma mensagem personalizada em vez do erro padrão. Imagem do autor.

Retornando em branco

Às vezes, você quer uma célula vazia, sem texto. Para isso, use duas aspas seguidas, assim:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Use quando estiver criando um relatório em que um "Not Found" perdido poluiria uma coluna de valores reais.

Exiba uma célula em branco em vez do erro padrão. Imagem do autor.

Deixando como erro genuíno

Se você ignorar o if_not_found, o XLOOKUP() volta ao #N/A. E isso nem sempre é ruim. Se a fórmula for para uso próprio e você quiser captar um erro de digitação ou uma falta de cadastro na hora, ver o #N/A é mais útil do que um branco silencioso que esconde o problema.

Observação: if_not_found só trata ausências de correspondência. Se seu lookup_array e return_array tiverem tamanhos diferentes, por exemplo, o XLOOKUP() ainda exibirá #VALUE!, e o if_not_found não vai impedir esse erro. Vamos falar sobre esse caso na seção de erros comuns.

Recursos avançados do XLOOKUP()

Vamos ver os recursos avançados do XLOOKUP()

Pesquisar de baixo para cima

Defina search_mode como -1 e o XLOOKUP() vasculha seu intervalo de baixo para cima, em vez de cima para baixo. Use quando sua base tiver valores duplicados e você quiser a entrada mais recente, não a primeira.

Digamos que, no meu conjunto de dados, eu tenha três Customer IDs duplicados. Na célula E2, digito a fórmula padrão, que usa a busca de cima para baixo: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Ela é calculada do começo da tabela e mostra o resultado da primeira correspondência. 

Mas, se eu quiser buscar a partir do fim, uso esta fórmula: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Nessa fórmula, -1 diz ao Excel para começar a busca do fim do conjunto de dados. Isso retorna 340, que é o primeiro valor correspondente a partir de baixo.  

XLOOKUP() busca de baixo para cima. Imagem do autor.

Modo de busca binária

Defina search_mode como 2 e o XLOOKUP() deixa de verificar linha a linha para usar uma busca binária. 

Funciona assim: 

• Ele olha primeiro para o item do meio do intervalo. 

• Se não for correspondência, descarta metade onde o valor não pode estar e olha o meio do que restou. 

• Repete isso, cortando os dados pela metade a cada passo, até encontrar a correspondência. 

• Defina search_mode como -2 e ele faz o mesmo, só que começando pelo último item em vez do primeiro.

Aqui, em E2, digite:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Essa fórmula retorna 200, uma linha completamente diferente.

O modo de busca binária dá resultado incorreto. Imagem do autor.

Porque a busca binária só funciona se o seu lookup_array estiver ordenado. Como nossa coluna de Customer ID não está ordenada — C100, C101, C100, C102, C100 — a busca binária descarta a metade errada e devolve uma correspondência errada. 

Agora vamos ver o que acontece quando os dados estão realmente ordenados. 

Ordene o mesmo log por Customer ID, crescente (os três C100 no começo) e rode a mesma fórmula de novo. 

O modo de busca binária funciona com dados ordenados. Imagem do autor.

Dessa vez, E2 retorna 120, batendo com o que a busca normal daria. Essa é a diferença: a busca binária só pula dados que pode descartar com segurança — e isso só vale quando os dados já estão ordenados. 

Fórmulas XLOOKUP() aninhadas

Aninhe um XLOOKUP() dentro de outro quando o resultado de uma busca precisar alimentar uma segunda busca, em vez de um intervalo fixo.

Suponha que você queira achar um preço, mas de qual coluna puxar depende de uma busca separada, como o nível de preço do cliente.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

Nessa fórmula, o XLOOKUP() interno primeiro identifica de qual intervalo de colunas puxar com base em D9; depois, o XLOOKUP() externo usa esse resultado como seu return_array. 

XLOOKUP() aninhado. Imagem do autor.

XLOOKUP() vs. VLOOKUP()

Já vimos em detalhes como o XLOOKUP() funciona. Aqui vai a comparação com o VLOOKUP().

Há dois pontos que valem o alerta rápido: 

• O VLOOKUP() usa, por padrão, correspondência aproximada. Se você esquecer o quarto argumento, ele não mostra erro; só retorna o valor errado em silêncio.

• Se você inserir uma coluna em uma planilha com VLOOKUP(), as fórmulas passam a puxar valores errados. O XLOOKUP() evita isso ao referenciar intervalos diretamente, e não contar colunas.

Nada disso torna o VLOOKUP() obsoleto. Ele continua ótimo para planilhas simples e estáticas. Mas, para qualquer coisa nova que você construir, XLOOKUP() é a escolha mais segura.

XLOOKUP() vs. INDEX() e MATCH()

Antes do XLOOKUP(), INDEX() e MATCH() juntos eram o padrão para contornar os limites do VLOOKUP(). São duas funções em vez de uma. 

• MATCH() encontra a posição do valor buscado. 

• INDEX() usa essa posição para trazer o resultado de onde você apontar: esquerda ou direita, tanto faz.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Compare com a versão em XLOOKUP() que usamos ao longo do artigo:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() só funciona no Microsoft 365 e no Excel 2021 em diante, como vimos. Se sua pasta precisar abrir no Excel 2019 ou 2016, ou se você for compartilhar com pessoas em versões antigas, INDEX() e MATCH() ainda funcionam em todos.

Há também um caso mais específico. 

Separar "encontrar a posição" de "retornar o valor" com INDEX() e MATCH() ajuda em algumas fórmulas aninhadas complexas. É raro, então a maioria dos leitores não vai precisar.

Fora esses dois casos, XLOOKUP() é a melhor escolha para um arquivo novo. 

Erros comuns do XLOOKUP() e como corrigir

Aqui vai uma referência rápida dos problemas mais comuns e como resolver cada um.

Erros #N/A

Essa é a resposta padrão do XLOOKUP() quando não encontra correspondência. Isso aconteceu comigo antes, quando o lookup_value e o lookup_array não batiam, e o XLOOKUP() não tinha com o que combinar na coluna A.

Como corrigir: confirme se o lookup_value realmente existe no lookup_array, com a mesma grafia e formatação. Se ausências forem esperadas, use o argumento if_not_found para trocar o #N/A por algo mais útil, como "Not Found" ou uma célula em branco, como vimos. 

Intervalo de busca incorreto

Acontece quando lookup_array e return_array não alinham corretamente: linhas erradas, colunas erradas ou tamanhos diferentes. 

Dependendo da sua versão do Excel ou do Sheets, isso pode aparecer como #VALUE! ou, às vezes, como #N/A com uma mensagem separada explicando o problema real. Não deixe o #N/A te enganar achando que é falta de correspondência. 

Como corrigir: garanta que lookup_array e return_array cubram o mesmo número de linhas ou colunas. Se lookup_array é A2:A5, return_array também precisa ir da linha 2 à 5, e não 4 à 5 ou qualquer intervalo desalinhado.

Incompatibilidade de tipo de dado

VLOOKUP() pode dar erro por esperar número e receber texto. O XLOOKUP() pode ter algo parecido. Se o seu lookup_value é numérico mas o lookup_array guarda os mesmos valores como texto (ou vice-versa), o XLOOKUP() não vai encontrar, mesmo parecendo idênticos na tela.

Como corrigir: verifique se lookup_value e lookup_array usam o mesmo tipo de dado. Números armazenados como texto geralmente ficam alinhados à esquerda na célula, não à direita. Use VALUE() para converter texto em número, ou TEXT() para fazer o inverso, dependendo do caso.

Erros de spill

Isso acontece quando seu return_array cobre mais de uma coluna, mas as células onde o XLOOKUP() precisa “espilhar” não estão vazias. O Excel precisa de espaço livre ao lado da célula da fórmula para derramar os resultados. Se já tiver algo ali, a fórmula inteira falha.

Como corrigir: limpe as células onde os resultados precisam ir e depois insira a fórmula. Se você precisa de algo nessas células, mova a fórmula do XLOOKUP() para outro lugar com espaço livre ao redor.

Considerações finais 

Agora você já sabe usar o XLOOKUP() para valores únicos, múltiplas colunas, dados ausentes e até buscas aninhadas em dois intervalos. Cobrimos bastante coisa.

Daqui para frente, faça do XLOOKUP() seu padrão para novas planilhas. Ele é mais flexível que o VLOOKUP(), mais fácil de ler que INDEX/MATCH() e dispensa envolver toda fórmula em IFERROR().

Se quiser ir além, vale aprender INDEX() e MATCH() por conta própria também.