Come utilizzare la funzione XLOOKUP in Microsoft Excel

Il nuovo XLOOKUP di Excel sostituirà VLOOKUP, fornendo un potente sostituto di una delle funzioni più popolari di Excel. Questa nuova funzione risolve alcuni dei limiti di VLOOKUP e ha funzionalità extra. Ecco cosa devi sapere.

Cos'è XLOOKUP?

La nuova funzione XLOOKUP offre soluzioni per alcune delle maggiori limitazioni di VLOOKUP. Inoltre, sostituisce anche HLOOKUP. Ad esempio, XLOOKUP può guardare alla sua sinistra, impostare una corrispondenza esatta per impostazione predefinita e consente di specificare un intervallo di celle anziché un numero di colonna. VLOOKUP non è così facile da usare o versatile. Ti mostreremo come funziona tutto.

Per il momento, XLOOKUP è disponibile solo per gli utenti del programma Insiders. Chiunque può partecipare al programma Insider per accedere alle più recenti funzionalità di Excel non appena diventano disponibili. Microsoft inizierà presto a distribuirlo a tutti gli utenti di Office 365.

Come utilizzare la funzione XLOOKUP

Immergiamoci subito con un esempio di XLOOKUP in azione. Prendi i dati di esempio di seguito. Vogliamo restituire il dipartimento dalla colonna F per ogni ID nella colonna A.

Questo è un classico esempio di ricerca di corrispondenza esatta. La funzione XLOOKUP richiede solo tre informazioni.

L'immagine sotto mostra XLOOKUP con sei argomenti, ma solo i primi tre sono necessari per una corrispondenza esatta. Quindi concentriamoci su di loro:

• Valore_ricerca: quello che stai cercando.
• Lookup_array: dove cercare.
• Return_array: l'intervallo contenente il valore da restituire.

La seguente formula funzionerà per questo esempio: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Esaminiamo ora un paio di vantaggi che XLOOKUP ha rispetto a VLOOKUP qui.

Niente più numero di indice della colonna

Il famigerato terzo argomento di CERCA.VERT era specificare il numero di colonna delle informazioni da restituire da un array di tabelle. Questo non è più un problema perché XLOOKUP ti consente di selezionare l'intervallo da cui tornare (colonna F in questo esempio).

E non dimenticare, XLOOKUP può visualizzare i dati rimasti della cella selezionata, a differenza di VLOOKUP. Maggiori informazioni su questo di seguito.

Inoltre, non hai più il problema di una formula non funzionante quando vengono inserite nuove colonne. Se ciò accadesse nel tuo foglio di lavoro, l'intervallo di ritorno si adatterebbe automaticamente.

La corrispondenza esatta è l'impostazione predefinita

Era sempre fonte di confusione quando si imparava VLOOKUP perché si doveva specificare una corrispondenza esatta.

Fortunatamente, XLOOKUP imposta automaticamente una corrispondenza esatta, il motivo molto più comune per utilizzare una formula di ricerca). Ciò riduce la necessità di rispondere al quinto argomento e garantisce meno errori da parte di utenti nuovi alla formula.

Quindi, in breve, XLOOKUP pone meno domande di VLOOKUP, è più intuitivo ed è anche più duraturo.

XLOOKUP può guardare a sinistra

La possibilità di selezionare un intervallo di ricerca rende XLOOKUP più versatile di VLOOKUP. Con XLOOKUP, l'ordine delle colonne della tabella non ha importanza.

CERCA.VERT è stato vincolato cercando nella colonna più a sinistra di una tabella e quindi tornando da un numero specificato di colonne a destra.

Nell'esempio seguente, dobbiamo cercare un ID (colonna E) e restituire il nome della persona (colonna D).

La seguente formula può raggiungere questo obiettivo: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Cosa fare se non viene trovato

Gli utenti delle funzioni di ricerca hanno molta familiarità con il messaggio di errore #N/D che li saluta quando la loro funzione VLOOKUP o MATCH non riesce a trovare ciò di cui ha bisogno. E spesso c'è una ragione logica per questo.

Pertanto, gli utenti cercano rapidamente come nascondere questo errore perché non è corretto o utile. E, naturalmente, ci sono modi per farlo.

XLOOKUP viene fornito con il proprio argomento "se non trovato" integrato per gestire tali errori. Vediamolo in azione con l'esempio precedente, ma con un ID digitato in modo errato.

La seguente formula visualizzerà il testo "ID errato" invece del messaggio di errore: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Utilizzo di XLOOKUP per una ricerca di intervallo

Sebbene non sia comune come la corrispondenza esatta, un uso molto efficace di una formula di ricerca consiste nel cercare un valore negli intervalli. Prendi il seguente esempio. Vogliamo restituire lo sconto in base all'importo speso.

Questa volta non stiamo cercando un valore specifico. Abbiamo bisogno di sapere dove i valori nella colonna B rientrano negli intervalli nella colonna E. Ciò determinerà lo sconto guadagnato.

XLOOKUP ha un quinto argomento opzionale (ricorda, per impostazione predefinita la corrispondenza esatta) denominata modalità di corrispondenza.

Puoi vedere che XLOOKUP ha capacità maggiori con corrispondenze approssimative rispetto a VLOOKUP.

C'è la possibilità di trovare la corrispondenza più vicina minore di (-1) o maggiore di (1) il valore cercato. C'è anche un'opzione per usare caratteri jolly (2) come ? o il *. Questa impostazione non è attiva per impostazione predefinita come lo era con CERCA.VERT.

La formula in questo esempio restituisce il minore più vicino al valore cercato se non viene trovata una corrispondenza esatta: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Tuttavia, è presente un errore nella cella C7 in cui viene restituito l'errore #N/D (l'argomento "se non trovato" non è stato utilizzato). Questo avrebbe dovuto restituire uno sconto dello 0% perché la spesa 64 non raggiunge i criteri per nessuno sconto.

Un altro vantaggio della funzione XLOOKUP è che non richiede che l'intervallo di ricerca sia in ordine crescente come fa VLOOKUP.

Immettere una nuova riga nella parte inferiore della tabella di ricerca e quindi aprire la formula. Espandi l'intervallo utilizzato facendo clic e trascinando gli angoli.

La formula corregge immediatamente l'errore. Non è un problema avere lo "0" nella parte inferiore dell'intervallo.

Personalmente, ordinerei comunque la tabella in base alla colonna di ricerca. Avere "0" in fondo mi farebbe impazzire. Ma il fatto che la formula non si sia rotta è geniale.

XLOOKUP Sostituisce anche la funzione HLOOKUP

Come accennato, anche la funzione XLOOKUP è qui per sostituire HLOOKUP. Una funzione per sostituirne due. Eccellente!

La funzione HLOOKUP è la ricerca orizzontale, utilizzata per la ricerca lungo le righe.

Non così noto come il suo fratello VLOOKUP, ma utile per esempi come di seguito in cui le intestazioni si trovano nella colonna A e i dati si trovano lungo le righe 4 e 5.

XLOOKUP può guardare in entrambe le direzioni: colonne in basso e anche lungo le righe. Non abbiamo più bisogno di due funzioni diverse.

In questo esempio, la formula viene utilizzata per restituire il valore di vendita relativo al nome nella cella A2. Cerca lungo la riga 4 per trovare il nome e restituisce il valore dalla riga 5: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP può guardare dal basso verso l'alto

In genere, è necessario cercare un elenco per trovare la prima (spesso unica) occorrenza di un valore. XLOOKUP ha un sesto argomento chiamato modalità di ricerca. Ciò ci consente di cambiare la ricerca per iniziare dal basso e cercare un elenco per trovare invece l'ultima occorrenza di un valore.

Nell'esempio seguente, vorremmo trovare il livello delle scorte per ciascun prodotto nella colonna A.

La tabella di ricerca è in ordine di data e sono disponibili più controlli delle scorte per prodotto. Vogliamo restituire il livello delle scorte dall'ultima volta che è stato controllato (ultima occorrenza dell'ID prodotto).

Il sesto argomento della funzione XLOOKUP fornisce quattro opzioni. Siamo interessati a utilizzare l'opzione "Cerca dall'ultimo al primo".

La formula completata è mostrata qui: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

In questa formula, il quarto e il quinto argomento sono stati ignorati. È facoltativo e volevamo l'impostazione predefinita di una corrispondenza esatta.

Arrotondare

La funzione XLOOKUP è l'attesissimo successore di entrambe le funzioni VLOOKUP e HLOOKUP.

In questo articolo sono stati utilizzati numerosi esempi per dimostrare i vantaggi di XLOOKUP. Uno di questi è che XLOOKUP può essere utilizzato su fogli, cartelle di lavoro e anche con tabelle. Gli esempi sono stati mantenuti semplici nell'articolo per aiutare la nostra comprensione.

A causa della prossima introduzione di matrici dinamiche in Excel, può anche restituire un intervallo di valori. Questo è sicuramente qualcosa che vale la pena esplorare ulteriormente.

I giorni di VLOOKUP sono contati. XLOOKUP è qui e sarà presto la formula di ricerca de facto.