XLOOKUP() vs. VLOOKUP(): un confronto per chi usa Excel

Sintassi di XLOOKUP() e VLOOKUP()

Ora che hai una comprensione di base di entrambe le funzioni, vediamo la loro sintassi e come usarle in applicazioni reali. 

Sintassi di VLOOKUP()

Diamo un’occhiata alla sintassi di VLOOKUP(). 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Nella formula sopra:

• lookup_value è il valore che vuoi cercare. Può essere un numero, un testo o un riferimento a una cella che contiene il valore da cercare.

• table_array è l’intervallo di celle che contiene i dati. La prima colonna di questo intervallo dovrebbe contenere la search_key.

• col_index_num è il numero della colonna nell’intervallo da cui vuoi recuperare il valore. 

• range_lookup è dove inserisci un valore logico (TRUE o FALSE).

Sintassi di XLOOKUP()

Ora diamo un’occhiata alla sintassi di xlookup(). Come possiamo vedere, XLOOKUP() ha più argomenti rispetto a VLOOKUP().

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Nella formula sopra:

• lookup_value è il valore che vuoi cercare.

• lookup_array è l’intervallo di celle in cui viene cercato il valore.

• return_array è l’intervallo di celle che contiene il valore che vuoi restituire.

• if_not_found (opzionale) restituisce un valore se non viene trovato alcun riscontro. Se non specificato, restituisce #N/A.

• match_mode (opzionale) determina il tipo di corrispondenza da eseguire. 0 per corrispondenza esatta (predefinito). -1 per corrispondenza esatta o elemento immediatamente più piccolo. 1 per corrispondenza esatta o elemento immediatamente più grande. 2 per corrispondenza con caratteri jolly.

• search_mode (opzionale) determina la modalità di ricerca. 1 per cercare dalla prima all’ultima occorrenza (predefinito). -1 per cercare dall’ultima alla prima. 2 per eseguire una ricerca binaria in ordine crescente. -2 per eseguire una ricerca binaria in ordine decrescente.

Differenze principali: XLOOKUP() vs. VLOOKUP()

Esploriamo ora alcune differenze chiave tra le funzioni XLOOKUP() e VLOOKUP().

Modalità di corrispondenza esatta 

XLOOKUP() usa di default la corrispondenza esatta, mentre VLOOKUP() richiede di specificare FALSE per ottenere la corrispondenza esatta.

Differenza nella corrispondenza esatta tra XLOOKUP() e VLOOKUP(). Fonte: immagine dell’autore.

In VLOOKUP() non ho specificato il valore range_lookup (4° argomento), quindi ha restituito il valore più vicino, che non è il risultato desiderato. XLOOKUP(), invece, restituisce per impostazione predefinita la corrispondenza esatta.

Direzione di ricerca

VLOOKUP() può cercare solo a destra della prima colonna nell’intervallo selezionato. XLOOKUP(), invece, può cercare valori in qualsiasi direzione.

Differenza nella direzione di ricerca tra VLOOKUP() e XLOOKUP(). Fonte: immagine dell’autore.

Qui, VLOOKUP() non ha trovato i voti perché può cercare i dati solo a destra del lookup_value (nome dello studente). Al contrario, XLOOKUP() trova i voti di Robin perché può cercare in entrambe le direzioni (sinistra o destra).

Riferimenti agli array

Con VLOOKUP() devi definire l’intero intervallo di dati (tabella) e specificare il numero di colonna (indice di colonna) che contiene l’output desiderato (colonna di ritorno) all’interno di un’unica formula. XLOOKUP() è più flessibile: ti permette di definire array separati per il valore da cercare e per i dati da restituire. 

Riferimenti agli array tra VLOOKUP() e XLOOKUP(). Fonte: immagine dell’autore.

Ricerca orizzontale

VLOOKUP() non ti consente di eseguire ricerche orizzontali. Se vuoi una ricerca orizzontale, dovresti usare HLOOKUP(). XLOOKUP() unifica queste due funzioni perché esegue sia ricerche verticali sia orizzontali, così non devi usare funzioni separate per direzioni diverse.

Differenza nella ricerca orizzontale tra VLOOKUP() e XLOOKUP(). Fonte: immagine dell’autore.

Gestione di inserimenti/eliminazioni di colonne 

Le modifiche alle colonne influiscono su VLOOKUP() a causa del column_index_num codificato in modo rigido. Invece, XLOOKUP() non è influenzata dalle modifiche alle colonne e continua a funzionare senza dover adattare la formula.

XLOOKUP() non viene influenzata, mentre VLOOKUP() fallisce. Fonte: immagine dell’autore.

Ordinamento e ricerca

Puoi ordinare VLOOKUP() in ordine crescente, ma questo può creare difficoltà con dati non ordinati. XLOOKUP(), invece, può usare l’argomento search_mode per cercare sia in ordine crescente sia decrescente.

Gestione di inserimenti/eliminazioni in XLOOKUP() e VLOOKUP(). Fonte: immagine dell’autore.

Messaggi di errore personalizzati

VLOOKUP() mostra l’errore #N/A quando non trova corrispondenze. XLOOKUP() ha un parametro opzionale if_not_found che ti consente di personalizzare il testo di output se un valore non viene trovato. 

Personalizzazione dei messaggi di errore in XLOOKUP() e VLOOKUP(). Fonte: immagine dell’autore.

Come vedi, VLOOKUP() non ha trovato lo studente nell’elenco e ha mostrato un errore #N/A. Allo stesso modo, la funzione XLOOKUP() non ha trovato il nome dello studente. Tuttavia, a differenza di VLOOKUP(), XLOOKUP() restituisce un messaggio specifico quando non c’è corrispondenza.

Restituire più valori

VLOOKUP() può restituire un solo valore alla volta. XLOOKUP(), invece, può recuperare valori da più colonne contemporaneamente. Ecco perché puoi usare una sola formula XLOOKUP() al posto di più formule VLOOKUP().

Restituire più valori con XLOOKUP() e VLOOKUP(). Fonte: immagine dell’autore.

Modalità di ricerca

VLOOKUP() scorre un elenco dall’inizio e restituisce solo il primo valore che corrisponde a ciò che cerchi. XLOOKUP(), invece, può esaminare un elenco in entrambe le direzioni (dall’alto in basso o dal basso in alto) e trovare rapidamente elementi in elenchi estesi.

Differenza tra VLOOKUP() e XLOOKUP(). Fonte: immagine dell’autore.

Qui, VLOOKUP() recupera la prima occorrenza, che è del primo semestre. Con XLOOKUP(), invece, uso il codice di modalità di ricerca -1 per cercare dal basso verso l’alto.

Esempi pratici e casi d’uso

Vediamo ora alcuni esempi pratici e casi d’uso.

Usare una ricerca senza dover riordinare una tabella

Ho un elenco di nomi di studenti e punteggi, e voglio trovare il punteggio di ciascuno in base al nome. Per questo, userò sia XLOOKUP() sia VLOOKUP() per mostrarti le differenze. 

Usando XLOOKUP(), inserisco la seguente formula:

=XLOOKUP(D8,B2:B5,A2:A5)

In questa formula:

• D8 contiene il valore da cercare, che è Charlie.

• L’intervallo B2:B5 contiene i nomi degli studenti. La funzione XLOOKUP() cercherà il valore in D8 all’interno di questo intervallo.

• A2:A5 è l’intervallo che contiene i punteggi corrispondenti ai nomi degli studenti. 

Usando VLOOKUP(), inserisco la seguente formula:

=VLOOKUP(D4,A1:B5,1,0)

In questa formula:

• D4 contiene il valore da cercare, ovvero Charlie.

• L’intervallo A1:B5 cerca in tutte le colonne (Scores, Student Name).

• 1 restituisce il valore dalla prima colonna dell’intervallo (Scores).

• 0 recupera la corrispondenza esatta.

VLOOKUP() restituisce un errore #N/A ma XLOOKUP() lo gestisce. Fonte: immagine dell’autore.

Come puoi vedere, VLOOKUP() mostra un errore #N/A perché non può cercare a sinistra.

Creare un output personalizzato quando un valore non viene trovato

Ho un elenco di studenti con i loro ID, nomi e voti. Durante la ricerca, ho inserito per errore un ID studente che non era nell’elenco. Per gestire questo caso, voglio che compaia un messaggio personalizzato ogni volta che l’ID non viene trovato. Userò sia XLOOKUP() sia VLOOKUP() per mostrarti le differenze. 

Usando XLOOKUP(), inserisco la seguente formula:

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 

In questa formula:

• E9 contiene il valore che vogliamo cercare. Nel mio caso è 14256.

• A2:A5 è l’intervallo di celle contenente il valore di ricerca (Student ID).

• B2:B5 cerca il punteggio dell’ID 14526.

• Student not Found viene visualizzato se il punteggio non è nell’elenco. 

Usando VLOOKUP(), inserisco la seguente formula:

(=VLOOKUP(E6, A2:C5,2,0)) 

In questa formula:

• E6 si riferisce allo Student ID, cioè ciò che sto cercando.

• A2:C5 si riferisce all’intervallo che contiene tutti i dati della tabella.

• 2 indica il numero della colonna da cui verranno prelevati i dati.

• 0 recupera la corrispondenza esatta.

XLOOKUP() restituisce un messaggio, VLOOKUP() no. Fonte: immagine dell’autore.

Come puoi vedere, 14256 non esiste nell’intervallo A2:A5, quindi XLOOKUP() restituisce il messaggio personalizzato Student not found. VLOOKUP(), invece, genera un errore #N/A. 

Ricerca dal basso verso l’alto

Voglio trovare lo stipendio di Sarah dell’anno più recente. Per questo, userò sia XLOOKUP() sia VLOOKUP() per mostrarti le differenze. 

Usando XLOOKUP(), inserisco la seguente formula:

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)

In questa formula:

• F8 contiene il valore da cercare, Sarah.

• B2:B10 guarda nell’intervallo e cerca Sarah. 

• C2:C10 recupera i dati.

• -1 effettua la ricerca a partire dal fondo della colonna. 

Usando VLOOKUP(), inserisco la seguente formula:

=VLOOKUP(F4, B2:C10,2,0)

In questa formula:

• F4 contiene il valore da cercare, Sarah.

• B2:C10 cerca il valore da cercare all’interno di questo intervallo.

• 2 indica il numero della colonna Salary da cui prelevare i dati.

• 0 recupera la corrispondenza esatta.

Differenza tra VLOOKUP() e XLOOKUP() nella modalità di ricerca. Fonte: immagine dell’autore.

Come puoi vedere, XLOOKUP() trova lo stipendio desiderato per l’anno desiderato, mentre VLOOKUP() mostra solo il primo valore corrispondente. Ci sono riuscito perché XLOOKUP() consente di cercare dal basso verso l’alto.

Prestazioni e compatibilità

Confrontiamo ora prestazioni e compatibilità di entrambe le funzioni. 

Prestazioni su dataset di grandi dimensioni

VLOOKUP() può essere più lenta con dataset grandi, soprattutto se la colonna di ricerca non è ordinata e l’argomento range lookup è impostato su FALSE per la corrispondenza esatta. Questo perché VLOOKUP() scansiona il dataset in modo sequenziale finché non trova una corrispondenza, operazione che può richiedere tempo su tabelle grandi. XLOOKUP() offre prestazioni migliori con dataset ampi. Gestisce le corrispondenze esatte in modo più efficiente e cerca orizzontalmente e verticalmente senza necessità di ordinamento. 

Compatibilità con diverse versioni di Excel

A questo punto potresti pensare di abbandonare VLOOKUP() dopo aver visto i vantaggi di XLOOKUP(). Ma lasciami chiarire: sebbene XLOOKUP() sia una funzionalità molto più veloce ed eccellente, ci sono casi in cui non è disponibile. Questo perché XLOOKUP() è una funzione più recente che funziona solo in Excel 2021 e Microsoft 365 (dal 2019). Se collabori con persone che usano versioni precedenti di Excel, dovrai usare VLOOKUP() e altre funzioni al posto di XLOOKUP().

Tabella riepilogativa

Creiamo una tabella riepilogativa per un riferimento rapido. 

Considerazioni finali

XLOOKUP() supera VLOOKUP(), soprattutto nella gestione di dataset grandi o di esigenze di ricerca complesse. La sua flessibilità, tra cui ricerca bidirezionale, recupero di risultati multipli e adattabilità ai cambiamenti dei dati, la rende uno strumento molto utile. Tuttavia, VLOOKUP() mantiene il suo ruolo, in particolare per chi usa versioni più vecchie di Excel o preferisce un approccio più semplice alle ricerche di base. La scelta migliore dipende in definitiva dalle tue esigenze specifiche e dalla versione di Excel.

Se sei alle prime armi con Excel o vuoi costruire basi solide, il nostro corso Introduction to Excel e lo skill track Excel Fundamentals sono ottimi punti di partenza per padroneggiare le basi.