XLOOKUP(): la funzione di ricerca più versatile di Excel

Se hai mai usato VLOOKUP() e ti sei bloccato perché dovevi cercare a sinistra, XLOOKUP() risolve il problema. È la nuova funzione di ricerca di Excel. Può sostituire VLOOKUP(), HLOOKUP() e persino la maggior parte delle formule INDEX() e MATCH() con un'unica formula semplice.

XLOOKUP() può cercare in qualsiasi direzione, riportare più di una colonna alla volta e mostrare un messaggio personalizzato quando un valore è mancante. 

In questa guida, vedremo prima la sintassi. Poi costruiremo vere formule passo dopo passo, usando un unico dataset per tutto il percorso. Tratteremo anche corrispondenze esatte e approssimative, come restituire più di una colonna, come gestire gli errori e come XLOOKUP() si confronta con VLOOKUP() e INDEX()/MATCH(). 

Come usare XLOOKUP() in Excel

Costruiamo da zero una vera formula XLOOKUP(). 

Il nostro dataset ha cinque prodotti, ciascuno con un ID, un nome, una categoria e un prezzo, dalle colonne A a D, con intestazioni nella riga 1.

Dataset per XLOOKUP(). Immagine dell'autore.

Diciamo che vogliamo trovare il prezzo di "P104". Vediamo come farlo passo dopo passo.

Cercare un singolo valore

Fai clic su una cella vuota dove vuoi che appaia il risultato. Io uso F2. E digita la seguente formula:

=XLOOKUP("P104", A2:A6, D2:D6)

In questa formula: 

• "P104" è il lookup_value, l'elemento che abbiamo cercato

• A2:A6 è il lookup_array, l'intervallo in cui Excel ha cercato

• D2:D6 è il return_array, l'intervallo da cui Excel ha prelevato la risposta

Excel cerca nella colonna A, “Product ID”, "P104", lo trova nella riga 5 e restituisce il valore corrispondente dalla colonna D: $45.

Invece di digitare direttamente "P104", puoi fare riferimento a una cella, così puoi cambiare il valore di ricerca senza modificare la formula stessa. Nel nostro caso, la cella A5 contiene "P104", quindi la formula diventa:

=XLOOKUP(A5, A2:A6, D2:D6)

Usa XLOOKUP() in Excel. Immagine dell'autore.

Ora puoi digitare qualsiasi ID prodotto in A5 e il prezzo si aggiorna automaticamente.

Restituire un valore da un'altra colonna

Questo passaggio mostra la vera differenza tra XLOOKUP() e VLOOKUP(). Non sei vincolato a restituire un valore da una colonna a destra della tua colonna di ricerca.

Diciamo che invece del prezzo, vuoi la categoria per "P102". Stessa impostazione, punta semplicemente il return_array alla colonna C, “Category”, invece:

=XLOOKUP(A3, A2:A6, C2:C6)

Qui, A3 restituisce Home. Se cambi in A5, si aggiorna a Electronics. Allo stesso modo, cambia di nuovo il return_array per prelevare il nome prodotto, il prezzo o qualsiasi altra colonna, tutto utilizzando lo stesso lookup_value e lookup_array.

XLOOKUP() restituisce un valore da un'altra colonna. Immagine dell'autore.

Con VLOOKUP(), conteresti le colonne dalla tua colonna di ricerca e modificheresti un numero di indice di colonna ogni volta che vuoi un risultato diverso. Con XLOOKUP(), indichi direttamente la colonna desiderata. 

Copiare la formula su più righe

Finora abbiamo cercato un valore alla volta. Ma la maggior parte dei fogli di calcolo reali deve cercare molti valori in una volta sola, ad esempio un elenco completo di ID ordine, ognuno dei quali necessita di un prezzo corrispondente.

Ecco come impostarlo: la colonna A, a partire da A2, contiene già il nostro elenco di Product ID. Vogliamo che i prezzi corrispondenti appaiano accanto a ciascuno, in colonna F.

Per farlo, fai clic su F2 e digita la formula, facendo riferimento ad A2 per il valore di ricerca in questo modo:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Ora fai di nuovo clic su F2, quindi trascina il riempimento tramite quadratino (il piccolo quadrato nell'angolo in basso a destra della cella) verso il basso attraverso il resto delle righe.

Excel copia la formula verso il basso e ogni riga ora restituisce il prezzo corretto per il proprio Product ID. L'ID di A2 ottiene il suo prezzo in F2, l'ID di A3 ottiene il suo prezzo in F3 e così via.

Copia la formula verso il basso in altre celle. Immagine dell'autore.

Nota i simboli del dollaro attorno a A2:A6 e D2:D6. Questo si chiama riferimento assoluto. Blocca l'intervallo in posizione, così non si sposta quando copi la formula in basso. 

Questo è importante qui, perché ogni riga deve cercare lo stesso elenco di prodotti, solo con un valore di ricerca diverso ogni volta.

Come usare corrispondenza esatta e approssimativa in XLOOKUP()

Per impostazione predefinita, XLOOKUP() cerca una corrispondenza esatta. Se non la trova, restituisce un errore, a meno che tu non abbia impostato l'argomento if_not_found che abbiamo trattato prima. 

Ma XLOOKUP() può anche trovare la corrispondenza più vicina, usando l'argomento match_mode. 

Corrispondenza esatta (predefinita)

Non devi digitare nulla in più per questo. Ogni formula che abbiamo costruito finora usa già la corrispondenza esatta, poiché è il comportamento predefinito di XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel cerca il valore esatto in A2 e restituisce un risultato solo se trova quella corrispondenza precisa da qualche parte nell'intervallo di ricerca.

Se vuoi scriverlo esplicitamente, aggiungi uno zero come quarto argomento:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() restituisce una corrispondenza esatta. Immagine dell'autore.

Noti lo spazio prima dello 0? Questo salta if_not_found, dato che qui lo lasciamo vuoto, e passa direttamente a match_mode. La maggior parte delle volte, puoi omettere del tutto questo argomento e lasciare che Excel usi di default la corrispondenza esatta.

Corrispondenza approssimativa

Ora, supponiamo che i tuoi dati non abbiano un valore per ogni possibile ricerca. Pensa a una struttura provvigionale in cui i venditori guadagnano aliquote diverse a seconda dell'intervallo in cui rientrano le vendite totali. Non avrai una riga per ogni importo in dollari, solo il punto di partenza di ogni intervallo.

Qui è utile una corrispondenza approssimativa. 

Imposta match_mode a -1 e XLOOKUP() trova il valore più grande che è minore o uguale al tuo valore di ricerca.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Supponi che il tuo intervallo di ricerca contenga soglie di vendita di $0, $5.000 e $10.000, ciascuna legata a un'aliquota provvigionale diversa. 

Un venditore con $7.500 di vendite non corrisponde esattamente a nessuna riga. Con match_mode impostato a -1, XLOOKUP() trova $5.000, la soglia più alta che non supera $7.500, e restituisce l'aliquota provvigionale di quella riga.

Imposta invece match_mode a 1 e XLOOKUP() capovolge la logica. Trova il valore più piccolo che è maggiore o uguale al tuo valore di ricerca. Questo è utile per cose come le fasce di peso di spedizione, dove arrotondi alla fascia successiva verso l'alto anziché verso il basso.

XLOOKUP() restituisce una corrispondenza approssimativa. Immagine dell'autore.

Nota: la corrispondenza approssimativa presuppone che il tuo intervallo di ricerca sia ordinato, crescente per -1, decrescente per 1. Se non è ordinato correttamente, XLOOKUP() può restituire il risultato sbagliato senza generare un errore, il che è peggio. Quindi, controlla sempre due volte l'ordinamento prima di affidarti a questa modalità.

Come cercare valori a sinistra con XLOOKUP()

VLOOKUP() cerca solo nella colonna più a sinistra del tuo intervallo e restituisce un valore da una colonna alla sua destra; non può fare il contrario. Il suo terzo argomento non è affatto un intervallo; è un indice di colonna contato dal bordo sinistro. 

Non c'è modo di dirgli "vai una colonna a sinistra", quindi se il valore di cui hai bisogno è a sinistra della tua colonna di ricerca, sei costretto a riorganizzare i dati o aggiungere una colonna di supporto solo per far funzionare la ricerca.

XLOOKUP() non ha questa limitazione, poiché indichi intervalli separati per la ricerca e per il ritorno.

Prendi il nostro elenco prodotti: Product ID è in colonna A, Product Name in colonna B. 

Supponi di avere un nome prodotto e voler trovare il suo ID; l'inverso di ciò che può fare VLOOKUP().

In C9, inserisci Bluetooth Speaker (un nome presente nell'elenco). In E2, inserisci:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() cerca nella colonna B "Bluetooth Speaker", lo trova alla riga 5 e restituisce "P104" dalla colonna A, anche se la colonna A è a sinistra della colonna B. 

XLOOKUP() cerca i valori a sinistra, a differenza di VLOOKUP(). Immagine dell'autore.

Come restituire più colonne con XLOOKUP()

XLOOKUP() può restituire più colonne alla volta, in un'unica formula, usando una funzione chiamata matrici dinamiche. Per farlo, punta return_array a un intervallo invece che a una singola colonna, e XLOOKUP() riversa l'intera riga corrispondente su tante celle quante ne servono. 

Proviamolo con il nostro elenco prodotti. 

In C9, digito un Product ID, per esempio "P104". In E2 inserisco:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Nota che qui il return_array è B2:D6, largo tre colonne, che copre Product Name, Category e Price. Una volta premuto Invio, XLOOKUP() trova "P104" in colonna A, quindi restituisce Bluetooth Speaker, Electronics e $45 tutti insieme, riversandoli in E9, F9 e G9.

XLOOKUP() restituisce più colonne con un'unica formula. Immagine dell'autore.

Questo è ciò che significa intervallo di riversamento (spill range). E9 contiene la formula effettiva e F9 e G9 si compilano da sole, senza formule proprie. 

Fai clic su F9 e la barra della formula mostra una versione in grigio della formula originale, non qualcosa digitato lì direttamente. Prova a digitare in F9 mentre l'intervallo riversato di E9 è attivo ed Excel genererà un errore di spill. 

Vedremo come correggerlo nella sezione Errori comuni.

Come gestire i valori mancanti in XLOOKUP()

Per impostazione predefinita, XLOOKUP() genera un errore #N/A quando non riesce a trovare una corrispondenza. L'argomento if_not_found può risolvere questo mostrando ciò che vogliamo invece dell'errore predefinito.  

Supponi che in C9 digiti un Product ID che non esiste nei miei dati, come "P999".

Restituire un messaggio personalizzato

In G2, inserisco:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Poiché "P999" non esiste nella colonna A, XLOOKUP() salta completamente l'errore e restituisce "Not Found". Cambia la frase come preferisci, ad esempio "Check Product ID" o "No Match".

Mostra un messaggio personalizzato invece dell'errore predefinito. Immagine dell'autore.

Restituire una cella vuota

A volte vuoi una cella vuota senza testo. Per questo, usa due virgolette senza nulla in mezzo, così:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Puoi usarlo quando stai usando questa formula in un report in cui un "Not Found" fuori posto sembrerebbe disordinato accanto a una colonna di valori reali.

Mostra una cella vuota invece dell'errore predefinito. Immagine dell'autore.

Lasciare che resti un errore vero e proprio

Se salti del tutto if_not_found, XLOOKUP() torna a #N/A. Non è sempre un male. Se stai costruendo una formula per uso personale e vuoi intercettare subito un errore di battitura o una voce mancante, vedere comparire #N/A è più utile di una silenziosa cella vuota che nasconde il problema.

Nota: if_not_found intercetta solo le corrispondenze mancanti. Se il tuo lookup_array e il return_array hanno dimensioni diverse, per esempio, XLOOKUP() genera comunque un errore #VALUE! e if_not_found non lo bloccherà. Tratteremo questo caso specifico nella sezione Errori comuni più avanti.

Funzionalità avanzate di XLOOKUP()

Vediamo le funzionalità avanzate di XLOOKUP()

Cerca dal basso verso l'alto

Imposta search_mode a -1 e XLOOKUP() scansiona dal fondo del tuo intervallo verso l'alto, invece che dall'alto verso il basso. Usalo quando i tuoi dati hanno valori duplicati e vuoi la voce più recente, non la prima.

Diciamo che nel mio dataset ho tre Customer ID duplicati. Nella cella E2, inserisco la formula predefinita che userà l'approccio dall'alto verso il basso: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Questo viene calcolato dall'inizio della tabella e mostra il risultato dalla prima corrispondenza. 

Ma se voglio cercare dal basso, userò questa formula: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

In questa formula, -1 indica a Excel di iniziare a cercare dal fondo del dataset. E questo restituirà 340, poiché è il primo valore corrispondente partendo dal basso.  

XLOOKUP() scansiona i dati dal basso verso l'alto. Immagine dell'autore.

Modalità di ricerca binaria

Imposta search_mode a 2 e XLOOKUP() passerà dal controllare una riga alla volta a una ricerca binaria. 

Ecco come funziona: 

• Guarda per prima l'elemento centrale del tuo intervallo. 

• Se non è una corrispondenza, scarta la metà in cui il valore non può essere, quindi guarda il centro di ciò che rimane. 

• Continua così, dimezzando i dati ogni volta, finché non trova la corrispondenza. 

• Imposta search_mode a -2 e funziona allo stesso modo, iniziando però dall'ultimo elemento invece che dal primo.

Qui in E2, inserisci:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Questa formula restituisce invece 200, una riga completamente diversa.

La modalità di ricerca binaria dà il risultato sbagliato. Immagine dell'autore.

Perché la ricerca binaria funziona solo se il tuo lookup_array è ordinato. Poiché la nostra colonna Customer ID non è ordinata, è C100, C101, C100, C102, C100, e la ricerca binaria scarta la metà sbagliata e dà una corrispondenza errata. 

Ora vediamo cosa succede quando i dati sono effettivamente ordinati. 

Ordina lo stesso registro per Customer ID, in ordine crescente (tutti e tre i C100 all'inizio) ed esegui di nuovo la stessa formula. 

La modalità di ricerca binaria funziona con dati ordinati. Immagine dell'autore.

Questa volta, E2 restituisce 120, in linea con ciò che ti darebbe una ricerca normale. Ecco la differenza. La ricerca binaria salta solo i dati che può escludere con certezza, e questo vale solo quando i dati sono ordinati in partenza. 

Formule XLOOKUP() annidate

Annida una XLOOKUP() dentro un'altra quando il risultato di una ricerca deve alimentare una seconda ricerca, invece di un intervallo fisso.

Diciamo che vuoi trovare un prezzo, ma da quale colonna prelevarlo dipende da una ricerca separata, ad esempio il livello di prezzo del cliente.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

In questa formula, la XLOOKUP() interna determina per prima quale intervallo di colonna prelevare, in base a D9, quindi la XLOOKUP() esterna usa quel risultato come return_array. 

XLOOKUP() annidate. Immagine dell'autore.

XLOOKUP() vs. VLOOKUP()

Abbiamo visto in dettaglio come funziona XLOOKUP(). Ecco il suo confronto con VLOOKUP().

Ci sono due cose che vale la pena notare rapidamente: 

• VLOOKUP() per impostazione predefinita usa la corrispondenza approssimativa. Se dimentichi il quarto argomento, non mostrerà un errore; restituirà semplicemente il valore sbagliato in silenzio.

• Se inserisci una colonna in un foglio con VLOOKUP(), le formule iniziano silenziosamente a prelevare il valore sbagliato. XLOOKUP() evita questo facendo riferimento direttamente agli intervalli anziché contare le colonne.

Nulla di tutto ciò rende VLOOKUP() obsoleto. Funziona ancora bene per fogli semplici e statici. Ma per qualsiasi cosa costruita d'ora in avanti, XLOOKUP() è la scelta predefinita più sicura.

XLOOKUP() vs. INDEX() e MATCH()

Prima che esistesse XLOOKUP(), INDEX() e MATCH() insieme erano lo standard per aggirare i limiti di VLOOKUP(). Usa due funzioni invece di una. 

• MATCH() trova la posizione del tuo valore di ricerca. 

• INDEX() quindi usa quella posizione per prelevare il risultato da dove vuoi: sinistra o destra, non importa.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Confrontalo con la versione XLOOKUP() che abbiamo usato in tutto l'articolo:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() funziona solo in Microsoft 365 ed Excel 2021 in poi, come abbiamo visto prima. Se la tua cartella di lavoro deve aprirsi in Excel 2019 o 2016, o la condividi con persone su versioni più vecchie, INDEX() e MATCH() funzionano ancora ovunque.

C'è anche un caso più di nicchia. 

INDEX() e MATCH() separano "trova la posizione" da "restituisci il valore", il che aiuta in alcune formule complesse annidate. Questo capita raramente, quindi la maggior parte dei lettori non ne avrà bisogno.

Al di fuori di questi due casi, XLOOKUP() è la scelta migliore per una nuova cartella di lavoro. 

Errori comuni di XLOOKUP() e come risolverli

Ecco un riferimento rapido per i problemi che è più probabile incontrare e come risolvere ciascuno.

Errori #N/A

Questa è la risposta predefinita di XLOOKUP() quando non trova una corrispondenza. Mi è capitato prima, quando il mio lookup_value e il lookup_array non erano allineati, e XLOOKUP() non aveva nulla a cui abbinare nella colonna A.

Per risolvere: ricontrolla che il tuo lookup_value esista davvero nel tuo lookup_array, scritto e formattato allo stesso modo. Se le corrispondenze mancanti sono previste e normali, usa l'argomento if_not_found per sostituire #N/A con qualcosa di più utile, come "Not Found" o una cella vuota, come abbiamo visto prima. 

Intervallo di ricerca errato

Succede quando il tuo lookup_array e il return_array non sono allineati correttamente: righe sbagliate, colonne sbagliate o intervalli di dimensioni non corrispondenti. 

A seconda della versione di Excel o Sheets che usi, questo può comparire come errore #VALUE!, o a volte come #N/A con un messaggio separato che spiega il vero problema. Non lasciare che il #N/A ti inganni facendoti pensare a una corrispondenza mancante. 

Per risolvere: assicurati che lookup_array e return_array coprano lo stesso numero di righe o colonne. Se lookup_array è A2:A5, il return_array deve coprire anch'esso le righe dalla 2 alla 5, non le righe 4 e 5 o qualsiasi altro intervallo non allineato.

Mancata corrispondenza dei tipi di dati

VLOOKUP() può generare un errore perché si aspettava un numero ma ha ottenuto invece testo. XLOOKUP() può incorrere in un problema simile. Se il tuo lookup_value è un numero, ma il tuo lookup_array memorizza quegli stessi valori come testo, o viceversa, XLOOKUP() non troverà una corrispondenza, anche se i valori sembrano identici sullo schermo.

Per risolvere: controlla che il tuo lookup_value e il lookup_array usino lo stesso tipo di dato. I numeri memorizzati come testo di solito sono allineati a sinistra in una cella anziché a destra. Incapsula il tuo lookup_value in VALUE() per convertire testo in numero, oppure usa TEXT() per fare l'inverso, a seconda della direzione della mancata corrispondenza.

Errori di spill

Succede quando il tuo return_array copre più di una colonna, ma le celle di cui XLOOKUP() ha bisogno non sono vuote. Excel ha bisogno di spazio libero accanto alla cella della tua formula per riversare i risultati. Se c'è già qualcosa, l'intera formula fallisce.

Per risolvere: svuota le celle dove devono andare i risultati, poi inserisci la tua formula. Se ti serve qualcosa in quelle celle, sposta la formula XLOOKUP() altrove dove ci sia spazio libero attorno.

Considerazioni finali 

Ora sai come usare XLOOKUP() per valori singoli, più colonne, dati mancanti e persino ricerche annidate su due intervalli. È un bel po' di terreno coperto.

D'ora in avanti, fai di XLOOKUP() il tuo predefinito per qualsiasi nuova cartella di lavoro. È più flessibile di VLOOKUP(), più leggibile di INDEX/MATCH() e ti evita di incapsulare ogni formula in IFERROR().

Se vuoi approfondire, vale la pena imparare anche INDEX() e MATCH() separatamente.