Retornando linhas ou colunas inteiras

Até agora, XLOOKUP retornou um único valor. Mas ela também pode retornar uma linha ou coluna inteira de dados de uma só vez.

Quando uma fórmula retorna vários valores, eles se expandem automaticamente para as células vizinhas. Assim, uma única XLOOKUP pode preencher um registro inteiro pequeno.

O truque é fazer a matriz de retorno abranger várias colunas. Em vez de retornar apenas B2:B20, retorne B2:E20.

XLOOKUP encontra a linha correspondente e então devolve todas as colunas dessa linha na matriz de retorno. Uma fórmula, quatro resultados.

Digite a fórmula em uma única célula, por exemplo F2, e pressione a tecla de confirmação. Os valores aparecerão em F2, G2, H2 e I2.

Uma borda azul discreta contorna o intervalo expandido. Você só edita a célula superior esquerda; as demais são preenchidas pela expansão e não podem ser alteradas diretamente.

Uma tabela de funcionários tem o ID na coluna A e o nome, o departamento, a função e o salário nas colunas B a E.

Digite um ID em D2 e uma única XLOOKUP retornará o registro inteiro. Altere o ID e a linha inteira será atualizada instantaneamente: uma pequena ferramenta de pesquisa em uma única fórmula.

A mesma ideia funciona verticalmente. Se você pesquisar em uma linha de cabeçalhos, poderá retornar uma coluna inteira de resultados.

Aqui, XLOOKUP pesquisa na linha de cabeçalhos B1:E1 um rótulo em D2 e expande para baixo a coluna completa B2:E50 abaixo do cabeçalho correspondente.

Depois que uma fórmula se expande, você pode fazer referência ao intervalo expandido inteiro usando a célula seguida de uma cerquilha, como em F2#.

Isso é muito útil: uma operação SUM sobre uma linha expandida continua correta mesmo que a largura da linha mude, porque F2# sempre significa "toda a expansão a partir de F2".

Como o resultado é uma matriz, você pode fornecê-lo diretamente a funções que aceitam intervalos.

Por exemplo, envolva a pesquisa em SUM para totalizar uma linha retornada de valores mensais, tudo em uma única fórmula e sem células auxiliares.

Uma fórmula de expansão precisa de células vazias para ser preenchida. Se alguma célula no caminho da expansão já contiver dados, XLOOKUP retornará um erro #SPILL!.

A correção é simples: limpe as células que estão bloqueando o caminho ou mova a fórmula para uma área livre. O intervalo de expansão precisa estar completamente vazio.

Para criar um cartão de pesquisa refinado, você também pode expandir os cabeçalhos dos campos.

Coloque uma XLOOKUP que retorne a linha de dados e, acima dela, faça referência ao intervalo de cabeçalhos. Quando a expansão aumentar ou diminuir, os rótulos continuarão alinhados às colunas retornadas.

Você pode até aninhar uma XLOOKUP dentro de outra. A função interna retorna uma coluna inteira, e a externa seleciona uma única célula dessa coluna.

Isso produz uma verdadeira pesquisa em duas direções, correspondendo tanto a uma linha quanto a uma coluna, usando apenas XLOOKUP. É uma alternativa interessante a INDEX-MATCH-MATCH.

Agora você sabe que XLOOKUP pode retornar mais de um valor:

• Uma matriz de retorno com várias colunas expande uma linha inteira
• Uma matriz de retorno com várias linhas expande uma coluna inteira
• Faça referência à expansão usando o sufixo #
• Limpe as células que bloqueiam o caminho para evitar #SPILL!

Isso transforma uma única fórmula em um visualizador completo de registros.

Teste sua compreensão dos resultados expandidos de XLOOKUP.

Você concluiu o curso de XLOOKUP aprendendo a expandir resultados:

• Amplie a matriz de retorno para expandir uma linha ou coluna inteira
• A expansão preenche as células vazias vizinhas e exibe uma borda azul
• Faça referência à expansão usando o sufixo #, como em F2#
• Evite #SPILL! mantendo a área de expansão livre

Com a sintaxe, os valores alternativos, a direção e a expansão dominados, XLOOKUP pode substituir quase todas as funções de pesquisa antigas que você escrever.