Come utilizzare la funzione XLOOKUP in Excel 2016

Excel 2016 per gli abbonati a Office 365 su Windows e Mac ora supporta una nuova funzione CERCA.X, pubblicizzata come un sostituto notevolmente più semplice e versatile della funzione di ricerca verticale molto popolare (ma spesso diffamata), CERCA.VERT (non so cosa sia la X in CERCA.X sta per; estensivo, forse?).

Per quelli di voi che non hanno ancora familiarità con CERCA.VERT (considerata la terza funzione più utilizzata subito dopo SOMMA e MEDIA), questa funzione cerca verticalmente per riga nella colonna più a sinistra di una tabella di ricerca designata dall'alto verso il basso finché non trova un valore in un colonna di ricerca designata da un numero di offset che corrisponde o supera quello che stai cercando. Sebbene estremamente utile per individuare elementi particolari in un lungo elenco o colonna di una tabella di dati nel foglio di lavoro, la funzione CERCA.VERT ha diverse limitazioni non condivise da questa nuova funzione di ricerca, come CERCA.X:

• Predefinito per trovare corrispondenze esatte per il tuo valore di ricerca nell'intervallo di ricerca
• Può cercare sia verticalmente (per riga) che orizzontalmente (per colonna) in una tabella, sostituendo così la necessità di utilizzare la funzione CERCA.ORIZZ durante la ricerca orizzontalmente per colonna
• Può cercare a sinistra oa destra in modo che l'intervallo di ricerca nella tabella di ricerca non debba trovarsi in una colonna a sinistra di quella designata come intervallo di restituzione affinché la funzione funzioni
• Quando viene utilizzata la corrispondenza esatta predefinita, funziona anche quando i valori nell'intervallo di ricerca non sono ordinati in un ordine particolare
• Può cercare dalla riga in basso verso l'alto nell'intervallo dell'array di ricerca, utilizzando un argomento della modalità di ricerca opzionale

La funzione XLOOKUP ha cinque possibili argomenti, i primi tre dei quali sono obbligatori e gli ultimi due facoltativi, utilizzando la seguente sintassi:

XLOOKUP( valore_ricerca , matrice_ricerca , matrice_ritorno ,[ modalità_corrispondenza ],[ modalità_ricerca ])

L' argomento lookup_value obbligatorio designa il valore o l'elemento che stai cercando. L' argomento array look_up obbligatorio designa l'intervallo di celle da cercare per questo valore di ricerca e l' argomento array_ritorno designa l'intervallo di celle contenente il valore che si desidera restituire quando Excel trova una corrispondenza esatta.

* Tieni presente che quando designi gli argomenti array_ricerca e array_ritorno nella funzione CERCA X, entrambi gli intervalli devono essere di uguale lunghezza, altrimenti Excel restituirà il #VALORE! errore nella tua formula Questo è un motivo in più per utilizzare i nomi degli intervalli oi nomi delle colonne di una tabella di dati designata quando si definiscono questi argomenti piuttosto che indicarli o digitarne i riferimenti di cella .

L' argomento facoltativo match_mode può contenere uno dei seguenti quattro valori:

• 0 per una corrispondenza esatta (l'impostazione predefinita, come quando non è designato alcun argomento match_mode )
• -1 per corrispondenza esatta o valore inferiore successivo
• 1 per la corrispondenza esatta o il valore successivo maggiore
• 2 per la corrispondenza parziale utilizzando caratteri jolly uniti al riferimento di cella nell'argomento lookup_value

L' argomento facoltativo search_mode può contenere uno dei seguenti quattro valori:

• 1 per cercare dal primo all'ultimo, ovvero dall'alto verso il basso (l'impostazione predefinita, come quando non è designato alcun argomento search_mode )
• -1 per cercare dall'ultimo al primo, ovvero dal basso verso l'alto
• 2 per una ricerca binaria in ordine crescente
• -2 per la ricerca binaria in ordine decrescente

Il modo migliore per comprendere la potenza e la versatilità della nuova funzione XLOOKUP è vederla in azione in un foglio di lavoro Excel. Nella figura seguente, ho un foglio di lavoro con una semplice tabella di dati di vendita 2019 organizzata per paese. Per utilizzare XLOOKUP per restituire le vendite totali da questa tabella nella cella E4 in base al paese che inserisci nella cella D4 del foglio di lavoro, segui questi passaggi:

Posiziona il cursore della cella nella cella E4 del foglio di lavoro

Fare clic sull'opzione Ricerca e riferimento nella scheda Formule seguita da CERCA.X nella parte inferiore del menu a discesa per aprire la finestra di dialogo Argomenti funzione.

Fare clic sulla cella D4 nel foglio di lavoro per immettere il riferimento di cella nella casella di testo dell'argomento Lookup_value.

Premi Tab per selezionare la casella di testo dell'argomento Lookup_array, quindi fai clic sulla cella A4 e tieni premuto Maiusc mentre premi la freccia Ctrl-giù per selezionare A4: A8 come intervallo da cercare (perché l'intervallo A3: B8 è definito come tabella dati Excel, Nella casella di testo viene visualizzato Table1[Paese] al posto dell'intervallo A4:A8).

Premi Tab per selezionare la casella di testo dell'argomento Return_array, quindi fai clic sulla cella B4 e tieni premuto Maiusc mentre premi Ctrl-freccia giù per selezionare B4: B8 come intervallo contenente i valori da restituire in base ai risultati della ricerca (che appare come Table1[Total Sales] nella casella di testo).

Fare clic su OK per inserire la formula CERCA.X nella cella E4.

Creazione di una formula con XLOOKUP nella cella E4 che restituisce le vendite in base al paese inserito nella cella D4.

Excel inserisce la formula XLOOKUP nella cella E4 del foglio di lavoro e restituisce 4900 come risultato perché il Costa Rica è attualmente inserito nella cella di ricerca D4 e, come puoi vedere nella tabella delle vendite 2019, questo è effettivamente il totale delle vendite effettuate per questo paese.

Poiché XLOOKUP funziona sia da destra a sinistra che da sinistra a destra, puoi utilizzare questa funzione anche per restituire il paese da questa tabella delle vendite in base a una determinata cifra di vendita. La figura seguente mostra come eseguire questa operazione. Questa volta, crei la formula XLOOKUP nella cella D4 e designi il valore immesso nella cella E4 (11.000, in questo caso) come argomento lookup_value.

Inoltre, inserisci -1 come argomento match_mode per sovrascrivere la corrispondenza esatta predefinita della funzione in modo che Excel restituisca il paese con una corrispondenza esatta al valore delle vendite immesso nella cella di ricerca E4 o quello con le vendite totali inferiori successive (Messico con $ 10.000 in questo caso poiché non vi è alcun paese in questa tabella con $ 11.000 di vendite totali). Senza designare un argomento match_mode per questa formula, Excel restituirebbe #NA come risultato, perché non esiste una corrispondenza esatta con $ 11.000 in questa tabella delle vendite.

Creazione di una formula con XLOOKUP nella cella D4 che restituisce il paese in base alle vendite inserite nella cella E4

Poiché la funzione XLOOKUP è ugualmente comoda sia per la ricerca in orizzontale per colonna che per la ricerca in verticale per riga, puoi usarla per creare una formula che esegue una ricerca bidirezionale (sostituendo la necessità di creare una formula che combini le funzioni INDICE e CONFRONTA come nel passato). La figura seguente, contenente la tabella del programma di produzione 2019 per i numeri di parte, da AB-100 a AB-103 per i mesi da aprile a dicembre, mostra come eseguire questa operazione.

Creazione di una formula con funzioni XLOOKUP nidificate per restituire il numero di unità prodotte per una parte in un determinato mese

Nella cella B12, ho creato la seguente formula:

=XCERCA(ricerca_parte,$A$3:$A$6,XCERCA(ricerca_data,$B$2:$J$2,$B$3:$J$6))

Questa formula inizia definendo una funzione XLOOKUP che cerca verticalmente per riga una corrispondenza esatta con l'inserimento della parte effettuata nella cella denominata part_lookup (cella B10, in questo caso) nell'intervallo di celle $A$3:$A$6 della tabella di produzione . Si noti, tuttavia, che l'argomento array_ritorno per questa funzione CERCA originale è di per sé una seconda funzione CERCA X.

Questa seconda funzione XLOOKUP nidificata ricerca l'intervallo di celle $B$2:$J$2 orizzontalmente per colonna per una corrispondenza esatta con la voce di data inserita nella cella denominata date_lookup (cella B11, in questo caso). L'argomento array_ritorno per questa seconda funzione CERCA.X nidificata è $B$3:$J$6, l'intervallo di celle di tutti i valori di produzione nella tabella.

Il modo in cui funziona questa formula è che Excel calcola prima il risultato della seconda funzione XLOOKUP nidificata eseguendo una ricerca orizzontale che, in questo caso, restituisce l'array nell'intervallo di celle D3: D6 della colonna Jun-19 (con i valori: 438, 153, 306 e 779) come risultato. Questo risultato, a sua volta, diventa l'argomento return_array per la funzione XLOOKUP originale che esegue una ricerca verticale per riga per una corrispondenza esatta con l'immissione del numero di parte effettuata nella cella B11 (denominata part_lookup). Poiché, in questo esempio, questa cella part_lookup contiene AB-102, la formula restituisce solo il valore di produzione Jun-19, 306, dal risultato della seconda funzione XLOOKUP successiva.

Ecco qua! Un primo sguardo a XLOOKUP, una nuova funzione di ricerca potente, versatile e abbastanza facile da usare che non solo può eseguire le ricerche di valore singolo eseguite dalle funzioni CERCA.VERT e CERCA.ORIZZ, ma anche le ricerche di valore bidirezionali eseguite combinando le Anche le funzioni INDICE e CONFRONTA.

* Sfortunatamente, la funzione CERCA.X non è compatibile con le versioni precedenti di Microsoft Excel che supportano solo le funzioni CERCA.VERT e CERCA.ORIZZ o compatibile con le versioni correnti che non la includono ancora come una delle funzioni di ricerca, come Excel 2019 ed Excel Online . Ciò significa che se condividi una cartella di lavoro contenente formule CERCA X con colleghi o clienti che utilizzano una versione di Excel che non include questa nuova funzione di ricerca, tutte queste formule restituiranno #NOME? valori di errore quando aprono il relativo foglio di lavoro.