Nella scelta tra XLOOKUP e VLOOKUP, usa XLOOKUP se tutti quelli che aprono il file hanno Excel per Microsoft 365, Excel 2021 o successivo, oppure Excel per il web. Fa tutto ciò che fa VLOOKUP con meno errori: corrispondenza esatta per impostazione predefinita, può cercare verso sinistra e non si rompe quando inserisci una colonna. Usa VLOOKUP solo quando la cartella di lavoro deve funzionare in Excel 2019 o precedenti.
Questo limite di compatibilità è l'avvertenza principale. XLOOKUP non esiste in Excel 2016 e 2019, quindi un file pieno di formule XLOOKUP mostra errori #NOME? a chiunque usi ancora quelle versioni.
Come si chiamano in Excel in italiano. Excel localizza i nomi delle funzioni e usa il punto e virgola (;) come separatore degli argomenti. In questo articolo ogni formula è scritta nella forma di Excel in italiano. Se usi Excel in inglese, sostituisci i nomi con quelli inglesi e i ; con le virgole.
| Inglese | Italiano |
|---|---|
XLOOKUP | CERCA.X |
VLOOKUP | CERCA.VERT |
HLOOKUP | CERCA.ORIZZ |
XMATCH | CONFRONTA.X |
INDEX / MATCH | INDICE / CONFRONTA |
IFNA / IFERROR | SE.NON.DISP / SE.ERRORE |
FILTER / UNIQUE | FILTRO / UNICI |
TRIM | ANNULLA.SPAZI |
TRUE / FALSE | VERO / FALSO |
Anche i codici di errore sono localizzati: #N/D (#N/A in inglese), #NOME? (#NAME?), #RIF! (#REF!), #VALORE! (#VALUE!) e #RIVERSAMENTO! (#SPILL!).
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Cercare a sinistra (restituire una colonna a sinistra di quella di ricerca) | No | Sì |
| Tipo di corrispondenza predefinito | Approssimativa (a meno di aggiungere FALSO) | Esatta |
| Regge l'inserimento o l'eliminazione di colonne | No, il numero di colonna resta fisso | Sì, gli intervalli si adattano |
| Messaggio personalizzato "non trovato" | Servono SE.NON.DISP o SE.ERRORE | Integrato (se_non_trovato, in inglese if_not_found) |
| Restituire più colonne insieme | Non facilmente | Sì, i risultati si riversano |
| Cercare dall'ultima corrispondenza verso l'alto | No | Sì (modalità_ricerca -1, in inglese search_mode) |
| Corrispondenza approssimativa | I dati devono essere ordinati in senso crescente | Elemento successivo minore o maggiore, senza ordinare |
Caratteri jolly (*, ?) | Sì, con corrispondenza esatta | Sì, con modalità_confronto 2 (in inglese match_mode) |
| Ricerche orizzontali | No, serve CERCA.ORIZZ (HLOOKUP) | Sì, stessa funzione |
| Velocità | Nessuna differenza pratica nelle cartelle di lavoro tipiche | Uguale, più la ricerca binaria facoltativa sui dati ordinati |
| Disponibile in | Ogni versione di Excel | Microsoft 365, Excel 2021, Excel 2024, Excel per il web, app per dispositivi mobili |
La sintassi a confronto
=CERCA.VERT(valore; matrice_tabella; indice; [intervallo])
=CERCA.X(valore_ricerca; matrice_ricerca; matrice_restituita; [se_non_trovato]; [modalità_confronto]; [modalità_ricerca])
In Excel in inglese:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
La grande differenza sta nel modo in cui indichi a Excel dove si trova la risposta. VLOOKUP prende un'intera tabella e un numero di colonna contato dal bordo sinistro della tabella. XLOOKUP prende due intervalli separati: la colonna in cui cercare e la colonna da cui restituire il risultato.
La stessa ricerca scritta nei due modi
Ecco un piccolo elenco prodotti in A1:D6, con un ID prodotto digitato in G2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID prodotto | Prodotto | Categoria | Prezzo |
| 2 | P-101 | Mouse wireless | Accessori | 24,99 $ |
| 3 | P-102 | Hub USB-C | Accessori | 39,50 $ |
| 4 | P-103 | Supporto per laptop | Ufficio | 45,00 $ |
| 5 | P-104 | Webcam HD | Video | 59,00 $ |
| 6 | P-105 | Lampada da scrivania | Ufficio | 32,75 $ |
Per trovare il prezzo di P-104:
=CERCA.VERT(G2; A2:D6; 4; FALSO)
=CERCA.X(G2; A2:A6; D2:D6)
(In Excel in inglese: =VLOOKUP(G2, A2:D6, 4, FALSE) e =XLOOKUP(G2, A2:A6, D2:D6).)
Entrambe restituiscono 59,00 $. CERCA.VERT cerca nella prima colonna di A2:D6 e restituisce la quarta colonna; FALSO impone la corrispondenza esatta. CERCA.X cerca in A2:A6 e restituisce la cella corrispondente di D2:D6, con la corrispondenza esatta come impostazione predefinita.
Se copierai la formula lungo una colonna, blocca la tabella con riferimenti assoluti, per esempio =CERCA.X(G2; $A$2:$A$6; $D$2:$D$6). Premi F4 su Windows o Cmd+T su Mac mentre il cursore è in un riferimento per aggiungere i segni $.
I due classici punti deboli di VLOOKUP
1. Dimenticare FALSO (o 0)
L'ultimo argomento di CERCA.VERT è facoltativo e, se lo ometti, Excel usa la corrispondenza approssimativa. Con la corrispondenza approssimativa, CERCA.VERT presume che la prima colonna sia ordinata e restituisce il valore più vicino che sia minore o uguale a quello cercato, senza alcun avviso.
Prova =CERCA.VERT("P-110"; A2:D6; 4) sulla tabella qui sopra. P-110 non esiste, ma invece di #N/D ottieni 32,75 $, il prezzo di P-105, perché "P-105" è il valore minore più vicino. Su dati non ordinati i risultati possono essere ancora più casuali.
Termina sempre una CERCA.VERT a corrispondenza esatta con FALSO oppure 0. XLOOKUP evita il problema perché usa la corrispondenza esatta a meno che tu non chieda altro.
2. Il numero di colonna scritto a mano
Il 4 nella formula CERCA.VERT è solo un numero. Se qualcuno inserisce una colonna Fornitore tra Prodotto e Categoria, la tabella diventa A:E, Prezzo si sposta nella colonna E e la formula continua a restituire la quarta colonna, che ora è Categoria. Vedresti "Video" invece di 59,00 $.
CERCA.X punta alla colonna del prezzo stessa (D2:D6), quindi Excel la aggiorna automaticamente in E2:E6 e la risposta resta corretta.
Cosa sa fare XLOOKUP e VLOOKUP no
Cercare a sinistra
CERCA.VERT può restituire solo colonne a destra di quella di ricerca. A CERCA.X la direzione non importa. Per trovare l'ID prodotto di un nome di prodotto in G2:
=CERCA.X(G2; B2:B6; A2:A6)
Con "Lampada da scrivania" in G2, restituisce P-105, anche se la colonna A è a sinistra della colonna B.
Mostrare un messaggio chiaro quando non si trova nulla
=CERCA.X(G2; A2:A6; D2:D6; "Non trovato")
L'equivalente con CERCA.VERT richiede un involucro: =SE.NON.DISP(CERCA.VERT(G2; A2:D6; 4; FALSO); "Non trovato").
Restituire più colonne con una sola formula
=CERCA.X(G2; A2:A6; B2:D6)
Per P-104 riversa Webcam HD, Video e 59,00 $ in tre celle affiancate. Lascia vuote quelle celle, altrimenti ottieni un errore #RIVERSAMENTO!.
Trovare l'ultima corrispondenza invece della prima
In un registro in cui lo stesso cliente compare molte volte, imposta modalità_ricerca su -1 per cercare dal basso verso l'alto e ottenere la voce più recente:
=CERCA.X(G2; A2:A500; C2:C500; "Non trovato"; 0; -1)
Corrispondenza approssimativa senza ordinare
Per prezzi a scaglioni o aliquote di commissione, la corrispondenza approssimativa di CERCA.VERT funziona solo se la prima colonna è ordinata in senso crescente. Le modalità_confronto -1 (corrispondenza esatta o elemento successivo minore) e 1 (corrispondenza esatta o elemento successivo maggiore) di CERCA.X non richiedono dati ordinati. Con gli importi minimi d'ordine in J2:J5 e gli sconti in K2:K5:
=CERCA.VERT(B2; J2:K5; 2; VERO)
=CERCA.X(B2; J2:J5; K2:K5; ; -1)
Caratteri jolly
Entrambe supportano * e ?, ma CERCA.X richiede modalità_confronto 2 per trattarli come caratteri jolly:
=CERCA.VERT("Lampada*"; B2:D6; 3; FALSO)
=CERCA.X("Lampada*"; B2:B6; D2:D6; ; 2)
Entrambe restituiscono 32,75 $ per la lampada da scrivania.
INDICE/CONFRONTA: l'alternativa classica
Prima di XLOOKUP, la soluzione standard ai limiti di VLOOKUP era INDICE con CONFRONTA (in inglese INDEX con MATCH). Cerca a sinistra, regge le colonne inserite e funziona in ogni versione di Excel, il che la rende la scelta migliore per i file che devono aprirsi in Excel 2019 o precedenti:
=INDICE(D2:D6; CONFRONTA(G2; A2:A6; 0))
=INDICE(A2:A6; CONFRONTA(G2; B2:B6; 0))
La prima restituisce il prezzo per l'ID in G2; la seconda è l'esempio di ricerca a sinistra. Lo 0 in CONFRONTA indica la corrispondenza esatta e, come il FALSO di CERCA.VERT, è facile dimenticarlo. XLOOKUP è semplicemente la versione più breve e più sicura della stessa idea.
Come convertire le formule VLOOKUP in XLOOKUP
Non esiste un convertitore integrato, ma la traduzione è meccanica:
- Trova le tue CERCA.VERT: premi
Ctrl+F(Cmd+Fsu Mac), digita CERCA.VERT (VLOOKUP in Excel in inglese), fai clic su Opzioni se le impostazioni aggiuntive sono nascoste, imposta Cerca in su Formule e fai clic su Trova tutti. - Mantieni il primo argomento (il valore da cercare) così com'è.
- Sostituisci la tabella con la sua prima colonna come
matrice_ricerca. - Sostituisci il numero di colonna con la colonna corrispondente come
matrice_restituita. La colonna 5 di una tabella che inizia nella colonna A è la colonna E. - Elimina
FALSO. Se l'originale terminava conVEROo non aveva l'ultimo argomento, aggiungimodalità_confronto-1 per mantenere il comportamento "elemento successivo minore".
Per esempio:
=CERCA.VERT(A2; Prodotti!$A$2:$F$500; 5; FALSO)
=CERCA.X(A2; Prodotti!$A$2:$A$500; Prodotti!$E$2:$E$500)
Controlla alcuni risultati rispetto alla vecchia formula prima di convertire un intero foglio. Le CERCA.VERT a corrispondenza approssimativa su dati non ordinati potrebbero aver restituito valori sbagliati fin dall'inizio, quindi le risposte di CERCA.X possono legittimamente essere diverse.
Cosa succede quando un file con XLOOKUP si apre in Excel 2016 o 2019
La cartella di lavoro si apre, ma quelle versioni non conoscono la funzione. Nella barra della formula compare come =_xlfn.XLOOKUP(...). Le celle possono inizialmente mostrare ancora gli ultimi risultati salvati, ma non appena la formula viene ricalcolata restituisce #NOME?.
Se condividi i file con persone che usano versioni meno recenti:
- Usa invece INDICE/CONFRONTA o CERCA.VERT, oppure
- Invia una copia con i risultati incollati come valori (Copia → Incolla speciale → Valori).
Lo stesso vale per altre funzioni più recenti come FILTRO, UNICI e CONFRONTA.X.
XLOOKUP in Google Sheets
Anche Google Sheets ha XLOOKUP, con lo stesso ordine degli argomenti: =XLOOKUP(chiave_ricerca, intervallo_ricerca, intervallo_risultato, [valore_mancante], [modalità_confronto], [modalità_ricerca]). Le formule di questo articolo funzionano lì con la stessa logica, quindi XLOOKUP è una scelta sicura per i file che passano tra Excel e Sheets. Con l'interfaccia di Sheets in italiano, usa il nome della funzione localizzato che ti viene suggerito mentre digiti.
Perché la ricerca restituisce #N/D o un valore sbagliato
- Spazi in più. "P-104 " con uno spazio finale non corrisponde a "P-104". Pulisci i dati con
ANNULLA.SPAZI. - Numeri memorizzati come testo. Un ID digitato come testo non corrisponde allo stesso ID memorizzato come numero. Cerca il triangolino verde nell'angolo della cella e converti la colonna a un solo tipo.
- FALSO mancante in CERCA.VERT. Ottieni una risposta plausibile ma sbagliata invece di
#N/D. - Intervalli che si sono spostati quando hai copiato la formula. Usa riferimenti assoluti con
$per la tabella di ricerca. #RIF!da CERCA.VERT. Il numero di colonna è maggiore del numero di colonne della tabella.#VALORE!da CERCA.X. L'intervallo di ricerca e quello restituito hanno dimensioni diverse, per esempio A2:A6 e D2:D60.#NOME?Il nome della funzione è scritto in modo errato, oppure il file è aperto in una versione senza XLOOKUP.
Domande frequenti
XLOOKUP è meglio di VLOOKUP? Sì, per quasi ogni uso. Ha la corrispondenza esatta come impostazione predefinita, cerca in qualsiasi direzione, non si rompe quando le colonne si spostano e gestisce i valori mancanti senza involucri. L'unico vero vantaggio di VLOOKUP è che funziona in ogni versione di Excel.
XLOOKUP è più veloce di VLOOKUP? Nelle cartelle di lavoro di tutti i giorni non noterai differenze. Su elenchi ordinati molto grandi, le modalità di ricerca binaria di XLOOKUP (modalità_ricerca 2 o -2) possono accelerare le cose, ma restituiscono risultati sbagliati se i dati non sono ordinati.
Perché XLOOKUP mostra #NOME? sul mio computer? La tua versione di Excel non include XLOOKUP. È presente in Microsoft 365, in Excel 2021 e successivi e in Excel per il web, ma non in Excel 2019 o 2016. Usa invece INDICE/CONFRONTA.
XLOOKUP sostituisce anche HLOOKUP? Sì. Se gli intervalli di ricerca e di risultato sono righe invece di colonne, XLOOKUP cerca in orizzontale con la stessa sintassi.
XLOOKUP può restituire tutte le corrispondenze, non solo la prima? No, restituisce una corrispondenza (o una riga di colonne). Per ottenere tutte le righe corrispondenti, usa FILTRO, per esempio =FILTRO(A2:D6; C2:C6="Ufficio").
Devo ancora imparare VLOOKUP? Vale la pena saperlo riconoscere, perché lo troverai in cartelle di lavoro e modelli meno recenti. Per le nuove formule, impara XLOOKUP e tieni presente INDICE/CONFRONTA per i file che devono aprirsi in versioni di Excel meno recenti.
Lascia che sia l'IA a scrivere la ricerca
Le formule di ricerca sono il punto in cui i piccoli dettagli, come un FALSO dimenticato o un intervallo spostato, causano più errori silenziosi. CoreGPT for Excel ti permette di descrivere ciò che ti serve, per esempio "trova il prezzo per l'ID prodotto in G2" oppure "segna ogni riga come sopra o sotto l'obiettivo", e scrive la formula nel tuo foglio con una breve spiegazione. Si può iniziare gratis e funziona in Excel per Windows, Mac e web.
CoreGPTUsa questa formula e trascinala verso il basso:
=IF(C2>=D2,"Sopra","Sotto")Provalo: CoreGPT for Excel
Da leggere anche:
