Como usar o XLOOKUP no Google Sheets (com exemplos)

De acordo com Exploding Topics, mais de 900 milhões de pessoas usam o Google Sheets todos os meses. Mas quantas usam o XLOOKUP no Google Sheets?

Este tutorial mostrará como usá-lo para puxar dados da mesma planilha ou de duas planilhas diferentes.

Ele também mostra o único caso em que o FILTER funciona melhor que o XLOOKUP: retornar todas as correspondências.

Neste guia:

• O que o XLOOKUP faz e seus seis argumentos
• Como usar o XLOOKUP, passo a passo
• XLOOKUP de outra aba
• XLOOKUP entre duas planilhas
• Exemplos: buscas horizontais, vários critérios, curingas, intervalos nomeados, valores padrão, FILTER e a última correspondência ou a mais próxima
• XLOOKUP vs. VLOOKUP

O que é o XLOOKUP no Google Sheets?

Em termos simples, o XLOOKUP ajuda a encontrar informações específicas no Planilhas Google. Você informa ao XLOOKUP o que procurar (como a nota de um aluno) e onde procurar (qual coluna do seu boletim de notas). Em seguida, ele encontra rapidamente as informações correspondentes e as retorna para você.

XLOOKUP é uma alternativa mais flexível a funções mais antigas como VLOOKUP e HLOOKUP.

Sintaxe e argumentos do XLOOKUP

O XLOOKUP tem seis argumentos. Só os três primeiros são obrigatórios.

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

O Excel chama os mesmos argumentos de lookup_value, lookup_array, return_array e if_not_found, e alguns exemplos abaixo usam esses nomes.

Como usar o XLOOKUP no Google Sheets: passo a passo

Nesta seção, você vai aprender a usar o XLOOKUP na vertical para encontrar uma correspondência exata com uma única chave de pesquisa.

1. Abra no Google Sheets a planilha que contém a sua tabela.

2. Em vez de digitar a chave de pesquisa diretamente na função, você pode digitá-la em uma célula e depois usar a referência dessa célula.

3. Digite a função XLOOKUP e selecione a célula com a chave de pesquisa como primeiro parâmetro.

4. Como segundo parâmetro, selecione a coluna em que você quer procurar a chave de pesquisa.

5. Como terceiro parâmetro, selecione a coluna de onde você quer obter o resultado. Pule os parâmetros opcionais para usar os valores padrão.

6. Feche o parêntese e pressione "Enter". Pronto, aí está o resultado.

Como usar o XLOOKUP de outra aba

Digamos que você seja um professor e use uma planilha do Google para acompanhar as notas dos seus alunos. Vamos chamar a planilha de ’Exames dos alunos“.”

Agora, a planilha tem duas folhas:

• Um chamado “Students” (Alunos) - contém os nomes dos alunos e os números de identificação e
• Um chamado “Test Scores” (Pontuações de testes) - contém os números de identificação e as pontuações dos alunos.

- Folha 1: Alunos

- Folha 2: Pontuações de testes

Lembre-se de que essas duas planilhas estão na mesma planilha. Mais adiante, mostraremos como usar o XLOOKUP em duas planilhas separadas.

Você deseja ver a pontuação de cada aluno ao lado de seu nome na planilha “Students” (Alunos). Vamos escolher John Doe. 

1. Na célula abaixo de “Test Score” (Pontuação do teste) para John Doe, use esta fórmula XLOOKUP:

Vamos detalhar essa fórmula:

• Valor de pesquisa (A2): O número de identificação que você está procurando.
• Matriz de pesquisa (‘Test Scores’!A:A): Essa é a coluna na planilha “Test Scores” em que a função XLOOKUP deve encontrar os números de ID.
• Retorna a matriz (‘Test Scores’!B:B): Se encontrar uma correspondência, ele retorna o valor correspondente da matriz de retorno (a pontuação do aluno).
• Se não for encontrado (“Not Found”): Se não encontrar uma correspondência, ele exibirá “Not Found” (Não encontrado).”

Em termos simples, é isso que a fórmula diz:

2. Pressione “Enter” no teclado ou clique em qualquer lugar da planilha. A partitura aparecerá.

Leitura recomendada: Como usar a função XLOOKUP no Excel?

Como você usa a função XLOOKUP em duas Planilhas Google separadas?

Você pode usar a função XLOOKUP em duas Planilhas Google separadas?

Sim. Coloque o intervalo de busca e o intervalo de resultados dentro de IMPORTRANGE para que o XLOOKUP os leia do outro arquivo. Na primeira vez que você conectar os dois arquivos, o Google Sheets vai pedir que você permita o acesso.

Como isso funciona?

Use esta fórmula:

Vamos voltar ao exemplo do nosso professor.

Imagine que você está colaborando com outro professor em um projeto. Cada um de vocês tem sua própria Planilha do Google contendo os dados dos alunos.

Sua Planilha do Google se chama “Students” (Alunos) e contém nomes de alunos e números de identificação.

Por outro lado, a planilha do seu colega se chama “Test Scores” (Pontuações de testes) e contém os números de identificação dos alunos e suas pontuações em um teste recente.

Você deseja combinar essas informações em sua planilha “Students” (Alunos) para ver a pontuação de cada aluno ao lado de seu nome.

Como você faz isso?

1. Abra a planilha de seu colega e copie o ID do URL. Ele começa depois de ‘/d/’ e termina antes do próximo “/”.”

2. Substitua os dois “sheet-id” na fórmula abaixo pelo ID que você acabou de copiar.

3. Copie o nome da planilha da planilha do seu colega.

4. Substitua os dois “Sheet1” na fórmula pelo nome da planilha de seu colega, “Test Scores”. Esta é a aparência de sua fórmula final:

5. Na célula abaixo de "Test Score" de Jane Smith, use a fórmula XLOOKUP acima.

6. Pressione “Enter” no teclado ou clique em qualquer lugar da planilha. A partitura aparecerá.

7. Se quiser obter a nota de Jane Smith na linha 3, troque "=XLOOKUP(A2" por "=XLOOKUP(A3".

Fórmula antiga com (A2:

Nova fórmula com (A3:

Exemplos de XLOOKUP no Google Sheets

Agora que você sabe usar o XLOOKUP para encontrar uma correspondência exata de um único valor, é hora de conhecer outras formas de usá-lo. Nos exemplos abaixo, você vai aprender a usar o XLOOKUP para fazer buscas horizontais, pesquisar com vários critérios, retornar várias colunas de uma vez, pesquisar com curingas e usar intervalos nomeados. Os últimos exemplos mostram um valor padrão para quando não há correspondência, o FILTER para retornar todas as correspondências e como encontrar a última correspondência ou a mais próxima.

XLOOKUP horizontal (em vez do HLOOKUP)

O XLOOKUP também pesquisa em uma linha, então você pode usá-lo no lugar do HLOOKUP. Siga os passos abaixo para fazer uma busca horizontal.

1. Em uma célula vazia, digite a fórmula XLOOKUP e adicione a chave de pesquisa como primeiro parâmetro. Neste caso, a chave de pesquisa é o cabeçalho de coluna que você quer encontrar.

2. Como segundo parâmetro, selecione a linha em que você quer procurar a chave de pesquisa. Neste caso, os cabeçalhos estão na linha 1.

3. Como terceiro parâmetro, indique a linha de onde você quer obter o resultado. Pule os parâmetros opcionais para usar os valores padrão.

4. Feche o parêntese e pressione "Enter" para obter o resultado.

XLOOKUP com vários critérios

Para pesquisar vários valores com o XLOOKUP, você pode combiná-lo com ARRAYFORMULA.

1. Em uma célula vazia, digite a função XLOOKUP.

2. Como primeiro parâmetro, selecione a primeira chave de pesquisa, adicione um E comercial (&) e selecione a segunda chave de pesquisa.

3. Como segundo parâmetro, digite a função ARRAYFORMULA e selecione as colunas em que você quer procurar as chaves de pesquisa, unidas por um E comercial (&).

4. Como terceiro parâmetro, selecione a coluna de onde você quer que o resultado seja retornado.

5. Feche o parêntese e pressione "Enter" para obter o resultado.

Retorne várias colunas com um único XLOOKUP

Para retornar vários valores da linha correspondente, como o e-mail, o produto e o valor, você pode usar a função ARRAYFORMULA. O XLOOKUP continua retornando só a primeira correspondência. Para obter todas as linhas correspondentes, use o FILTER, como mostrado mais abaixo.

1. Em uma célula vazia, digite a função XLOOKUP e adicione a chave de pesquisa como primeiro parâmetro.

2. Como segundo parâmetro, selecione a coluna em que você quer procurar a chave de pesquisa.

3. Como terceiro parâmetro, adicione a função ARRAYFORMULA e selecione as colunas de onde você quer obter os resultados.

4. Feche os dois parênteses e pressione "Enter" para ver os resultados.

XLOOKUP com curingas

Se você só tem ou só se lembra de partes da sua chave de pesquisa, pode usar caracteres curinga para encontrar uma correspondência.

• * corresponde a zero ou mais caracteres.
• ? corresponde a exatamente um caractere.
• ~ anula os caracteres curinga para que você possa pesquisá-los.

1. Digite a chave de pesquisa parcial em uma célula para usá-la como referência depois.

2. Em uma célula vazia, digite a função XLOOKUP.

3. Adicione o curinga e a chave de pesquisa parcial, como mostrado abaixo. O caractere curinga deve ficar entre aspas e ser unido à chave de pesquisa parcial com um E comercial (&).

4. Como segundo parâmetro, selecione a coluna em que você quer procurar a correspondência.

5. Como terceiro parâmetro, selecione a coluna de onde você quer obter o resultado.

6. Como você está pesquisando com um curinga, precisa indicar que o quinto parâmetro é "2". Mas não é preciso indicar o quarto e o sexto parâmetros se você quiser os valores padrão.

XLOOKUP com intervalos nomeados

Por fim, veja como usar intervalos nomeados no XLOOKUP para deixar o processo ainda mais fácil.

1. Abra a planilha com a sua tabela e acesse Dados > Intervalos nomeados.

2. Na barra lateral, clique em "Adicionar um intervalo".

3. Selecione a coluna que você quer nomear e digite o nome. Clique em "Concluído".

4. Repita as etapas 2 e 3 até nomear todas as colunas necessárias.

5. Em uma célula vazia, digite a função XLOOKUP e adicione a chave de pesquisa.

6. Como segundo parâmetro, comece a digitar o nome da coluna de busca e selecione-a no menu suspenso.

7. Como terceiro parâmetro, comece a digitar o nome da coluna a retornar e selecione-a como antes.

8. Feche o parêntese e pressione "Enter" para ver o resultado.

Mostre um valor padrão quando não houver correspondência

Você pode usar XLOOKUP no Google Sheets para encontrar valores ausentes em um conjunto de dados comparando duas listas (por exemplo, uma lista de produtos existentes e uma de produtos novos).

Vamos dar uma olhada em um exemplo.

Você é um gerente de vendas que acompanha o desempenho dos funcionários em uma planilha do Google. Você tem:

• Folha 1 (Dados do funcionário): Contém nomes de funcionários e números de identificação.

• Folha 2: Contém os números de identificação dos funcionários e seus números de vendas.

• Folha 3: Contém o informações consolidadas. No entanto, alguns funcionários não têm dados de vendas, como mostrado abaixo.

Lembre-se de que essas três planilhas estão na mesma planilha.

1. Abra a Planilha 3 (“Consolidado” em nosso exemplo).
2. Na célula C2 de Consolidado, insira a seguinte fórmula XLOOKUP:

Essa fórmula irá:

1. Procure o ID do funcionário em B2 dentro do intervalo de IDs de funcionários na Planilha 2 (Planilha2!A:A).
2. Se for encontrado, ele retornará o valor de vendas correspondente da Planilha 2 (Sheet2!B:B).
3. Se não for encontrado, ele retornará o valor 0, indicando que não há vendas.

Lembre-se de que você precisa editar a fórmula antes de usá-la. Por exemplo, minha planilha se chama “Sales”, não “Sheet2” e o ID “102” está na linha B3, não na B2. Portanto, minha nova fórmula será:

Use-a na célula e pressione Enter no teclado.

Retorne todas as correspondências com o FILTER

Muitas vezes as pessoas nos perguntam se o XLOOKUP pode retornar vários resultados no Planilhas Google.

A melhor maneira de responder a essa pergunta é por meio de um exemplo.

Digamos que você gerencie uma pequena livraria e controle o estoque em uma planilha do Google. Sua planilha tem as seguintes colunas:

• Título do livro
• Autor
• ISBN
• Quantidade em estoque
• Preço

Você deseja encontrar todos os livros que um autor específico escreveu, seus ISBNs e quantidades em estoque.

1. Identifique seu valor de pesquisa. Neste exemplo, escolherei o nome de um autor, pois é o valor que quero encontrar: J.K. Rowling.
2. Identifica a matriz de pesquisa, a coluna que contém os nomes dos autores: Author.
3. Identifique as matrizes de retorno, as colunas das quais você deseja retornar os dados:
   • ISBN
   • Quantidade em estoque
4. Crie a fórmula XLOOKUP:

Lembre-se de que você precisa editar essa fórmula para adequá-la ao seu cenário.

5. Abra a planilha e use a fórmula em uma célula vazia.

6. Pressione "Enter". Como você pode ver, o XLOOKUP só retorna o primeiro livro correspondente, embora a fórmula verifique todas as linhas.

O XLOOKUP pode retornar todas as linhas correspondentes no Google Sheets?

Não é bem assim. O XLOOKUP normalmente retorna um único ou a primeira correspondência que encontrar. Infelizmente, ele não suporta nativamente o retorno de várias correspondências, como faz o FILTER. Portanto, em sua forma padrão, o XLOOKUP não é adequado para extrair várias linhas.

Você notou que mencionei o FILTRO? Como ele funciona?

7. Altere sua fórmula para:

8. Pressione "Enter". E pronto: o FILTER trouxe dados de várias linhas.

Encontre a última correspondência ou o valor mais próximo

Os dois últimos argumentos mudam a forma como o XLOOKUP pesquisa. Estes exemplos usam a tabela de vendas da seção passo a passo, em que a coluna F tem o valor e a coluna G, o vendedor.

Para obter o valor da última venda da lista para Agent2, defina search_mode como -1 para que o XLOOKUP pesquise de baixo para cima:

=XLOOKUP("Agent2", G:G, F:F, "Not found", 0, -1)

Para encontrar o valor mais próximo em vez de um exato, defina match_mode como -1 (próximo menor) ou 1 (próximo maior). Digamos que K2:K5 tenha faixas de vendas e L2:L5 a taxa de comissão de cada uma. Esta fórmula retorna a taxa da faixa mais alta que não passa do valor em F2:

=XLOOKUP(F2, K2:K5, L2:L5, , -1)

Nenhuma das duas fórmulas precisa de dados ordenados. Só os modos de busca binária, 2 e -2, precisam.

XLOOKUP vs. VLOOKUP: qual é a diferença?

VLOOKUP pesquisa verticalmente, da esquerda para a direita, em uma tabela (daí o “V”); o valor de pesquisa deve estar na primeira coluna. O XLOOKUP, entretanto, é mais flexível. Ele pode pesquisar vertical e horizontalmente; não é necessário especificar um índice de coluna.

Além disso, o VLOOKUP só retorna valores à direita da coluna de busca, enquanto o XLOOKUP retorna valores de qualquer um dos lados.

Aqui está a diferença em termos de sintaxe:

VLOOKUP

XLOOKUP

Conclusão

Como você viu, a função XLOOKUP do Google Sheets é mais poderosa que o VLOOKUP e o HLOOKUP, e mais fácil de usar que alternativas como INDEX & MATCH. Você conheceu a função e a sua sintaxe e agora sabe usar o XLOOKUP para fazer buscas verticais e horizontais de uma única correspondência exata. Também sabe trazer dados de outra aba ou de outro arquivo, pesquisar com vários critérios ou curingas e retornar várias colunas de uma vez. Quando uma busca não encontra nada, o missing_value oferece um valor padrão, e quando você precisa de todas as correspondências, o FILTER faz o que o XLOOKUP não consegue.

Se você repete as mesmas buscas em vários arquivos toda semana, o Sheetgo, uma plataforma de automação de dados com IA, pode conectar essas planilhas e manter os dados combinados atualizados de forma programada, sem fórmulas IMPORTRANGE para manter.