Funzione XLOOKUP di Excel: tutto ciò che devi sapere (10 esempi)

Guarda il video - Funzione XLOOKUP di Excel (10 esempi XLOOKUP)

Eccellere Funzione CERCA X è finalmente arrivato.

Se hai utilizzato VLOOKUP o INDEX/MATCH, sono sicuro che adorerai la flessibilità fornita dalla funzione XLOOKUP.

In questo tutorial, tratterò tutto ciò che c'è da sapere sulla funzione XLOOKUP e alcuni esempi che ti aiuteranno a sapere come utilizzarla al meglio.

Quindi iniziamo!

Cos'è XLOOKUP?

XLOOKUP è una nuova funzione di Office 365 ed è una versione nuova e migliorata della funzione VLOOKUP/HLOOKUP.

Fa tutto quello che faceva VLOOKUP e molto altro.

XLOOKUP è una funzione che consente di cercare rapidamente un valore in un set di dati (verticale o orizzontale) e restituire il valore corrispondente in qualche altra riga/colonna.

Ad esempio, se hai i punteggi per gli studenti in un esame, puoi utilizzare XLOOKUP per controllare rapidamente il punteggio di uno studente utilizzando il nome dello studente.

Il potere di questa funzione diventerà ancora più chiaro man mano che mi tufferò in profondità in alcuni XLOOKUP esempi più avanti in questo tutorial.

Ma prima di entrare negli esempi, c'è una grande domanda: come posso accedere a XLOOKUP?

Come accedere a XLOOKUP?

Al momento XLOOKUP è disponibile solo per gli utenti di Office 365.

Quindi, se stai utilizzando versioni precedenti di Excel (2010/2013/2016/2019), non sarai in grado di utilizzare questa funzione.

Inoltre, non sono sicuro che questo venga mai rilasciato per versioni precedenti o meno (forse Microsoft può creare un componente aggiuntivo come hanno fatto per Power Query). Ma a partire da ora, puoi usarlo solo se sei su Office 365.

Fare clic qui per eseguire l'aggiornamento a Office 365

Se sei già su Office 365 (edizione Home, Personal o University) e non hai accesso ad esso, puoi andare alla scheda File e quindi fare clic su Account.

Ci sarebbe un programma Office Insider e puoi fare clic e partecipare al programma Office Insider. Questo ti darà accesso alla funzione XLOOKUP.

Prevedo che XLOOKUP sarà presto disponibile su tutte le versioni di Office 365.

Nota: XLOOKUP è disponibile anche per Office 365 per Mac ed Excel per il Web (Excel online)

Sintassi della funzione XLOOKUP

Di seguito è riportata la sintassi della funzione CERCA.X:

=XLOOKUP(valore_ricerca, matrice_ricerca, matrice_ritorno, [se_non_trovato], [modalità_corrispondenza], [modalità_ricerca])

Se hai utilizzato VLOOKUP, noterai che la sintassi è abbastanza simile, con alcune fantastiche funzionalità aggiuntive ovviamente.

Non preoccuparti se la sintassi e l'argomento sembrano un po' eccessivi. Li copro con alcuni semplici esempi XLOOKUP più avanti in questo tutorial che lo renderanno chiarissimo.

La funzione XLOOKUP può contenere 6 argomenti (3 obbligatori e 3 opzionali):

1. valore di ricerca - il valore che stai cercando
2. lookup_array - l'array in cui stai cercando il valore di ricerca
3. array_ritorno - l'array da cui si vuole prelevare e restituire il valore (corrispondente alla posizione in cui si trova il valore di ricerca)
4. [se_non_trovato] - il valore da restituire nel caso in cui il valore di ricerca non venga trovato. Nel caso in cui non specifichi questo argomento, verrebbe restituito un errore #N/A
5. [match_mode] - Qui puoi specificare il tipo di corrispondenza che desideri:
   • 0 - Corrispondenza esatta, dove lookup_value deve corrispondere esattamente al valore in lookup_array. Questa è l'opzione predefinita.
   • -1 - Cerca la corrispondenza esatta, ma se viene trovata, restituisce l'elemento/valore successivo più piccolo
   • 1 - Cerca la corrispondenza esatta, ma se viene trovata, restituisce l'elemento/valore successivo più grande
   • 2 - Per fare corrispondenze parziali usando i caratteri jolly (* o ~)
6. [modalità_ricerca] - Qui specifichi come la funzione XLOOKUP dovrebbe cercare il lookup_array
   • 1 - Questa è l'opzione predefinita in cui la funzione inizia a cercare il lookup_value dall'alto (primo elemento) al fondo (ultimo elemento) nel lookup_array
   • -1 - Esegue la ricerca dal basso verso l'alto. Utile quando si desidera trovare l'ultimo valore corrispondente in lookup_array
   • 2 - Esegue una ricerca binaria in cui i dati devono essere ordinati in ordine crescente. Se non ordinato, questo può dare errori o risultati errati
   • -2 - Esegue una ricerca binaria in cui i dati devono essere ordinati in ordine decrescente. Se non ordinato, questo può dare errori o risultati errati

XLOOKUP Funzione Esempi

Ora passiamo alla parte interessante: alcuni esempi pratici di XLOOKUP.

Questi esempi ti aiuteranno a capire meglio come funziona XLOOKUP, come è diverso da VLOOKUP e INDEX/MATCH e alcuni miglioramenti e limitazioni di questa funzione.

Fare clic qui per scaricare il file di esempio e seguire

Esempio 1: recuperare un valore di ricerca

Supponiamo di avere il seguente set di dati e di voler recuperare il punteggio matematico per Greg (il valore di ricerca).

Di seguito è la formula che fa questo:

=XCERCA(F2,A2:A15,B2:B15)

Nella formula sopra, ho appena usato gli argomenti obbligatori in cui cerca il nome (dall'alto verso il basso), trova una corrispondenza esatta e restituisce il valore corrispondente da B2: B15.

Un'ovvia differenza tra le funzioni CERCA.X e CERCA.VERT è il modo in cui gestiscono l'array di ricerca. In CERCA.VERT, hai l'intero array in cui il valore di ricerca si trova nella colonna più a sinistra e quindi specifichi il numero di colonna da cui vuoi recuperare il risultato. XLOOKUP, d'altra parte, ti permette di scegliere lookup_array e return_array separatamente

Un vantaggio immediato di avere lookup_array e return_array come argomenti separati significa che ora puoi guarda a sinistra. CERCA.VERT aveva questa limitazione in cui puoi solo cercare e trovare un valore che si trova a destra. Ma con XLOOKUP, quella limitazione è sparita.

Ecco un esempio. Ho lo stesso set di dati, dove il nome è a destra e return_range è a sinistra.

Di seguito è riportata la formula che posso utilizzare per ottenere il punteggio per Greg in Math (che significa guardare a sinistra di lookup_value)

=XCERCA(F2,D2:D15,A2:A15)

XLOOKUP risolve un altro grosso problema: nel caso in cui si inserisca una nuova colonna o si spostino colonne, i dati risultanti sarebbero comunque corretti. VLOOKUP probabilmente si interromperebbe o darebbe un risultato errato in questi casi poiché la maggior parte delle volte il valore dell'indice di colonna è hardcoded.

Esempio 2: ricerca e recupero di un intero record

Prendiamo gli stessi dati come esempio.

In questo caso, non voglio solo recuperare il punteggio di Greg in Math. Voglio ottenere i punteggi in tutte le materie.

In questo caso, posso usare la seguente formula:

=XCERCA(F2,A2:A15,B2:D15)

La formula precedente utilizza un intervallo array_ritorno che è più di una colonna (B2: D15). Quindi, quando il valore di ricerca viene trovato in A2:A15, la formula restituisce l'intera riga da return_array.

Inoltre, non è possibile eliminare solo le celle che fanno parte dell'array che sono state popolate automaticamente. In questo esempio, non è possibile eliminare H2 o I2. Se ci provassi, non succederebbe nulla. Se selezioni queste celle, la formula nella barra della formula sarà disattivata (a indicare che non può essere modificata)

Puoi eliminare la formula nella cella G2 (dove l'abbiamo originariamente inserita), eliminerà l'intero risultato.

Questo è un utile miglioramento come in precedenza con CERCA.VERT, dovrai specificare il numero di colonna separatamente per ogni formula.

Esempio 3: ricerca bidirezionale utilizzando XLOOKUP (ricerca orizzontale e verticale)

Di seguito è riportato un set di dati in cui voglio conoscere il punteggio di Greg in Math (l'oggetto nella cella G2).

Questo può essere fatto usando una ricerca bidirezionale in cui cerco il nome nella colonna A e il nome del soggetto nella riga 1. Il vantaggio di questa ricerca bidirezionale è che il risultato è indipendente dal nome dello studente del nome del soggetto. Se cambio il nome del soggetto in Chimica, questa formula XLOOKUP bidirezionale funzionerebbe comunque e mi darebbe il risultato corretto.

Di seguito è riportata la formula che eseguirà la ricerca bidirezionale e darà il risultato corretto:

=CERCAX(G1,B1:D1,CERCAX(F2,A2:A15,B2:D15))

Questa formula utilizza un XLOOKUP annidato, dove prima lo uso per recuperare tutti i voti dello studente nella cella F2.

Quindi il risultato di XLOOKUP(F2,A2:A15,B2:D15) è {21,94,81}, che in questo caso è una serie di voti ottenuti da Greg.

Questo viene quindi utilizzato di nuovo all'interno della formula XLOOKUP esterna come matrice di ritorno. Nella formula XLOOKUP esterna, cerco il nome del soggetto (che si trova nella cella G1) e l'array di ricerca è B1: D1.

Se il nome del soggetto è Math, questa formula XLOOKUP esterna recupera il primo valore dall'array restituito, che in questo esempio è {21,94,81}.

Questo fa lo stesso che è stato, fino ad ora, ottenuto usando la combinazione INDICE e CONFRONTA

Fare clic qui per scaricare il file di esempio e seguire

Esempio 4: quando il valore di ricerca non viene trovato (gestione degli errori)

La gestione degli errori è stata ora aggiunta alla formula CERCA.X.

Il quarto argomento nella funzione XLOOKUP è [if_not_found], dove è possibile specificare ciò che si desidera nel caso in cui la ricerca non venga trovata.

Supponiamo di avere il set di dati come mostrato di seguito in cui si desidera ottenere il punteggio matematico nel caso in cui la corrispondenza e nel caso in cui il nome non venga trovato, si desideri restituire - "Non è apparso"

La formula seguente farà questo:

=XLOOKUP(F2,A2:A15,B2:B15,"Non è apparso")

In questo caso, ho codificato ciò che voglio ottenere nel caso non ci fosse corrispondenza. Puoi anche utilizzare un riferimento di cella a una cella oa una formula.

Esempio 5: XLOOKUP nidificato (ricerca in più intervalli)

Il genio di avere l'argomento [if_not_found] è che ti permette di usare formula XLOOKUP annidata.

Ad esempio, supponiamo di avere due elenchi separati come mostrato di seguito. Mentre ho queste due tabelle sullo stesso foglio, puoi averle in fogli separati o persino cartelle di lavoro.

Di seguito è riportata la formula XLOOKUP nidificata che verificherà il nome in entrambe le tabelle e restituirà il valore corrispondente dalla colonna specificata.

=XCERCA(A12,A2:A8,B2:B8,XCERCA(A12,F2:F8,G2:G8))

Nella formula sopra, ho usato l'argomento [if_not_found] per usare un'altra formula XLOOKUP. Ciò consente di aggiungere il secondo XLOOKUP nella stessa formula ed eseguire la scansione di due tabelle con un'unica formula.

Non sono sicuro di quanti XLOOKUP nidificati puoi usare in una formula. Ho provato fino alle 10 e ha funzionato, poi ho rinunciato 🙂

Esempio 6: trova l'ultimo valore di corrispondenza

Questo era assolutamente necessario e XLOOKUP lo ha reso possibile. Ora non è necessario trovare modi complicati per ottenere l'ultimo valore corrispondente in un intervallo.

Supponiamo di avere il set di dati come mostrato di seguito e di voler controllare quando è stata assunta l'ultima persona in ciascun reparto e qual è stata la data di assunzione.

La formula seguente cercherà l'ultimo valore per ogni dipartimento e darà il nome dell'ultima assunzione:

=XCERCA(F1,$B$2:$B$15,$A$2:$A$15,,,-1)

E la formula seguente darà la data di assunzione dell'ultima assunzione per ciascun dipartimento:

=XCERCA(F1,$B$2:$B$15,$C$2:$C$15,,,-1)

Poiché XLOOKUP ha una funzione integrata per specificare la direzione della ricerca (dal primo all'ultimo o dall'ultimo al primo), questo viene fatto con una semplice formula. Con i dati verticali, VLOOKUP e INDEX/MATCH guardano sempre dall'alto verso il basso, ma con XLOOKUP e possono anche specificare la direzione dal basso verso l'alto.

Esempio 7: corrispondenza approssimativa con CERCA.X (Trova aliquota fiscale)

Un altro notevole miglioramento con XLOOKUP è che ora ci sono quattro modalità di corrispondenza (VLOOKUP ha 2 e MATCH ha 3).

Puoi specificare uno qualsiasi dei quattro argomenti per decidere come deve essere abbinato il valore di ricerca:

• 0 - Corrispondenza esatta, dove lookup_value deve corrispondere esattamente al valore in lookup_array. Questa è l'opzione predefinita.
• -1 - Cerca la corrispondenza esatta, ma se viene trovata, restituisce l'elemento/valore successivo più piccolo
• 1 - Cerca la corrispondenza esatta, ma se viene trovata, restituisce l'elemento/valore successivo più grande
• 2 - Per fare corrispondenze parziali usando i caratteri jolly (* o ~)

Ma la parte migliore è che non devi preoccuparti se i tuoi dati sono ordinati in ordine crescente o decrescente. Anche se i dati non sono ordinati, XLOOKUP se ne occuperà.

Di seguito ho un set di dati in cui voglio trovare la commissione di ogni persona - e la commissione deve essere calcolata utilizzando la tabella a destra.

Di seguito è la formula che farà questo:

=XCERCA(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

Questo utilizza semplicemente il valore delle vendite come ricerca e guarda attraverso la tabella di ricerca a destra. In questa formula, ho usato -1 come quinto argomento ([match_mode]), il che significa che cercherà una corrispondenza esatta e, quando non ne troverà una, restituirà il valore appena più piccolo del valore di ricerca .

E come ho detto, non devi preoccuparti se i tuoi dati non sono ordinati.

Fare clic qui per scaricare il file di esempio e seguire

Esempio 8: ricerca orizzontale

XLOOKUP può eseguire una ricerca verticale e orizzontale.

Di seguito ho un set di dati in cui ho i nomi degli studenti e i loro punteggi in righe e voglio recuperare il punteggio per il nome nella cella B7.

La formula seguente farà questo:

=XCERCA(B7,B1:O1,B2:O2)

Questa non è altro che una semplice ricerca (simile a quella che abbiamo visto nell'Esempio 1), ma orizzontale.

Tutti gli esempi che tratterò sulla ricerca verticale possono essere eseguiti anche con una ricerca orizzontale utilizzando XLOOKUP (addio a VLOOKUP e HLOOKUP).

Esempio 9: ricerca condizionale (usando XLOOKUP con altre formule)

Questo è un esempio leggermente avanzato e mostra anche la potenza di XLOOKUP quando è necessario eseguire ricerche complesse.

Di seguito è riportato un set di dati in cui ho i nomi degli studenti e i loro punteggi, e voglio conoscere il nome dello studente che ha ottenuto il punteggio massimo in ogni materia e il conteggio degli studenti che ha ottenuto più di 80 in ciascuna materia.

Di seguito la formula che darà il nome dello studente con il punteggio più alto in ciascuna materia:

=CERCAX(MAX(CERCAX(G1,$B$1:$D$1,$B$2:$D$15)),CERCAX(G1,$B$1:$D$1,$B$2:$D$15),$A $2:$A$15)

Poiché XLOOKUP può essere utilizzato per restituire un intero array, l'ho usato per ottenere prima tutti i segni per l'oggetto richiesto.

Ad esempio, per Math, quando uso XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15), mi dà tutti i punteggi in matematica. Posso quindi utilizzare la funzione MAX per trovare il punteggio massimo in questo intervallo.

Questo punteggio massimo diventa quindi il mio valore di ricerca e l'intervallo di ricerca sarebbe l'array restituito da XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)

Lo uso all'interno di un'altra formula XLOOKUP per recuperare il nome dello studente che ha ottenuto il punteggio massimo.

E per contare il numero di studenti che hanno totalizzato più di 80, usa la formula seguente:

=CONTA.SE(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15);">80")

Questo usa semplicemente la formula XLOOKUP per ottenere un intervallo di tutti i valori per il dato soggetto. Quindi lo avvolge nella funzione COUNTIF per ottenere il numero di punteggi superiori a 80.

Esempio 10: utilizzo di caratteri jolly in XLOOKUP

Proprio come puoi usare i caratteri jolly in CERCA.VERT e CONFRONTA, puoi farlo anche con CERCA.X.

Ma c'è una differenza.

In XLOOKUP, devi specificare che stai utilizzando caratteri jolly (nel quinto argomento). Se non lo specifichi, XLOOKUP ti darà un errore.

Di seguito è riportato un set di dati in cui ho nomi di società e la loro capitalizzazione di mercato.

Voglio cercare il nome di una società nella colonna D e recuperare la capitalizzazione di mercato dalla tabella a sinistra. E poiché i nomi nella colonna D non sono corrispondenze esatte, dovrò utilizzare caratteri jolly.

Di seguito è la formula che farà questo:

=XCERCA("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,2)

Nella formula sopra, ho usato il carattere jolly asterisco (*) prima come dopo D2 (deve essere tra virgolette e unito a D2 usando la e commerciale).

Questo dice alla formula di esaminare tutte le celle e, se contiene la parola nella cella D2 (che è Apple), considerala una corrispondenza esatta. Non importa quanti e quali caratteri ci sono prima e dopo il testo nella cella D2.

E per assicurarsi che XLOOKUP accetti i caratteri jolly, il quinto argomento è stato impostato su 2 (corrispondenza caratteri jolly).

Esempio 11: trova l'ultimo valore nella colonna

Poiché XLOOKUP ti consente di cercare dal basso verso l'alto, puoi facilmente trovare l'ultimo valore in un elenco, nonché recuperare il valore corrispondente da una colonna.

Supponiamo di avere un set di dati come mostrato di seguito e di voler sapere qual è l'ultima azienda e qual è la capitalizzazione di mercato di quest'ultima azienda.

La seguente formula ti darà il nome dell'ultima azienda:

=XCERCA("*",A2:A11,A2:A11,,2,-1)

E la formula seguente fornirà la capitalizzazione di mercato dell'ultima società nell'elenco:

=XCERCA("*",A2:A11,B2:B11,,2,-1)

Queste formule utilizzano ancora i caratteri jolly. In questi, ho usato l'asterisco (*) come valore di ricerca, il che significa che questo considererebbe la prima cella che incontra come una corrispondenza esatta (poiché l'asterisco potrebbe essere qualsiasi carattere e qualsiasi numero di caratteri).

E poiché la direzione è dal basso verso l'alto (per i dati disposti verticalmente), restituirà l'ultimo valore nell'elenco.

E la seconda formula poiché utilizza un intervallo_ritorno separato per ottenere la capitalizzazione di mercato del cognome nell'elenco.

Fare clic qui per scaricare il file di esempio e seguire

Cosa succede se non hai XLOOKUP?

Poiché XLOOKUP sarà probabilmente disponibile solo per gli utenti di Office 365, un modo per ottenerlo è eseguire l'aggiornamento a Office 365.

Se hai già Office 365 Home, Personal o University edition, hai già accesso a XLOOKUP. Tutto quello che devi fare è partecipare al programma Office Insider.

Per fare ciò, vai alla scheda File, fai clic su Account e quindi fai clic sull'opzione Insider di Office. Ci sarebbe un'opzione per partecipare al programma insider.

Nel caso in cui disponi di altri abbonamenti a Office 365 (come Enterprise), sono sicuro che XLOOKUP e altre fantastiche funzionalità (come array dinamici, formule come SORT e FILTER) saranno presto disponibili.

Nel caso in cui utilizzi Excel 2010/2013/2016/2019, non avrai XLOOKUP e dovrai continuare a utilizzare la combinazione VLOOKUP, HLOOKUP e INDICE/MATCH per ottenere il meglio dalle formule di ricerca.

XLOOKUP Compatibilità con le versioni precedenti

Questa è una cosa a cui devi stare attento: XLOOKUP è NON compatibile con le versioni precedenti.

Ciò significa che se crei un file e utilizzi la formula XLOOKUP, quindi lo apri in una versione che non ha XLOOKUP, mostrerà errori.

Poiché XLOOKUP è un enorme passo avanti nella giusta direzione, credo che questa diventerà la formula di ricerca predefinita, ma ci vorranno sicuramente alcuni anni prima di essere ampiamente adottata. Dopotutto, vedo ancora alcune persone che usano Excel 2003.

Quindi questi sono 11 XLOOKUP Esempi che possono aiutarti a fare tutte le ricerche e le cose di riferimento fatte più velocemente e anche a semplificare l'uso.

Spero che tu abbia trovato utile questo tutorial!