XLOOKUP com vários critérios no Excel: Guia prático

• XLOOKUP pode procurar com vários critérios usando duas técnicas: lógica booleana (arrays multiplicados/somados) ou concatenação (&). Ambas permitem retornar várias colunas de resultado. Use lógica booleana para cenários flexíveis (AND/OR, comparadores) e concatenação quando cada combinação for única.

Quick Links

• A sintaxe do XLOOKUP e exemplo com critério único

• Exemplo 1: Múltiplos critérios com lógica booleana

• Exemplo 2: Múltiplos critérios com concatenação

Um equívoco comum sobre a função XLOOKUP no Microsoft Excel é que você só pode fornecer um único critério ao procurar um valor em seu conjunto de dados. Na verdade, você pode procurar valores com base em múltiplos critérios — e existem pelo menos duas técnicas práticas para isso, com diferenças em simplicidade e versatilidade.

Observação de compatibilidade: para usar XLOOKUP no aplicativo desktop do Excel (PC ou Mac) você precisa do Excel 2021 ou posterior (incluindo Excel para Microsoft 365). XLOOKUP também está disponível no Excel para a web e nos apps de tablet e celular.

Microsoft 365 Personal

Microsoft 365 inclui acesso a aplicativos do Office como Word, Excel e PowerPoint em até cinco dispositivos, 1 TB de armazenamento no OneDrive e mais.

$100 at Microsoft

$100 at Amazon

Expand

Collapse

A sintaxe do XLOOKUP e exemplo com critério único

Embora a sintaxe do XLOOKUP possa parecer, à primeira vista, um pouco densa, ela segue uma ordem lógica e é fácil de dominar:

=XLOOKUP(*a*,*b*,*c*,*d*,*e*,*f*)

onde

• a (obrigatório) é o valor de pesquisa (lookup value),
• b (obrigatório) é o intervalo de pesquisa (lookup array),
• c (obrigatório) é o intervalo de retorno (return array),
• d (opcional) é o texto a retornar se o valor de pesquisa (a) não for encontrado em b,
• e (opcional) é o modo de correspondência (0 = correspondência exata (padrão), -1 = exata ou item imediatamente menor, 1 = exata ou item imediatamente maior, 2 = correspondência com curingas), e
• f (opcional) é o modo de pesquisa (1 = do primeiro ao último (padrão), -1 = do último ao primeiro, 2 = pesquisa binária onde b está em ordem ascendente, -2 = pesquisa binária onde b está em ordem descendente).

Por exemplo, depois de escolher um ID numa lista de validação de dados na célula E1, a fórmula:

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)

na célula E2 retorna o score 51.

Isto ocorre porque:

• E1 contém o valor a procurar (por exemplo, 1323),
• T_Scores[ID] indica a coluna ID da tabela T_Scores onde procurar,
• T_Scores[Score] indica a coluna Score da mesma tabela para retornar o valor correspondente,
• “No match” é o texto a retornar se o valor não existir,
• 0 força correspondência exata, e
• 1 força a pesquisa do topo para baixo.

Você poderia omitir os dois últimos argumentos porque correspondência exata e pesquisa do primeiro ao último são os padrões.

Exemplo 1: Múltiplos critérios com lógica booleana

Quando há mais de um critério, a forma como usamos XLOOKUP muda. Uma técnica poderosa é construir um array temporário usando igualdade e operadores lógicos (TRUE/FALSE convertidos em 1/0) e depois procurar o valor 1.

Cenário: você tem uma marca que vende seis bebidas em seis países. Cada combinação bebida+país tem um gerente e um total de vendas. Sua meta: informar bebida e país e obter o gerente e as vendas.

A fórmula que faz isso é (formatada em múltiplas linhas para legibilidade):

=XLOOKUP(
  1,
  (T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Como funciona, passo a passo:

1. O argumento b constrói dois arrays lógicos:

(T_Managers[Drink]=G1)
(T_Managers[Country]=G2)

cada um retornando uma série de TRUE/FALSE por linha.

2. TRUE/FALSE são coerçados para 1/0 quando multiplicados: multiplicar os arrays equivale a um operador lógico AND (somente linhas que satisfazem ambos os critérios resultam em 1).

3. Procuramos o número 1 com XLOOKUP(“a” = 1). XLOOKUP retorna a primeira linha que contém 1 e então extrai as colunas definidas em c:

T_Managers[[Manager]:[Sales]]

Resultado: o nome do gerente e o número de vendas para a combinação bebida+país.

Importante: ao invés de multiplicar (AND), você pode somar arrays ((cond1)+(cond2)) para obter um comportamento OR — neste caso, procure o primeiro valor >=1 ou ajuste a lógica para localizar a primeira ocorrência.

Notas:

• Use operadores de comparação (>, <, >=, <=) em vez de = quando trabalhar com intervalos numéricos.
• Você pode combinar mais colunas ao multiplicar mais arrays. Ex.: (A=G1)(B=G2)(C=G3).
• Se vários resultados correspondem, XLOOKUP retorna apenas a primeira linha encontrada (a partir do topo por padrão).

Exemplo 2: Múltiplos critérios com concatenação

Outra técnica é concatenar os critérios na chave de pesquisa usando &. Isso cria uma chave única por linha e é simples quando as combinações são estáticas e únicas.

Reutilizando o mesmo cenário, a fórmula fica assim:

=XLOOKUP(
  G1&G2,
  T_Managers[Drink]&T_Managers[Country],
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Como funciona:

• O lookup value G1&G2 cria a string “CoffeeSpain”.
• O lookup array T_Managers[Drink]&T_Managers[Country] cria uma coluna virtual com valores como “Apple juiceAustralia”, “Apple juiceCanada”, “CoffeeSpain” etc.
• XLOOKUP encontra a string concatenada e retorna as colunas de resultado.

Vantagem: simples, direto e legível quando a chave composta é única.

Desvantagem: sensível a espaços/maiúsculas e ao formato exato de concatenação. Se as colunas envolverem valores dinâmicos ou formatação inconsistente, recomenda-se normalizar strings (TRIM, LOWER) antes da concatenação.

Quando cada método é mais adequado

• Concatenação (G1&G2): quando cada combinação é única e as strings são limpas; fácil de entender para a maioria dos usuários.
• Lógica booleana ((A=val)*(B=val)): quando você precisa de flexibilidade (comparadores, lógica OR via soma, múltiplos critérios, critérios numéricos), ou quando uma coluna não tem dados textuais confiáveis.

Contraexemplos e quando isso falha

1. Combinações não únicas: se a mesma combinação bebida+país aparece várias vezes e você precisa de todos os resultados, XLOOKUP devolve apenas a primeira ocorrência. Para retornar múltiplas linhas, use FILTER (Excel 365) ou tabelas auxiliares.

2. Dados inconsistentes: espaços extras, diferenças de caixa (maiúsculas/minúsculas) e formatos numéricos quebram concatenação simples. Use TRIM(), LOWER(), TEXT() para normalizar.

3. Tabelas dinâmicas ou ordenações: se você depender de índices fixos, operações como SORT ou alterações na tabela podem alterar retornos. Prefira referências estruturadas (T_Table[Coluna]) para estabilidade.

4. Performance em planilhas muito grandes: arrays calculados (especialmente com muitas linhas e colunas) podem ser mais lentos; considere INDEX/MATCH em colunas individuais ou otimize com colunas auxiliares.

Abordagens alternativas e complementares

• FILTER (Excel 365): retorna todas as linhas que correspondem a critérios; útil quando há múltiplos resultados.
• SUMIFS/COUNTIFS/AVERAGEIFS: para agregar dados baseados em vários critérios.
• AGGREGATE/SMALL/ROW: para retornar enésimos resultados quando houver duplicatas.
• Power Query: para transformar e combinar colunas antes de carregar para a planilha, útil para operações recorrentes e conjuntos de dados grandes.

Modelo mental e heurísticas rápidas

• Heurística “Chave única”: se a junção dos valores identifica unicamente uma linha, concatenação é suficiente.
• Heurística “Flexibilidade”: se precisa de comparadores, variáveis dinâmicas ou lógica OR, use lógica booleana.
• Mental model: XLOOKUP está procurando um valor em um array; se o valor que você precisa não existe literalmente em uma coluna, crie um array temporário que o represente.

Caixa de fatos (qualitativa)

• Compatibilidade: Excel 2021+ e Excel para Microsoft 365, web e apps móveis.
• Tipos de retorno: XLOOKUP pode retornar múltiplas colunas (faixa) se o return_array for múltiplo.
• Escalabilidade: arrays calculados em milhares de linhas impactam a performance; o Power Query ou colunas auxiliares podem ajudar.

Cheatsheet rápido (fórmulas úteis)

• Procurar com AND (lógica booleana):

=XLOOKUP(1,(A:A=val1)*(B:B=val2),C:D)

• Procurar com OR (soma de arrays):

=XLOOKUP(1,((A:A=val1)+(B:B=val2)),C:D)

• Concatenar chaves (assegure TRIM/LOWER se necessário):

=XLOOKUP(TRIM(LOWER(G1&"|"&G2)),TRIM(LOWER(A:A&"|"&B:B)),C:D)

• Usar FILTER para múltiplas linhas (Excel 365):

=FILTER(T_Managers, (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), "No result")

Matriz de comparação: Booleano vs Concatenar

Checklist por função

• Para analistas de dados:

  • Verificar unicidade das chaves antes de usar concatenação.
  • Normalizar strings (TRIM/LOWER).
  • Testar com dados nulos/ausentes.

• Para gestores:

  • Validar requisitos: precisa do primeiro resultado ou de todos?
  • Decida por FILTER se quer listar todos os matches.

• Para desenvolvedores de relatórios:

  • Preferir colunas auxiliares ou Power Query para operações repetidas em conjuntos de dados grandes.

Galeria de casos limite

• Combinação aparece duas vezes, mas você precisa do maior valor de vendas: combine XLOOKUP com MAXIFS ou use FILTER + MAX.
• Critério numérico com intervalo (ex.: idade entre 20 e 30): use (Idade>=20)*(Idade<=30) na lógica booleana.
• Critérios com datas: normalize datas com DATEVALUE ou garanta formatação consistente.

Dicas de desempenho e otimização

• Evite referenciar colunas inteiras (A:A) em grandes planilhas; prefira intervalos nomeados ou tabelas estruturadas.
• Para cálculos recorrentes, armazene a chave concatenada em uma coluna auxiliar (persistente) em vez de recalcular em arrays.
• Quando possível, use FILTER ou Power Query para reduzir a carga de fórmulas dinâmicas.

Compatibilidade e migração

• Se migrando de INDEX/MATCH: XLOOKUP simplifica sintaxe e reduz a necessidade de colunas auxiliares; contudo, mantenha testes de regressão para garantir resultados idênticos.
• Em ambientes que não tenham XLOOKUP (Excel mais antigo), use INDEX/MATCH com colunas auxiliares ou CONCATENATE + MATCH.

Exemplo prático estendido: tratamento de espaços e maiúsculas

Suponha que as colunas contenham valores com espaços ou maiúsculas inconsistentes. Uma versão resiliente da concatenação:

=XLOOKUP(
  TRIM(LOWER(G1))&"|"&TRIM(LOWER(G2)),
  TRIM(LOWER(T_Managers[Drink]))&"|"&TRIM(LOWER(T_Managers[Country])),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Isto reduz falhas por espaços extras e diferenças de caixa.

Exemplos com comparadores

Para procurar a primeira linha em que Produto = G1 e Vendas > G2:

=XLOOKUP(1, (T_Prod[Produto]=G1)*(T_Prod[Vendas]>G2), T_Prod[[Gerente]:[Vendas]], "No result")

Mermeid: Árvore de decisão para escolher método

flowchart TD
  A[Precisa buscar com múltiplos critérios?] -->|Não| B[XLOOKUP padrão]
  A -->|Sim| C[Chave composta é única e simples?]
  C -->|Sim| D[Use concatenação '&' e normalize strings]
  C -->|Não| E[Precisa de comparadores ou OR?]
  E -->|Sim| F[Use lógica booleana 'arrays' com * ou +]
  E -->|Não| G[Considere FILTER ou Power Query]

Riscos e mitigação

• Risco: resultados errados por dados sujos. Mitigação: validadores, TRIM, LOWER, regras de entrada.
• Risco: latência em planilhas grandes. Mitigação: colunas auxiliares, Power Query, limitar intervalos.
• Risco: múltiplas correspondências retornando apenas a primeira. Mitigação: usar FILTER para listar todas.

1-line Glossário

• XLOOKUP: função de procura moderna do Excel que substitui INDEX/MATCH e VLOOKUP para casos comuns.
• FILTER: função que retorna um conjunto de linhas que satisfazem condições (Excel 365).
• Array temporário: um vetor criado na memória do Excel por uma expressão lógica que XLOOKUP pode pesquisar.

Resumo final

• XLOOKUP aceita múltiplos critérios utilizando lógica booleana ou concatenação.
• Use concatenação para chaves únicas e lógica booleana para maior flexibilidade.
• Normalize dados e cuide da performance em grandes conjuntos. Quando precisar de todos os matches, prefira FILTER.

Importante: teste fórmulas com dados reais e casos extremos (nulos, duplicatas, formatos) antes de aplicar em relatórios de produção.

Notas finais e recursos adicionais

• Se você usa Excel 365, experimente FILTER junto com XLOOKUP para cenários que exigem múltiplos resultados.
• Para transformações recorrentes, Power Query (Obter e Transformar) tende a ser mais robusto e eficiente do que fórmulas matriciais complexas.

• XLOOKUP funciona com múltiplos critérios usando (1) arrays booleanos multiplicados/somados ou (2) concatenação de chaves. Escolha a técnica conforme a unicidade das chaves e a necessidade de flexibilidade. Normalize entradas e otimize performance em grandes planilhas.