Você escreveu uma fórmula XLOOKUP com várias condições usando o operador &, mas em vez de um resultado, vê um erro #SPILL!. Esse erro ocorre porque o XLOOKUP retorna uma matriz que se sobrepõe a células com dados, ou a matriz de pesquisa está estruturada incorretamente para lógica de múltiplas condições. Este artigo explica por que o erro de transbordamento acontece com XLOOKUP ao combinar condições e fornece três métodos confiáveis para corrigi-lo.
Principais Conclusões: Corrigir o Erro #SPILL! no XLOOKUP com Várias Condições
- Coluna auxiliar com concatenação: Combine as colunas de condição em uma coluna auxiliar e use XLOOKUP nessa única coluna para evitar conflitos de transbordamento de matriz.
- XLOOKUP com lógica booleana e duplo unário: Use
XLOOKUP(1, (intervalo1=cond1)(intervalo2=cond2), intervalo_retorno)para forçar uma pesquisa escalar sem transbordamento. - INDEX/MATCH como alternativa: Quando o XLOOKUP continuar a transbordar, troque para INDEX e MATCH com critérios inseridos como matriz para pesquisas estáveis com múltiplas condições.
Por que o XLOOKUP com Várias Condições Causa o Erro #SPILL!
O erro #SPILL! ocorre quando uma fórmula retorna vários resultados e não há espaço vazio suficiente abaixo e à direita da célula da fórmula. O XLOOKUP normalmente retorna um único valor, mas quando você concatena matrizes de pesquisa usando & ou usa operações de matriz no argumento lookup_array, o Excel pode interpretar o resultado como uma matriz dinâmica que transborda. Por exemplo, =XLOOKUP(A2&B2, Plan2!A:A&Plan2!B:B, Plan2!C:C) cria dois argumentos de matriz: o valor de pesquisa é um único texto concatenado, mas a matriz de pesquisa é uma matriz de valores concatenados de colunas inteiras. O Excel tenta retornar uma matriz transbordada porque o argumento lookup_array é uma expressão de matriz, não uma referência de intervalo simples. Se qualquer célula no intervalo de transbordamento não estiver vazia, você obtém #SPILL!. A correção é reestruturar a fórmula para que o lookup_array seja um único intervalo que possa ser correspondido sem expansão de matriz.
Método 1: Usar uma Coluna Auxiliar para Concatenar Condições
A correção mais simples é adicionar uma coluna auxiliar na tabela de pesquisa que una as colunas de condição em uma única coluna. Em seguida, o XLOOKUP usa essa única coluna como lookup_array, sem operação de matriz.
- Inserir uma coluna auxiliar na tabela de pesquisa
Na tabela de pesquisa, insira uma nova coluna ao lado dos seus dados. Por exemplo, se as condições estão na coluna A e na coluna B, insira uma nova coluna C. Na célula C2, digite=A2&B2e copie para baixo. Isso cria uma chave concatenada única para cada linha. - Escrever a fórmula XLOOKUP usando a coluna auxiliar
Na célula de resultado, digite=XLOOKUP(A2&B2, Plan2!C:C, Plan2!D:D). Substitua Plan2!C:C pela coluna auxiliar e Plan2!D:D pela coluna que contém o valor que você deseja retornar. Como o lookup_array agora é um intervalo de coluna única, não ocorre expansão de matriz e o erro #SPILL! desaparece. - Copiar a fórmula para baixo
Arraste a fórmula para baixo para aplicá-la a linhas adicionais. Cada célula da fórmula retorna um único resultado sem transbordamento.
Método 2: Usar Lógica Booleana com o Operador Duplo Unário
Se você não puder adicionar uma coluna auxiliar, use multiplicação booleana dentro do XLOOKUP. Este método força uma pesquisa escalar multiplicando matrizes de condição em uma única matriz de 1s e 0s e, em seguida, procurando o valor 1.
- Construir a fórmula de multiplicação booleana
Na célula da fórmula, digite=XLOOKUP(1, (A2=Plan2!A:A)(B2=Plan2!B:B), Plan2!C:C). O lookup_value é o número 1. O lookup_array é(A2=Plan2!A:A)(B2=Plan2!B:B), que retorna uma matriz de 1s e 0s. Um 1 aparece apenas onde ambas as condições são verdadeiras. - Garantir que não haja obstrução de transbordamento
Verifique se as células abaixo e à direita da célula da fórmula estão vazias. Se contiverem dados, limpe-as ou mova a fórmula para um local com espaço vazio suficiente. A fórmula ainda pode transbordar se o lookup_array for uma expressão de matriz, mas com o método do duplo unário, o intervalo de transbordamento é de apenas uma célula porque o XLOOKUP retorna uma única correspondência. - Testar com uma única linha
Se o erro persistir, teste a fórmula em uma única linha limitando os intervalos a um pequeno número de linhas, por exemplo=XLOOKUP(1, (A2=Plan2!A1:A10)(B2=Plan2!B1:B10), Plan2!C1:C10). Se funcionar, o problema provavelmente é obstrução do intervalo de transbordamento. Estenda os intervalos para colunas completas somente após confirmar que a fórmula retorna um único valor.
Método 3: Usar INDEX e MATCH como Alternativa
Quando o XLOOKUP continuar a transbordar apesar das correções acima, troque para a combinação INDEX e MATCH. Esta fórmula clássica lida com múltiplas condições sem transbordar porque MATCH sempre retorna um único número de posição.
- Escrever a fórmula INDEX/MATCH
Digite=INDEX(Plan2!C:C, MATCH(1, (A2=Plan2!A:A)(B2=Plan2!B:B), 0)). Plan2!C:C é a coluna de retorno. A parte MATCH usa a mesma multiplicação booleana do Método 2. O terceiro argumento de MATCH é 0 para correspondência exata. - Inserir como fórmula de matriz em versões antigas do Excel
Se você estiver usando Excel 2019 ou anterior, pressione Ctrl+Shift+Enter para inserir a fórmula como uma fórmula de matriz. Excel 365 e Excel 2021 aceitam normalmente sem entrada de matriz. - Copiar a fórmula para outras células
Arraste a fórmula para baixo. Cada célula retorna um único resultado. Nenhum erro #SPILL! ocorre porque INDEX e MATCH não produzem comportamento de transbordamento de matriz dinâmica.
Se o Erro de Transbordamento Ainda Aparecer Após Tentar Estes Métodos
XLOOKUP Retorna #SPILL! Mesmo com uma Coluna Auxiliar
Se você ainda vir #SPILL! após adicionar uma coluna auxiliar, verifique se a coluna auxiliar não contém células vazias ou erros. Uma célula vazia na coluna auxiliar cria um valor concatenado em branco, o que pode causar múltiplas correspondências. Preencha todas as células da coluna auxiliar com uma fórmula de concatenação. Verifique também se o intervalo de transbordamento não está obstruído por células mescladas ou dados em colunas adjacentes. Selecione a célula da fórmula e pressione Ctrl+Shift+Seta para Baixo para ver o intervalo de transbordamento pretendido. Limpe quaisquer dados nesse intervalo.
XLOOKUP com Multiplicação Booleana Retorna Resultados Incorretos
O método de multiplicação booleana pode retornar valores incorretos se as colunas de pesquisa contiverem células vazias. Uma célula vazia comparada a uma condição retorna FALSO, que multiplica para 0, então a linha é ignorada. Certifique-se de que todas as colunas de condição tenham dados. Se uma célula de condição estiver realmente em branco, use SE para tratar os espaços em branco como uma string específica, por exemplo (SE(A2="","BRANCO",A2)=Plan2!A:A). Isso evita falsas incompatibilidades.
INDEX/MATCH Retorna Erro #N/D
Um erro #N/D de INDEX/MATCH significa que nenhuma linha satisfaz todas as condições. Verifique se as condições na fórmula correspondem aos tipos de dados nas colunas de pesquisa. Por exemplo, se a coluna A contém números armazenados como texto, a comparação falha. Use a função TEXTO ou converta os tipos de dados. Verifique também espaços à direita nos valores de pesquisa ou células da tabela. Use a função ARRUMAR em ambos os lados da comparação: (ARRUMAR(A2)=ARRUMAR(Plan2!A:A)).
XLOOKUP com Coluna Auxiliar vs Lógica Booleana: Comparação
| Item | Método da Coluna Auxiliar | Método da Lógica Booleana |
|---|---|---|
| Esforço de configuração | Requer adicionar uma nova coluna à tabela de origem | Nenhuma alteração na tabela de origem necessária |
| Legibilidade da fórmula | Simples e fácil de auditar | Mais complexa devido à multiplicação de matrizes |
| Desempenho com grandes dados | Rápido porque a pesquisa é em uma única coluna | Mais lento porque colunas inteiras são multiplicadas em memória |
| Risco de erro de transbordamento | Baixo — lookup_array é um único intervalo | Médio — lookup_array é uma expressão de matriz |
| Compatibilidade com Excel antigo | Funciona no Excel 2019 e anteriores | Requer Ctrl+Shift+Enter no Excel 2019 e anteriores |
Agora você pode eliminar o erro #SPILL! ao usar XLOOKUP com várias condições. Comece adicionando uma coluna auxiliar para a correção mais simples. Se não puder modificar os dados de origem, use o método de multiplicação booleana. Para máxima compatibilidade com versões antigas do Excel, troque para INDEX e MATCH. Como dica avançada, combine XLOOKUP com a função LET para armazenar a matriz booleana em uma variável, o que melhora a legibilidade e o desempenho da fórmula: =LET(conds, (A2=Plan2!A:A)(B2=Plan2!B:B), XLOOKUP(1, conds, Plan2!C:C)).