Como usar o XLOOKUP no Planilhas Google (+ fórmulas e exemplos)

De acordo com Tópicos de explosão, De acordo com o Google Sheets, mais de 900 milhões de pessoas usam o Google Sheets mensalmente. Mas quantos usam o XLOOKUP no Planilhas Google?

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

Também discutiremos a melhor e mais fácil alternativa ao XLOOKUP.

Continue lendo!

O que é XLOOKUP?

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ê.

O XLOOKUP é uma alternativa mais flexível ao antigo funções como VLOOKUP e HLOOKUP.

Como usar o XLOOKUP no Planilhas Google?

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, é necessário garantir que ambas as planilhas estejam abertas no navegador e que você faça referência à outra planilha corretamente na fórmula XLOOKUP.

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” (Pontuação do teste) para 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 você quiser obter Pontuação de Jane Smith na linha 3, altere “=XLOOKUP(A2” para “=XLOOKUP(A3.”

Fórmula antiga com (A2:

Nova fórmula com (A3:

Exemplos de XLOOKUP em ação

Exemplo 1: Valor ausente

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

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 na célula e pressione Enter no teclado.

Exemplo 2: Retornar vários resultados

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 retorna apenas um valor, embora nossa fórmula solicite explicitamente que todas as linhas sejam verificadas.

7. No entanto, ainda há uma pequena maneira de recuperar todos os dados. Clique no primeiro dado retornado.

E arraste-o para baixo. Serão exibidos mais dados, embora eles sejam repetidos com frequência.

Você pode usar o XLOOKUP para retornar vários resultados no Planilhas Google?

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?

8. Altere sua fórmula para:

9. Pressione “Enter”. E voilà, o FILTER extraiu dados de várias linhas.

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.

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

VLOOKUP

XLOOKUP

Usar o XLOOKUP no Planilhas Google

Acabamos de ver como usar o XLOOKUP no Planilhas Google por meio de exemplos. Também compartilhamos fórmulas brutas e explicamos como usá-las.