Como usar a função XLOOKUP do Excel -

Resumo

A função Excel XLOOKUP é um substituto moderno e flexível para funções mais antigas como VLOOKUP, HLOOKUP e LOOKUP. XLOOKUP suporta correspondência aproximada e exata, curingas (*?) Para correspondências parciais e pesquisas em intervalos verticais ou horizontais.

Objetivo

Valores de pesquisa em intervalo ou matriz

Valor de retorno

Valores correspondentes da matriz de retorno

Sintaxe

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumentos

• lookup - o valor de lookup.
• lookup_array - a matriz ou intervalo a ser pesquisado.
• return_array - a matriz ou intervalo a retornar.
• not_found - (opcional) Valor a retornar se nenhuma correspondência for encontrada.
• match_mode - (opcional) 0 = correspondência exata (padrão), -1 = correspondência exata ou próximo menor, 1 = correspondência exata ou próximo maior, 2 = correspondência curinga.
• search_mode - (opcional) 1 = pesquisa do primeiro (padrão), -1 = pesquisa do último, 2 = pesquisa binária crescente, -2 = pesquisa binária decrescente.

Versão

Excel 365

Notas de uso

XLOOKUP é um substituto moderno para a função VLOOKUP. É uma função flexível e versátil que pode ser usada em uma ampla variedade de situações.

XLOOKUP pode encontrar valores em intervalos verticais ou horizontais, pode realizar correspondências aproximadas e exatas e oferece suporte a curingas (*?) Para correspondências parciais. Além disso, o XLOOKUP pode pesquisar dados a partir do primeiro valor ou do último valor (consulte o tipo de correspondência e os detalhes do modo de pesquisa abaixo). Comparado a funções mais antigas, como VLOOKUP, HLOOKUP e LOOKUP, XLOOKUP oferece várias vantagens principais.

Mensagem não encontrada

Quando XLOOKUP não consegue encontrar uma correspondência, ele retorna o erro # N / A, como outras funções de correspondência no Excel. Ao contrário das outras funções de correspondência, XLOOKUP oferece suporte a um argumento opcional denominado not_found que pode ser usado para substituir o erro # N / A quando, de outra forma, ele apareceria. Valores típicos para not_found podem ser "Não encontrado", "Sem correspondência", "Sem resultado", etc. Ao fornecer um valor para not_found, coloque o texto entre aspas duplas ("").

Nota: Tenha cuidado se você fornecer uma string vazia ("") para not_found. Se nenhuma correspondência for encontrada, XLOOKUP não exibirá nada em vez de # N / A. Se você quiser ver o erro # N / A quando uma correspondência não for encontrada, omita o argumento inteiramente.

Tipo de partida

Por padrão, o XLOOKUP executará uma correspondência exata. O comportamento da correspondência é controlado por um argumento opcional chamado match_type, que tem as seguintes opções:

Tipo de partida	Comportamento
0 (padrão)	Combinação exata. Retornará # N / A se não houver correspondência.
-1	Correspondência exata ou próximo item menor.
1	Correspondência exata ou próximo item maior.
2	Correspondência de curinga (*,?, ~)

Modo de pesquisa

Por padrão, XLOOKUP começará a corresponder a partir do primeiro valor de dados. O comportamento da pesquisa é controlado por um argumento opcional chamado search_mode , que fornece as seguintes opções:

Modo de pesquisa	Comportamento
1 (padrão)	Pesquisa do primeiro valor
-1	Pesquisa a partir do último valor (reverso)
2	Valores binários de pesquisa classificados em ordem crescente
-2	Valores binários de pesquisa classificados em ordem decrescente

As pesquisas binárias são muito rápidas, mas os dados devem ser classificados conforme necessário. Se os dados não forem classificados corretamente, uma pesquisa binária pode retornar resultados inválidos que parecem perfeitamente normais.

Exemplo # 1 - correspondência exata básica

Por padrão, o XLOOKUP realizará uma correspondência exata. No exemplo abaixo, XLOOKUP é usado para recuperar vendas com base em uma correspondência exata no filme. A fórmula em H5 é:

=XLOOKUP(H4,B5:B9,E5:E9)

Explicação mais detalhada aqui.

Exemplo # 2 - correspondência básica aproximada

Para ativar uma correspondência aproximada, forneça um valor para o argumento "match_mode". No exemplo abaixo, XLOOKUP é usado para calcular um desconto com base na quantidade, que requer uma correspondência aproximada. A fórmula em F5 fornece -1 para match_mode para permitir a correspondência aproximada com o comportamento de "correspondência exata ou menor":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Explicação mais detalhada aqui.

Exemplo # 3 - valores múltiplos

XLOOKUP pode retornar mais de um valor ao mesmo tempo para a mesma correspondência. O exemplo abaixo mostra como XLOOKUP pode ser configurado para retornar três valores correspondentes com uma única fórmula. A fórmula em C5 é:

=XLOOKUP(B5,B8:B15,C8:E15)

Observe que a matriz de retorno (C8: E15) inclui 3 colunas: Primeiro, Último, Departamento. Todos os três valores são retornados e abrangem o intervalo C5: E5.

Exemplo # 4 - pesquisa bidirecional

XLOOKUP pode ser usado para realizar uma pesquisa bidirecional, aninhando um XLOOKUP dentro de outro. No exemplo abaixo, o XLOOKUP "interno" recupera uma linha inteira (todos os valores de Glass), que é transferida para o XLOOKUP "externo" como a matriz de retorno. O XLOOKUP externo encontra o grupo apropriado (B) e retorna o valor correspondente (17,25) como o resultado final.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Mais detalhes aqui.

Exemplo # 5 - mensagem não encontrada

Como outras funções de pesquisa, se XLOOKUP não encontrar um valor, ele retornará o erro # N / A. Para exibir uma mensagem personalizada em vez de # N / A, forneça um valor para o argumento opcional "não encontrado", entre aspas duplas (""). Por exemplo, para exibir "Não encontrado" quando nenhum filme correspondente for encontrado, com base na planilha abaixo, use:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Você pode personalizar esta mensagem como quiser: "Sem correspondência", "Filme não encontrado", etc.

Exemplo # 6 - critérios complexos

Com a capacidade de manipular matrizes nativamente, XLOOKUP pode ser usado com critérios complexos. No exemplo abaixo, XLOOKUP está correspondendo ao primeiro registro onde: a conta começa com "x" e a região é "leste" e o mês não é abril:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Detalhes: (1) exemplo simples, (2) exemplo mais complexo.

Benefícios do XLOOKUP

XLOOKUP oferece várias vantagens importantes, especialmente em comparação com VLOOKUP:

• XLOOKUP pode pesquisar dados à direita ou à esquerda dos valores de pesquisa
• XLOOKUP pode retornar vários resultados (exemplo # 3 acima)
• O padrão de XLOOKUP é uma correspondência exata (o padrão de VLOOKUP é aproximado)
• XLOOKUP pode trabalhar com dados verticais e horizontais
• XLOOKUP pode realizar uma busca reversa (do último ao primeiro)
• XLOOKUP pode retornar linhas ou colunas inteiras, não apenas um valor
• XLOOKUP pode trabalhar com matrizes nativamente para aplicar critérios complexos

Notas

1. XLOOKUP pode funcionar com arrays verticais e horizontais.
2. XLOOKUP retornará # N / A se o valor de pesquisa não for encontrado.
3. O lookup_array deve ter uma dimensão compatível com o argumento return_array , caso contrário, XLOOKUP retornará #VALUE!
4. Se XLOOKUP for usado entre pastas de trabalho, ambas devem ser abertas, caso contrário, XLOOKUP retornará #REF !.
5. Como a função INDEX, XLOOKUP retorna uma referência como resultado.

Vídeos relacionados

Exemplo básico de XLOOKUP Neste vídeo, configuraremos a função XLOOKUP com um exemplo básico. Correspondendo ao nome da cidade, recuperaremos o país e a população. Correspondência aproximada XLOOKUP básica Neste vídeo, configuraremos a função XLOOKUP para realizar uma correspondência aproximada a fim de calcular um desconto baseado na quantidade. XLOOKUP com lógica booleana Neste vídeo, veremos como usar a função XLOOKUP com lógica booleana para aplicar vários critérios. XLOOKUP com vários valores de pesquisa Neste vídeo, configuraremos XLOOKUP para retornar vários valores em uma matriz dinâmica, fornecendo uma gama de valores de pesquisa em vez de um único valor de pesquisa.