Restituire intere righe o colonne

Finora XLOOKUP ha restituito un singolo valore. Tuttavia, può anche restituire contemporaneamente un'intera riga o colonna di dati.

Quando una formula restituisce più valori, questi si espandono automaticamente nelle celle adiacenti. In questo modo una sola XLOOKUP può riempire un'intera mini-scheda.

Il trucco consiste nel fare in modo che l'array restituito comprenda più colonne. Invece di restituire solo B2:B20, si restituisce B2:E20.

XLOOKUP trova la riga corrispondente e restituisce quindi ogni colonna di quella riga nell'array restituito. Una formula, quattro risultati.

Inserisca la formula in una sola cella, ad esempio F2, quindi prema Invio. I valori compariranno nelle celle F2, G2, H2 e I2.

Un sottile bordo blu delimita l'intervallo di espansione. È possibile modificare solo la cella in alto a sinistra; le altre vengono riempite dall'espansione e non possono essere modificate direttamente.

Una tabella dei dipendenti contiene l'ID nella colonna A e nome, reparto, ruolo e stipendio nelle colonne dalla B alla E.

Inserendo un ID in D2, una sola XLOOKUP restituisce l'intero record. Modificando l'ID, l'intera riga si aggiorna immediatamente: un piccolo strumento di ricerca contenuto in una sola formula.

La stessa idea funziona verticalmente. Se si cerca in una riga di intestazioni, è possibile restituire un'intera colonna di risultati.

In questo caso XLOOKUP cerca nella riga delle intestazioni B1:E1 un'etichetta presente in D2 ed espande verso il basso l'intera colonna B2:E50 sotto l'intestazione corrispondente.

Una volta che una formula si è espansa, è possibile fare riferimento all'intero intervallo espanso usando la cella seguita da un cancelletto, ad esempio F2#.

È una funzione molto potente: una SUM applicata a una riga espansa continua a essere corretta anche se cambia la larghezza della riga, perché F2# indica sempre "l'intera espansione a partire da F2".

Poiché il risultato è un array, è possibile passarlo direttamente alle funzioni che accettano intervalli.

Per esempio, si può racchiudere la ricerca in SUM per calcolare il totale dei valori mensili restituiti in una riga, tutto in un'unica formula e senza celle di supporto.

Una formula che si espande ha bisogno di celle vuote da riempire. Se una cella lungo il percorso di espansione contiene già dei dati, XLOOKUP restituisce un errore #SPILL!.

La soluzione è semplice: cancellare i dati dalle celle che bloccano l'espansione oppure spostare la formula in un'area libera. L'intervallo di espansione deve essere completamente vuoto.

Per creare una scheda di ricerca curata, è possibile espandere anche le intestazioni dei campi.

Inserisca una XLOOKUP che restituisca la riga di dati e, sopra di essa, faccia riferimento all'intervallo delle intestazioni. Quando l'espansione si allarga o si restringe, le etichette rimangono comunque allineate alle colonne restituite.

È persino possibile annidare una XLOOKUP all'interno di un'altra. Quella interna restituisce un'intera colonna, mentre quella esterna seleziona una singola cella al suo interno.

In questo modo si ottiene una vera ricerca a due vie, che individua sia una riga sia una colonna, usando esclusivamente XLOOKUP. È una valida alternativa a INDEX-MATCH-MATCH.

Ora sa che XLOOKUP può restituire più di un valore:

• Un array restituito con più colonne si espande in un'intera riga
• Un array restituito con più righe si espande in un'intera colonna
• È possibile fare riferimento all'espansione con il suffisso #
• È necessario svuotare le celle che la bloccano per evitare #SPILL!

In questo modo una singola formula diventa un visualizzatore completo di record.

Verifichi la propria comprensione dell'espansione dei risultati di XLOOKUP.

Ha completato il corso su XLOOKUP imparando a espandere i risultati:

• Ampliare l'array restituito per espandere un'intera riga o colonna
• L'espansione riempie le celle vuote adiacenti e mostra un bordo blu
• Fare riferimento a un'espansione con il suffisso #, come in F2#
• Evitare #SPILL! mantenendo libera l'area di espansione

Dopo aver acquisito padronanza della sintassi, dei valori alternativi, della direzione e dell'espansione, XLOOKUP può sostituire quasi ogni funzione di ricerca precedente.