XLOOKUP in Excel: sintassi, esempi e perché batte VLOOKUP
By Mirko Peters · · 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à_corrispondenzaimposta il funzionamento della corrispondenza.0è la corrispondenza esatta ed è il valore predefinito.-1indica una corrispondenza esatta o, in mancanza, l'elemento successivo più piccolo.1indica una corrispondenza esatta o l'elemento successivo più grande.2è una corrispondenza con caratteri jolly, dove*,?e~hanno un significato speciale.modalità_ricercaimposta la direzione della ricerca.1cerca dal primo elemento all'ultimo ed è il valore predefinito.-1cerca dall'ultimo al primo.2e-2sono 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.
- Fai clic sulla cella in cui deve comparire il risultato, per esempio E2.
- Inserisci
=XLOOKUP(D2,A2:A100,B2:B100). - 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_modeimpostato su 2 quando usi i caratteri jolly. La ricerca cercherà altrimenti i caratteri letterali. - Usare un
search_modebinario (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.