M365Con 2027 – conferenza online gratuita · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

XLOOKUP in Excel: sintassi, esempi e perché batte VLOOKUP

By · · Updated

XLOOKUP in Excel spiegato: la sintassi, ogni argomento, esempi per corrispondenza esatta, con caratteri jolly e per l'ultima corrispondenza, più colonne e differenze rispetto a VLOOKUP.

Automatically translated with AI from the original post. Read the English original

XLOOKUP in Excel trova un valore in un intervallo e restituisce l'elemento corrispondente da un altro intervallo: =XLOOKUP(valore_ricerca; matrice_ricerca; matrice_restituita). Inserisci un ID dipendente, indica la colonna degli ID, indica la colonna dei nomi e ottieni il nome. Questo articolo è pensato per gli utenti di Excel che scrivono ancora VLOOKUP per abitudine, o che hanno appena visto XLOOKUP nel file di un collega. Troverai la sintassi, i sei argomenti, esempi svolti per i casi che si presentano al lavoro, il confronto con VLOOKUP e un elenco degli errori più probabili.

Punti chiave

  • XLOOKUP richiede un valore di ricerca, un intervallo di ricerca e un intervallo di restituzione: =XLOOKUP(valore_ricerca; matrice_ricerca; matrice_restituita). Trova la prima corrispondenza e restituisce l'elemento dell'intervallo di restituzione.
  • Può cercare a sinistra della colonna di ricerca e usa per impostazione predefinita la corrispondenza esatta: due cose che VLOOKUP non può fare, o non fa per impostazione predefinita.
  • Il quarto argomento, se_non_trovato, sostituisce l'errore #N/D con un testo a tua scelta. Senza di esso, una ricerca senza corrispondenza restituisce #N/D.
  • Con modalità_corrispondenza 2 si attivano i caratteri jolly, -1 e 1 trovano l'elemento successivo più piccolo o più grande, e modalità_ricerca -1 cerca dall'ultimo elemento al primo.
  • La pagina di Microsoft elenca XLOOKUP per Microsoft 365, 2021 e 2024, mentre le note su Excel 2016 e 2019 sono in contrasto tra loro: prima di affidarti alla funzione, verifica i file condivisi.

La sintassi e le opzioni qui sotto seguono la pagina di Microsoft sulla funzione XLOOKUP. Dove uno schema non compare in quella pagina, il testo lo indica. È una funzione di Excel ed è quella che Microsoft stessa suggerisce come scelta migliore quando apri la guida di VLOOKUP.

La sintassi e i sei argomenti

La formula completa si presenta così:

=XLOOKUP(valore_ricerca; matrice_ricerca; matrice_restituita; [se_non_trovato]; [modalità_corrispondenza]; [modalità_ricerca])

I primi tre argomenti sono obbligatori. Gli ultimi tre sono facoltativi.

  • valore_ricerca è ciò che stai cercando, ad esempio un ID nella cella D2. Se lo ometti, XLOOKUP restituisce le celle vuote che trova nella matrice di ricerca.
  • matrice_ricerca è l'intervallo o la matrice in cui cercare, ad esempio A2:A100.
  • matrice_restituita è l'intervallo o la matrice da cui restituire il valore, ad esempio B2:B100.
  • se_non_trovato è il testo da mostrare quando non esiste una corrispondenza valida. Se lo ometti, ottieni #N/D.
  • modalità_corrispondenza imposta il funzionamento della corrispondenza. 0 è la corrispondenza esatta ed è il valore predefinito. -1 indica una corrispondenza esatta o, in mancanza, l'elemento successivo più piccolo. 1 indica una corrispondenza esatta o l'elemento successivo più grande. 2 è una corrispondenza con caratteri jolly, dove *, ? e ~ hanno un significato speciale.
  • modalità_ricerca imposta la direzione della ricerca. 1 cerca dal primo elemento all'ultimo ed è il valore predefinito. -1 cerca dall'ultimo al primo. 2 e -2 sono ricerche binarie su dati ordinati in modo crescente o decrescente. Microsoft avverte che, con le modalità binarie, dati non ordinati restituiscono risultati non validi.

Mantieni la matrice di ricerca e la matrice di restituzione della stessa dimensione. Se la matrice di ricerca copre le righe da 2 a 100, anche quella di restituzione deve coprire le righe da 2 a 100. La pagina di Microsoft non lo indica come regola esplicita, ma intervalli non allineati sono il motivo più comune per cui una ricerca restituisce la riga sbagliata o un errore.

Ricerche di base e l'argomento "non trovato"

Supponiamo che gli ID dei dipendenti si trovino in A2:A100 e i nomi in B2:B100. Digiti in D2 l'ID che ti interessa.

  1. Fai clic sulla cella in cui deve comparire il risultato, per esempio E2.
  2. Inserisci =XLOOKUP(D2,A2:A100,B2:B100).
  3. Premi Invio.

Ottieni il nome associato all'ID. XLOOKUP restituisce l'elemento corrispondente alla prima occorrenza trovata. Se l'ID non esiste, ottieni #N/D.

Per mostrare un messaggio più chiaro, aggiungi il quarto argomento:

=XLOOKUP(D2,A2:A100,B2:B100,"Nessun record trovato")

Ora un ID non presente mostra "Nessun record trovato". Usalo nei fogli che altri leggeranno. Attenzione però: un messaggio di questo tipo può nascondere un problema reale. Se ogni riga riporta "Nessun record trovato", il problema è negli intervalli, non nei dati, quindi prova prima con un ID di cui conosci la presenza.

Ricerca verso sinistra

VLOOKUP non può restituire un valore da una colonna situata a sinistra della colonna di ricerca. XLOOKUP sì, perché gli intervalli di ricerca e di restituzione sono indipendenti. Se il nome si trova nella colonna C e l'ID che ti serve è nella colonna A:

=XLOOKUP(F2,C2:C100,A2:A100)

La pagina di Microsoft lo dice esplicitamente: la colonna di ricerca può trovarsi da entrambi i lati della colonna di restituzione.

Modalità di corrispondenza: caratteri jolly e valori più vicini

Corrispondenze con caratteri jolly

Imposta match_mode su 2 per usare i caratteri jolly. L'asterisco rappresenta un numero qualsiasi di caratteri, il punto interrogativo esattamente uno. La tilde permette di cercare un asterisco o un punto interrogativo letterali.

=XLOOKUP("Jan*",B2:B100,A2:A100,"Non trovato",2)

Questa formula restituisce la prima voce che inizia con "Jan". Se ometti il 2, Excel cerca il testo letterale "Jan*" e restituisce il messaggio di mancato ritrovamento. È l'errore più comune con i caratteri jolly. Rendi i criteri il più specifici possibile, perché XLOOKUP restituisce solo la prima corrispondenza e un modello troppo generico può trovare qualcosa che non intendevi.

Elemento immediatamente inferiore o superiore

Le modalità -1 e 1 servono per le scale di valori: aliquote fiscali, fasce di commissione, soglie di prezzo. Inserisci le soglie in A2:A6 e le aliquote in B2:B6, e cerca un reddito in E2:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

Con -1 Excel restituisce la corrispondenza esatta oppure, se non esiste, la soglia immediatamente inferiore. Lo spazio vuoto prima di -1 salta l'argomento "non trovato". Nell'esempio di Microsoft per la corrispondenza approssimativa si usa 1 per restituire l'aliquota fiscale relativa al valore di reddito immediatamente superiore, quindi scegli la direzione più adatta alla tua tabella. A differenza del VLOOKUP approssimato tradizionale, per queste modalità non devi ordinare le soglie. Le modalità di ricerca binaria sono quelle che richiedono dati ordinati.

Ricerca dal basso: l'ultima corrispondenza

Quando un valore compare più volte, XLOOKUP restituisce la prima occorrenza incontrata. Per ottenere l'ultima, avvia la ricerca dalla fine impostando search_mode su -1:

=XLOOKUP(D2,A2:A100,C2:C100,"Non trovato",0,-1)

Se il foglio elenca gli ordini in ordine cronologico, questa formula restituisce l'ultima voce relativa a un cliente. Lo 0 mantiene la corrispondenza esatta. È lo schema da usare per domande come "ultimo prezzo", "ultimo contatto" e simili, che con VLOOKUP risultavano complicate.

Restituire più colonne contemporaneamente

La pagina di Microsoft ricorda che, a differenza di VLOOKUP, XLOOKUP può restituire più colonne come un unico array. Basta allargare l'intervallo di restituzione:

=XLOOKUP(D2,A2:A100,B2:D100)

La formula inserisce nella cella la corrispondenza della colonna B e riversa i valori delle colonne C e D nelle celle a destra. Lascia spazio per loro. Se le celle a destra contengono già dei dati, Excel mostra un errore di overflow invece di sovrascriverli. L'esempio di Microsoft restituisce con un'unica formula il nome di un dipendente e il suo reparto.

Ricerche a doppia entrata

Microsoft mostra come annidare due funzioni XLOOKUP per trovare un valore all'incrocio tra una riga e una colonna. Lo schema, dalla sua pagina, è:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

La funzione interna sceglie la colonna che corrisponde all'intestazione in C3. La funzione esterna cerca quindi D2 nelle etichette di riga e restituisce il valore di quella colonna. Microsoft lo paragona a una combinazione di INDEX e MATCH. Se hai un report con i prodotti in verticale e i mesi in orizzontale, questa è la sostituzione di una formula più complicata.

Più criteri

XLOOKUP accetta un solo valore di ricerca, e la pagina di Microsoft non contiene esempi con più criteri. Uno schema comune consiste nel far verificare a Excel due condizioni contemporaneamente. Supponiamo che la categoria sia in A, lo stato in B e il prezzo in C. Per trovare il prezzo della categoria in F2 e dello stato in G2:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

Ogni confronto restituisce VERO o FALSO. Moltiplicandoli si ottiene 1 solo nelle righe in cui entrambi sono veri, e XLOOKUP cerca il primo 1. È uno schema che funziona nelle versioni attuali di Microsoft 365, ma non è presente nella pagina delle funzioni di Microsoft, quindi verificalo su un piccolo campione. Un'alternativa più datata è una colonna di supporto che unisce i due campi. Inserisci un separatore tra i valori, ad esempio =A2&"|"&B2, e cerca lo stesso testo unito.

XLOOKUP e VLOOKUP a confronto

La pagina di VLOOKUP di Microsoft riporta la sintassi VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) e specifica che il valore di ricerca deve trovarsi nella prima colonna dell'intervallo. Se ometti range_lookup, VLOOKUP esegue una corrispondenza approssimata, che presuppone che la prima colonna sia ordinata. Impostalo su FALSE per una corrispondenza esatta. La stessa pagina suggerisce di provare XLOOKUP e indica che funziona in qualsiasi direzione e restituisce per impostazione predefinita corrispondenze esatte.

Lo stesso lavoro con entrambe le funzioni, con gli ID nella colonna A e i nomi nella colonna B:

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

Le differenze che contano nell'uso quotidiano:

  • VLOOKUP fa riferimento al risultato tramite il numero di colonna, quindi inserire una colonna nella tabella cambia il significato di quel numero. XLOOKUP fa riferimento direttamente all'intervallo di restituzione.
  • VLOOKUP non può cercare a sinistra. XLOOKUP sì.
  • VLOOKUP usa per impostazione predefinita la corrispondenza approssimata. XLOOKUP usa quella esatta.
  • XLOOKUP ha un argomento per il caso di mancato ritrovamento e la direzione di ricerca integrata.

Non devi riscrivere le cartelle di lavoro esistenti. Se una VLOOKUP funziona, lasciala così com'è. Usa XLOOKUP per le nuove formule e per le ricerche che richiedono una ricerca a sinistra, l'ultima corrispondenza o più colonne di restituzione.

Quali versioni di Excel supportano XLOOKUP

Qui serve prudenza. La pagina di Microsoft elenca XLOOKUP per Excel per Microsoft 365 su Windows e Mac, Excel 2024, Excel 2021 e le app per iPad, iPhone e Android. Una nota della pagina dice che non è disponibile in Excel 2016 o 2019, ma può comparire nelle cartelle di lavoro create in versioni più recenti; la stessa pagina, però, elenca 2016 e 2019 nella riga "si applica a". Le informazioni sono contraddittorie, quindi non considerare Excel 2016 e 2019 sicuri. Anche Excel per il web non è indicato in quella pagina.

Se invii una cartella di lavoro a qualcuno che usa una versione meno recente, la formula potrebbe mostrare un errore dove dovrebbe mostrare un valore. Chiedi quale versione usano i destinatari e, per i file che arrivano a molte persone, prova sulla versione più vecchia in uso. Se non puoi fare una prova, usa una funzione di ricerca classica per quel file.

Errori comuni

  • Dimenticare match_mode impostato su 2 quando usi i caratteri jolly. La ricerca cercherà altrimenti i caratteri letterali.
  • Usare un search_mode binario (2 o -2) su dati non ordinati. Microsoft afferma che i risultati non sono validi.
  • Intervalli di ricerca e di restituzione di dimensioni diverse. Rendili della stessa lunghezza.
  • Spazi nascosti e numeri memorizzati come testo. "100" e 100 sono valori diversi, quindi la ricerca non riesce e compare #N/D. Pulisci i dati con TRIM oppure converti i numeri.
  • Nascondere i problemi con un messaggio di mancato ritrovamento gradevole. Verifica un valore noto prima di fare affidamento sulla formula.
  • Puntare l'array di restituzione su celle in cui il risultato deve estendersi, lasciandole però occupate.
  • Riferimenti a intere colonne su fogli molto grandi. Usa un intervallo delimitato o una tabella di Excel, così Excel elabora meno celle.

Argomenti correlati da approfondire

  • XLOOKUP rispetto a INDEX e MATCH. Entrambe effettuano ricerche a sinistra, e la coppia INDEX e MATCH funziona anche nelle versioni precedenti di Excel. Approfondiscila se condividi file con chi usa Excel 2016 o 2019.
  • Risultati multipli. XLOOKUP restituisce solo la prima corrispondenza. Per ottenere tutte le corrispondenze, usa invece la funzione FILTER.
  • XLOOKUP con le tabelle di Excel. I riferimenti strutturati fanno crescere gli intervalli insieme ai dati e sono facili da leggere nelle formule.
  • Dati da Power Query. Dopo aver caricato i dati in una tabella, XLOOKUP può cercarvi valori come in qualsiasi altra tabella. Aggiorna prima la query e verifica i risultati in seguito.
  • Excel per il web. La pagina di Microsoft su XLOOKUP non lo indica, quindi prima di condividere un file verifica nel tuo ambiente che la funzione funzioni per i tuoi colleghi.

Prossimo passo

Prendi un VLOOKUP dalla cartella di lavoro attuale e riscrivilo come XLOOKUP. Aggiungi un messaggio per il caso di valore non trovato e confronta i due risultati su alcuni ID che conosci. Se coincidono, hai un modello riutilizzabile per ogni nuovo XLOOKUP in Excel.

Fonti

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS