La funzione CERCA. VERT è sempre stata una delle funzioni più potenti di Excel. Ti consente di cercare valori nella prima colonna di una tabella e di restituire valori dai campi a destra. Ma Excel ha anche una funzione chiamata XLOOKUP, che ti consente di cercare un valore in qualsiasi colonna o riga e restituire dati da qualsiasi altra colonna.
Come funziona XLOOKUP
La funzione XLOOKUP è molto più facile da usare rispetto alla funzione VLOOKUP, perché invece di specificare un valore per la colonna dei risultati, puoi specificare l'intero intervallo.
La funzione permette anche di cercare sia una colonna che una riga, individuando il valore nella cella che si interseca.
I parametri della funzione XLOOKUP sono i seguenti:
=XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
- lookup_value: Il valore che vuoi cercare
- lookup_array: L'array (colonna) in cui vuoi cercare
- return_array: Il risultato (colonna) da cui vuoi recuperare un valore
- match_mode (opzionale): seleziona una corrispondenza esatta (0), una corrispondenza esatta o un valore più piccolo successivo (-1) o una corrispondenza con caratteri jolly (2).
- modalità_ricerca (opzionale): Seleziona se cercare a partire dal primo elemento della colonna (1), l'ultimo elemento della colonna (-1), ricerca binaria ascendente (2) o ricerca binaria discendente (-2).
Le seguenti sono alcune delle ricerche più comuni che puoi eseguire con la funzione XLOOKUP.
Come cercare un singolo risultato usando XLOOKUP
Il modo più semplice per utilizzare XLOOKUP è cercare un singolo risultato utilizzando un punto dati da una colonna.
-
Questo foglio di calcolo di esempio è un elenco di ordini inviati dai rappresentanti di vendita, inclusi l'articolo, il numero di unità, il costo e la vendita totale.
Image -
Se vuoi trovare la prima vendita nell'elenco inviato da un rappresentante di vendita specifico, puoi creare una funzione XLOOKUP che ricerca un nome nella colonna Rep. La funzione restituirà il risultato dalla colonna Totale. La funzione XLOOKUP per questo è:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: Punta alla cella di ricerca Nome rappresentante
- C2:C44: Questa è la colonna Rep, che è l'array di ricerca
- G2:G33: questa è la colonna Total, che è l'array restituito
- 0: Seleziona una corrispondenza esatta
- 1: Seleziona la prima corrispondenza nei risultati
-
Quando premi Invio e digiti il nome di un rappresentante di vendita, la cella del risultato Totale ti mostrerà il primo risultato nella tabella per quel rappresentante di vendita.
Image -
Se vuoi cercare la vendita più recente (poiché la tabella è ordinata per data in ordine inverso), cambia l'ultimo argomento XLOOKUP in - 1, che inizierà la ricerca dall'ultima cella nell'array di ricerca e fornisci invece quel risultato.
Image -
Questo esempio mostra una ricerca simile che potresti eseguire con una funzione VLOOKUP utilizzando la colonna Rep come prima colonna della tabella di ricerca. Tuttavia, XLOOKUP ti consente di cercare qualsiasi colonna in entrambe le direzioni. Ad esempio, se desideri trovare il rappresentante di vendita che ha venduto il primo ordine Binder dell'anno, devi utilizzare la seguente funzione XLOOKUP:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: Punta alla cella di ricerca dell'oggetto
- D2:D44: Questa è la colonna Item, che è l'array di ricerca
- C2:C44: Questa è la colonna Rep, che è l'array di ritorno a sinistra dell'array di ricerca
- 0: Seleziona una corrispondenza esatta
- 1: Seleziona la prima corrispondenza nei risultati
-
Questa volta, il risultato sarà il nome del rappresentante di vendita che ha venduto il primo ordine di raccoglitore dell'anno.
Image
Esegui abbinamento verticale e orizzontale con XLOOKUP
Un' altra funzionalità di XLOOKUP di cui VLOOKUP non è in grado è la capacità di eseguire una ricerca sia verticale che orizzontale, il che significa che puoi cercare un elemento in fondo a una colonna e anche in una riga.
Questa doppia funzione di ricerca sostituisce efficacemente altre funzioni di Excel come INDICE, CONFRONTA o CERCA.
-
Nel seguente foglio di calcolo di esempio, le vendite di ciascun rappresentante di vendita sono divise per trimestre. Se volessi vedere le vendite del terzo trimestre per un rappresentante di vendita specifico, senza la funzione XLOOKUP, questo tipo di ricerca sarebbe difficile.
Image -
Con la funzione XLOOKUP, questo tipo di ricerca è facile. Usando la seguente funzione XLOOKUP, puoi cercare le vendite del terzo trimestre per un rappresentante di vendita specifico:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: Punta alla cella di ricerca Rep
- B2:B42: Questa è la colonna Item, che è l'array di ricerca della colonna
- K2: Punta alla cella di ricerca del quarto
- C1:H1: Questo è l'array di ricerca delle righe
- C2:H42: questo è l'array di ricerca per l'importo in dollari in ogni trimestre
Questa funzione XLOOKUP nidificata identifica prima il rappresentante di vendita e la successiva funzione XLOOKUP identifica il trimestre desiderato. Il valore restituito sarà la cella in cui quei due si intercettano.
-
Il risultato di questa formula è il quarto di guadagno per il rappresentante con il nome Thompson.
Image
Utilizzo della funzione XLOOKUP
La funzione XLOOKUP è disponibile solo per gli abbonati Office Insider, ma sarà presto implementata per tutti gli abbonati a Microsoft 365.
Se vuoi provare tu stesso la funzione, puoi diventare un Office Insider. Seleziona File > Account, quindi seleziona il menu a discesa Office Insider per iscriverti.
Una volta entrato nel programma Office Insider, la versione installata di Excel riceverà tutti gli ultimi aggiornamenti e potrai iniziare a utilizzare la funzione XLOOKUP.
