Le funzioni di ricerca di Microsoft Excel sono potenti ma spesso fraintese perché hanno alcuni comportamenti che sembrano un po’ contrari a ciò che gli utenti si aspettano. Queste funzioni vengono utilizzate quando si desidera trovare valori in base al valore in una cella corrispondente.
Grazie alla nuova funzione di array dinamico XLOOKUP(), ora hai più potenza e meno confusione. In questo articolo, confronteremo XLOOKUP() con VLOOKUP() in modo che tu possa vedere come eccelle XLOOKUP() e iniziare a usarlo subito.
Sto usando Microsoft 365 su un sistema Windows 10 a 64 bit. Questa nuova funzione è disponibile in Microsoft 365 ed Excel 2021 ed Excel per il Web. Per tua comodità, puoi scaricare il file dimostrativo .xlsx . Questo articolo presuppone che tu abbia le competenze di base di Excel, ma anche un principiante dovrebbe essere in grado di seguire le istruzioni per avere successo.
Informazioni su XLOOKUP() in Excel
XLOOKUP() è una delle numerose nuove funzioni di array dinamico. Se hai mai inserito un’espressione usando Ctrl + Maiusc + Invio, hai già familiarità con il modo in cui Excel funzionava con gli array dinamici. Grazie alla nuova funzionalità di array dinamico, questi tipi di espressioni sono molto più facili da creare e mantenere perché puoi inserire l’espressione come faresti normalmente, con un semplice Invio. I risultati si riversano nelle celle sottostanti, riempiendo il numero necessario per completare i calcoli dell’espressione. Questo è chiamato l’intervallo di fuoriuscita. Se vedi un errore di versamento, l’intervallo necessario per soddisfare la funzione non è disponibile. Ciò significa che è possibile utilizzare una funzione per restituire più colonne (o righe) di valori risultanti.
XLOOKUP() restituisce i dati in una tabella o in un intervallo per riga. Potresti voler restituire il prezzo di un prodotto o il numero di telefono di un cliente. Utilizzando XLOOKUP(), puoi recuperare rapidamente le informazioni in base a un termine di ricerca in una cella corrispondente.
Ecco alcuni degli aggiornamenti di XLOOKUP:
- XLOOKUP() supporta le ricerche verticali e orizzontali.
- XLOOKUP() cerca a sinistra e a destra, quindi non dovrai più riorganizzare le colonne.
- XLOOKUP() supporta i riferimenti relativi in modo da poter inserire ed eliminare colonne (o righe) e la funzione si aggiornerà di conseguenza.
- XLOOKUP() per impostazione predefinita è una corrispondenza esatta, che è l’impostazione predefinita preferita; le funzioni di ricerca precedenti utilizzano per impostazione predefinita la corrispondenza più vicina.
- La nuova modalità di corrispondenza di XLOOKUP() consente ricerche più flessibili.
Ora diamo un’occhiata alla sintassi di questa funzione:
XLOOKUP(valore_ricerca, matrice_ricerca, matrice_ritorno, [se_non_trovato], [modalità_corrispondenza], [modalità_ricerca])
I primi tre argomenti sono obbligatori:
- lookup_value: il termine di ricerca.
- lookup_array: l’intervallo di ricerca (oi dati di origine).
- return_array: l’intervallo restituito (oi risultati).
- if_not_found: testo restituito quando non viene trovata una corrispondenza valida. Se omesso, la funzione restituisce #N/A.
- match_mode: specifica il tipo di corrispondenza. Vedere la tabella A per i valori e le spiegazioni appropriati.
- search_mode: specifica la modalità di ricerca. Vedere la tabella B per i valori e le spiegazioni appropriati.
Tabella A
| Valore | Spiegazione | |
| 0 | Trova una corrispondenza esatta; restituisce #N/A se non ne sono stati trovati. Questo è il valore predefinito dell’argomento. | |
| -1 | Trova una corrispondenza esatta; restituisce l’elemento successivo più piccolo se non viene trovata alcuna corrispondenza. | |
| 1 | Trova una corrispondenza esatta; restituisce l’elemento successivo più grande se non viene trovata alcuna corrispondenza. | |
| 2 | Consente una corrispondenza con caratteri jolly: *, ? e ~. |
Tabella B
| Valore | Spiegazione |
| 1 | Inizia la ricerca con il primo elemento. Questo è il valore predefinito per questo argomento. |
| -1 | Inizia la ricerca con l’ultimo elemento. |
| 2 | La ricerca richiede che lookup_array sia ordinato in ordine crescente. In caso contrario, la funzione restituisce risultati non validi, non un errore. |
| -2 | La ricerca richiede che lookup_array sia ordinato in ordine decrescente. In caso contrario, la funzione restituisce risultati non validi, non un errore. |
Sono molte informazioni, ma la maggior parte è simile alle funzioni di ricerca precedenti. Passiamo ora ad alcuni esempi.
Un rapido confronto tra CERCA.X() e CERCA.VERT()
XLOOKUP() può essere utilizzato per restituire un singolo valore, in modo simile a VLOOKUP(), ma utilizza argomenti diversi. Confrontiamo le due funzioni utilizzando il set di dati in Figura A . Nello specifico, restituiremo l’ID del dipendente e la data utilizzando il valore del personale come termine di ricerca (K1).
Figura A
Utilizzeremo le funzioni di ricerca per restituire valori in base a un termine di ricerca.
Innanzitutto, esaminiamo le funzioni ID:
K3: =CERCA.VERT($K$1,Tabella1[[Personale]:[ID]],2)
K4: =XLOOKUP($K$1,Tabella1[Personale],Tabella1[ID])
Entrambe le funzioni utilizzano il valore in K1, Luke, come termine di ricerca. La cosa più importante da menzionare è che la funzione CERCA.VERT() in K3 restituisce il valore sbagliato, mentre la funzione CERCA.X() in K4 restituisce il valore corretto. VLOOKUP() richiede un set di dati ordinato, ma XLOOKUP() no. XLOOKUP() restituisce il primo valore che corrisponde alle impostazioni predefinite.
Le funzioni in L3 e L4 tentano di restituire la data in base al valore di ricerca, Luke, utilizzando le funzioni
L3: =CERCA.VERT($K$1,Tabella1[[Personale]:[ID]],-1)
L4: =XLOOKUP($K$1,Tabella1[Personale],Tabella1[Data])
Probabilmente ti aspettavi il valore di errore in L3 perché VLOOKUP() non supporta una ricerca a sinistra del valore di ricerca; la funzione semplicemente non comprende l’argomento, -1. XLOOKUP() lo fa, e invece di usare un valore negativo, fai riferimento alla colonna effettiva e di nuovo, alla funzione non importa che il set di dati non sia ordinato. In questo semplice esempio, l’ordinamento dei dati non ha importanza, ma a volte devi lavorare con l’ordine del set di dati, quindi questo nuovo comportamento è un ottimo aggiornamento.
Rimani sintonizzato
A questo punto, puoi iniziare a utilizzare XLOOKUP() invece delle vecchie funzioni di ricerca, se lo desideri. Hai abbastanza informazioni per iniziare. Non preoccuparti di sostituire le vecchie funzioni di ricerca; è dubbio che Microsoft li deprecherà nel prossimo futuro.
Nel mio prossimo articolo, utilizzeremo funzionalità avanzate per utilizzare più criteri con XLOOKUP(). Impareremo anche come restituire più colonne con una funzione XLOOKUP().