Oltre20 esempi di VLOOKUP per utenti principianti e avanzati di Excel
La funzione VLOOKUP è una delle più utilizzate in Excel. In questo tutorial scoprirai come utilizzare la funzione VLOOKUP in Excel con decine di esempi pratici, sia di base che avanzati, spiegati passo dopo passo.
Indice dei contenuti:
1. Introduzione alla funzione VLOOKUP – Sintassi e argomenti
- 2.1 VLOOKUP con corrispondenza esatta e approssimativa
- 2.2 VLOOKUP sensibile alle maiuscole/minuscole
- 2.3 VLOOKUP da destra a sinistra
- 2.4 VLOOKUP del secondo, ennesimo o ultimo valore corrispondente
- 2.5 VLOOKUP tra due valori o date specifiche
- 2.6 Utilizzo dei caratteri jolly per corrispondenze parziali nella funzione VLOOKUP
- 2.7 VLOOKUP di valori da un altro foglio di lavoro
- 2.8 VLOOKUP di valori da un'altra cartella di lavoro
- 2.9 VLOOKUP e restituzione di una cella vuota o testo specifico invece di0 o valore di errore #N/A
- 3.1 Ricerca bidirezionale con la funzione VLOOKUP (VLOOKUP su riga e colonna)
- 3.2 VLOOKUP del valore corrispondente in base a due o più criteri
- 3.3 VLOOKUP per restituire più valori corrispondenti con una o più condizioni
- 3.4 VLOOKUP per restituire l'intera riga di una cella corrispondente
- 3.5 Eseguire più funzioni VLOOKUP (VLOOKUP annidato) in Excel
- 3.6 VLOOKUP per verificare se un valore esiste in base a un elenco dati in un'altra colonna
- 3.7 VLOOKUP e somma di tutti i valori corrispondenti in righe o colonne
- 3.8 VLOOKUP per unire due tabelle in base a una o più colonne chiave
- 3.9 VLOOKUP di valori corrispondenti su più fogli di lavoro
4. VLOOKUP: mantenere la formattazione delle celle dei valori corrispondenti
Scarica i file di esempio VLOOKUP
Esempi base di VLOOKUP | Esempi avanzati di VLOOKUP | VLOOKUP: mantenere la formattazione delle celle
Introduzione alla funzione VLOOKUP – Sintassi e argomenti
In Excel, la funzione VLOOKUP è uno strumento potente per la maggior parte degli utenti, poiché consente di cercare un valore nella colonna più a sinistra dell'area dati e restituire un valore corrispondente nella stessa riga da una colonna specificata, come mostrato nello screenshot seguente.
Sintassi della funzione VLOOKUP:
Argomenti:
"Lookup_value" (obbligatorio): Il valore che desideri cercare. Può essere un valore (numero, data o testo) oppure un riferimento di cella. Deve trovarsi nella prima colonna dell'intervallo table_array.
"Table_array" (obbligatorio): L'intervallo dati o la tabella in cui si trovano la colonna del valore da cercare e la colonna del valore da restituire.
"Col_index_num" (obbligatorio): Il numero della colonna che contiene i valori da restituire. Parte da1 dalla colonna più a sinistra dell'intervallo tabella.
"Range_lookup" (opzionale): Un valore logico che determina se la funzione VLOOKUP restituirà una corrispondenza esatta o approssimativa.
- "Corrispondenza approssimativa" –1 / VERO / omesso (predefinito): Se non viene trovata una corrispondenza esatta, la formula cerca il valore più vicino, ovvero il valore più grande che sia inferiore al valore da cercare.
- "Corrispondenza esatta" –0 / FALSO: Si utilizza per cercare un valore esattamente uguale al valore da cercare. Se non viene trovata una corrispondenza esatta, verrà restituito il valore di errore #N/D.
Note sulla funzione:
- La funzione VLOOKUP cerca un valore solo da sinistra a destra.
- La funzione VLOOKUP esegue una ricerca non sensibile alle maiuscole/minuscole.
- Se ci sono più valori corrispondenti in base al valore cercato, la funzione VLOOKUP restituirà solo il primo valore trovato.
Esempi base di VLOOKUP
In questa sezione, vedremo alcune formule VLOOKUP che vengono utilizzate frequentemente.
2.1.1 Eseguire un VLOOKUP con corrispondenza esatta
Normalmente, se desideri trovare una corrispondenza esatta con la funzione VLOOKUP, basta utilizzare FALSO come ultimo argomento.
Ad esempio, per ottenere i punteggi di Matematica corrispondenti in base a specifici numeri ID, procedi così:
Copia e incolla la formula qui sotto in una cella vuota (in questo esempio, seleziono G2) e premi il tasto "Invio" per ottenere il risultato:
=VLOOKUP(F2,$A$2:$D$7,3,FALSE)
Nota: Nella formula sopra sono presenti quattro argomenti:
- "F2" è la cella che contiene il valore C1005 che vuoi cercare;
- "A2:D7" è l'intervallo tabella in cui stai effettuando la ricerca;
- "3" è il numero della colonna da cui viene restituito il valore corrispondente; (Una volta che la funzione trova l'ID - C1005, passerà alla terza colonna dell'intervallo tabella e restituirà il valore nella stessa riga dell'ID - C1005.)
- "FALSO" indica la corrispondenza esatta.
Come funziona la formula VLOOKUP?
Per prima cosa, cerca l'ID - C1005 nella colonna più a sinistra della tabella. Scorre dall'alto verso il basso e trova il valore nella cella A6.
Appena trova il valore, si sposta a destra nella terza colonna ed estrae il valore corrispondente.
Quindi, otterrai il risultato come mostrato nello screenshot qui sotto:
Kutools per Excel Vanta Oltre 300 Funzionalità, Assicurando Che Ciò Di Cui Hai Bisogno Sia A Portata di Click...
2.1.2 Eseguire un VLOOKUP con corrispondenza approssimativa
La corrispondenza approssimativa è utile per cercare valori tra intervalli di dati. Se non viene trovata una corrispondenza esatta, il VLOOKUP approssimativo restituirà il valore più grande che sia inferiore al valore da cercare.
Ad esempio, se hai il seguente intervallo di dati e gli ordini specificati non sono presenti nella colonna Ordini, come puoi ottenere lo Sconto più vicino nella colonna B?
Passaggio1: Applica la formula VLOOKUP e copiala nelle altre celle
Copia e incolla la formula seguente in una cella dove vuoi ottenere il risultato, quindi trascina il quadratino di riempimento verso il basso per applicare questa formula alle altre celle.
=VLOOKUP(D2,$A$2:$B$9,2,TRUE)
Risultato:
Ora otterrai le corrispondenze approssimative in base ai valori forniti, vedi screenshot:
Note:
- Nella formula sopra:
- "D2" è il valore di cui vuoi restituire le informazioni relative;
- "A2:B9" è l'intervallo dati;
- "2" indica il numero della colonna da cui viene restituito il valore corrispondente;
- "VERO" indica la corrispondenza approssimativa.
- La corrispondenza approssimativa restituirà il valore più grande che sia inferiore al valore da cercare, se non viene trovata una corrispondenza esatta.
- Per utilizzare la funzione VLOOKUP per ottenere un valore con corrispondenza approssimativa, è necessario ordinare la colonna più a sinistra dell'intervallo dati in ordine crescente, altrimenti verrà restituito un risultato errato.
2.2 Eseguire un VLOOKUP sensibile alle maiuscole/minuscole in Excel
Per impostazione predefinita, la funzione VLOOKUP esegue una ricerca non sensibile alle maiuscole/minuscole, trattando caratteri minuscoli e maiuscoli come identici. A volte potresti aver bisogno di una ricerca sensibile alle maiuscole/minuscole in Excel, ma la funzione VLOOKUP standard non lo consente. In questi casi, puoi utilizzare funzioni alternative come INDEX e MATCH insieme a EXACT, oppure LOOKUP ed EXACT.
Ad esempio, ho il seguente intervallo dati in cui la colonna ID contiene stringhe di testo sia in maiuscolo che in minuscolo. Ora voglio restituire il punteggio di Matematica corrispondente all'ID fornito.
Passaggio1: Applica una delle formule e copiala nelle altre celle
Copia e incolla una delle formule seguenti in una cella vuota dove vuoi ottenere il risultato. Poi, seleziona la cella della formula e trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula.
Formula1: Dopo aver incollato la formula, premi i tasti "Ctrl" + "Shift" + "Invio".
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))
Formula2: Dopo aver incollato la formula, premi il tasto "Invio".
=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)
Risultato:
Otterrai così i risultati corretti di cui hai bisogno. Vedi screenshot:
Note:
- Nella formula sopra:
- "A2:A10" è la colonna che contiene i valori specifici da cercare;
- "F2" è il valore da cercare;
- "C2:C10"è la colonna da cui verrà restituito il risultato.
- Se vengono trovate più corrispondenze, questa formula restituirà sempre l'ultima corrispondenza.
2.3 VLOOKUP di valori da destra a sinistra in Excel
La funzione VLOOKUP cerca sempre un valore nella colonna più a sinistra di un intervallo dati e restituisce il valore corrispondente da una colonna a destra. Se vuoi eseguire un VLOOKUP inverso, cioè cercare un valore specifico in una colonna a destra e restituire il valore corrispondente nella colonna a sinistra, come mostrato nello screenshot seguente:
Clicca qui per scoprire i dettagli passo dopo passo su questa operazione…
2.4 VLOOKUP del secondo, ennesimo o ultimo valore corrispondente in Excel
Normalmente, se vengono trovati più valori corrispondenti utilizzando la funzione VLOOKUP, verrà restituito solo il primo record trovato. In questa sezione vedremo come ottenere il secondo, ennesimo o ultimo valore corrispondente in un intervallo dati.
2.4.1 VLOOKUP e restituzione del secondo o ennesimo valore corrispondente
Supponiamo di avere un elenco di nomi nella colonna A e i corsi di formazione acquistati nella colonna B. Ora vuoi trovare il secondo o ennesimo corso acquistato dal cliente specificato. Vedi screenshot:
In questo caso, la funzione VLOOKUP potrebbe non risolvere direttamente il problema. Tuttavia, puoi utilizzare la funzione INDEX come alternativa.
Passaggio1: Applica e copia la formula nelle altre celle
Ad esempio, per ottenere il secondo valore corrispondente in base ai criteri forniti, applica la seguente formula in una cella vuota e premi contemporaneamente i tasti "Ctrl" + "Shift" + "Invio" per ottenere il primo risultato. Poi, seleziona la cella della formula e trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula.
=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))
Risultato:
Ora tutti i secondi valori corrispondenti in base ai nomi forniti sono stati visualizzati.
Nota: Nella formula sopra:
- "A2:A14" è l'intervallo con tutti i valori da cercare;
- "B2:B14" è l'intervallo dei valori corrispondenti da restituire;
- "E2" è il valore da cercare;
- "2" indica il secondo valore corrispondente che vuoi ottenere; per restituire il terzo valore corrispondente, basta cambiarlo in3.
2.4.2 VLOOKUP e restituzione dell'ultimo valore corrispondente
Se desideri eseguire un VLOOKUP e restituire l'ultimo valore corrispondente come mostrato nello screenshot seguente, il tutorial VLOOKUP e restituzione dell'ultimo valore corrispondente può aiutarti a ottenere il risultato desiderato in dettaglio.
2.5 VLOOKUP di valori corrispondenti tra due valori o date specifiche
A volte potresti voler cercare valori compresi tra due valori o date e restituire i risultati corrispondenti come mostrato nello screenshot qui sotto. In questi casi, puoi utilizzare la funzione LOOKUP invece di VLOOKUP con una tabella ordinata.
2.5.1 VLOOKUP di valori corrispondenti tra due valori o date specifiche con una formula
Passaggio1: Organizza i dati e applica la seguente formula
La tua tabella originale deve essere un intervallo dati ordinato. Poi, copia o inserisci la formula seguente in una cella vuota. Quindi, trascina il quadratino di riempimento per applicare questa formula alle altre celle necessarie.
=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)
Risultato:
Ora otterrai tutti i record corrispondenti in base al valore fornito, vedi screenshot:
Note:
- Nella formula sopra:
- "A2:A6" è l'intervallo dei valori minori;
- "B2:B6" è l'intervallo dei numeri maggiori;
- "E2" è il valore da cercare di cui vuoi ottenere il valore corrispondente;
- "C2:C6" è la colonna da cui vuoi restituire un valore corrispondente.
- Questa formula può essere utilizzata anche per estrarre valori corrispondenti tra due date come mostrato nello screenshot seguente:
2.5.2 VLOOKUP di valori corrispondenti tra due valori o date specifiche con una funzione pratica
Se trovi difficile ricordare e comprendere la formula sopra, qui ti presento uno strumento semplice – "Kutools per Excel". Con la funzione "LOOKUP tra due valori", puoi restituire l'elemento corrispondente in base a un valore o una data specifica compresa tra due valori o date con facilità.
- Clicca su "Kutools" > "Super RICERCA.VERT" > "Trova dati tra due valori" per attivare questa funzione.
- Poi specifica le operazioni dalla finestra di dialogo in base ai tuoi dati.
2.6 Utilizzo dei caratteri jolly per corrispondenze parziali nella funzione VLOOKUP
In Excel, i caratteri jolly possono essere utilizzati all'interno della funzione VLOOKUP, consentendo di effettuare una corrispondenza parziale su un valore da cercare. Ad esempio, puoi usare VLOOKUP per restituire un valore corrispondente da una tabella in base a una parte del valore da cercare.
Supponiamo di avere un intervallo di dati come mostrato nello screenshot seguente e di voler estrarre il punteggio in base al nome (non al nome completo). Come risolvere questo compito in Excel?
Passaggio1: Applica la formula e copiala nelle altre celle
Copia o inserisci la formula seguente in una cella vuota, quindi trascina il quadratino di riempimento per applicare questa formula alle altre celle necessarie:
=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)
Risultato:
Tutti i punteggi corrispondenti sono stati restituiti come mostrato nello screenshot seguente:
Nota: Nella formula sopra:
- "E2&“*”" è il criterio per la corrispondenza parziale. Significa che stai cercando qualsiasi valore che inizi con il valore nella cella E2. (Il carattere jolly “*” indica uno o più caratteri qualsiasi)
- "A2:C11" è l'intervallo di dati in cui vuoi cercare il valore corrispondente;
- "3" significa restituire il valore corrispondente dalla terza colonna dell'intervallo dati;
- "FALSO" indica la corrispondenza esatta. (Quando si utilizzano i caratteri jolly, è necessario impostare l'ultimo argomento della funzione su FALSO o0 per abilitare la modalità di corrispondenza esatta in VLOOKUP.)
- Per trovare e restituire i valori corrispondenti che terminano con un valore specifico, devi inserire il carattere jolly "*" davanti al valore. Applica questa formula:
-
=VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)
- Per cercare e restituire il valore corrispondente in base a una parte della stringa di testo, sia che il testo specificato sia all'inizio, alla fine o al centro della stringa, basta racchiudere il riferimento di cella o il testo tra due asterischi (*). Procedi con questa formula
-
=VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)
2.7 VLOOKUP di valori da un altro foglio di lavoro
Di solito, può capitare di lavorare con più di un foglio di lavoro; la funzione VLOOKUP può essere utilizzata per cercare dati da un altro foglio proprio come su uno stesso foglio.
Ad esempio, hai due fogli di lavoro come mostrato nello screenshot seguente; per cercare e restituire i dati corrispondenti dal foglio specificato, segui questi passaggi:
Passaggio1: Applica la formula e copiala nelle altre celle
Inserisci o copia la formula qui sotto in una cella vuota dove vuoi ottenere gli elementi corrispondenti. Poi, trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula.
=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)
Risultato:
Otterrai i risultati corrispondenti come desideri, vedi screenshot:
![]() | ![]() | ![]() |
Nota: Nella formula sopra:
- "A2"rappresenta il valore da cercare;
- "'Data sheet'!A2:C15" indica di cercare i valori nell'intervallo A2:C15 sul foglio chiamato Data sheet; (Se il nome del foglio contiene spazi o caratteri di punteggiatura, racchiudi il nome del foglio tra apici singoli, altrimenti puoi usare direttamente il nome del foglio come:
=VLOOKUP(A2,Datasheet!$A$2:$C$15,3,0) ). - "3" è il numero della colonna che contiene i dati corrispondenti da restituire;
- "0" significa eseguire una corrispondenza esatta.
2.8 VLOOKUP di valori da un'altra cartella di lavoro
In questa sezione vedremo come cercare e restituire i valori corrispondenti da una cartella di lavoro diversa utilizzando la funzione VLOOKUP.
Ad esempio, supponiamo di avere due cartelle di lavoro. La prima contiene un elenco di prodotti e i relativi costi. Nella seconda cartella di lavoro vuoi estrarre il costo corrispondente per ciascun prodotto, come mostrato nello screenshot seguente.
Passaggio1: Applica la formula
Apri entrambe le cartelle di lavoro che vuoi utilizzare, poi applica la formula seguente in una cella della seconda cartella di lavoro dove vuoi ottenere il risultato. Quindi, trascina e copia questa formula nelle altre celle necessarie
=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)
Risultato:
Note:
- Nella formula sopra:
- "B2" rappresenta il valore da cercare;
- "'[Product list.xlsx]Sheet1'!A2:B6" indica di cercare nell'intervallo A2:B6 sul foglio Sheet1 della cartella di lavoro Product list; (Il riferimento alla cartella di lavoro è racchiuso tra parentesi quadre e l'intero percorso cartella+foglio tra apici singoli.)
- "2" è il numero della colonna che contiene i dati corrispondenti da restituire;
- "0" indica di restituire una corrispondenza esatta.
- Se la cartella di lavoro di ricerca è chiusa, il percorso completo del file verrà visualizzato nella formula come mostrato nello screenshot seguente:
2.9 Restituire una cella vuota o un testo specifico invece di0 o errore #N/D
Di solito, quando utilizzi la funzione VLOOKUP per restituire un valore corrispondente, se la cella corrispondente è vuota, verrà restituito0. Se invece il valore corrispondente non viene trovato, otterrai un errore #N/D come mostrato nello screenshot qui sotto. Se vuoi visualizzare una cella vuota o un valore specifico invece di0 o #N/D, il tutorial VLOOKUP per restituire una cella vuota o un valore specifico invece di0 o N/D può esserti utile.
3.1 Ricerca bidirezionale (VLOOKUP su riga e colonna)
A volte potresti aver bisogno di eseguire una ricerca bidimensionale, cioè cercare un valore sia su riga che su colonna contemporaneamente. Ad esempio, se hai il seguente intervallo dati, potresti dover ottenere il valore di un prodotto specifico in un trimestre specifico. In questa sezione verrà presentata una formula per gestire questa esigenza in Excel.
In Excel puoi utilizzare una combinazione delle funzioni VLOOKUP e MATCH per eseguire una ricerca bidirezionale.
Applica la formula seguente in una cella vuota, quindi premi il tasto "Invio" per ottenere il risultato.
=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)
Nota: Nella formula sopra:
- "G2" è il valore da cercare nella colonna su cui vuoi ottenere il valore corrispondente;
- "A2:E7" è la tabella dati da cui cercare;
- "H1" è il valore da cercare nella riga su cui vuoi ottenere il valore corrispondente;
- "A2:E2" sono le celle delle intestazioni di colonna;
- "FALSO" indica di ottenere una corrispondenza esatta.
3.2 VLOOKUP del valore corrispondente in base a due o più criteri
È semplice cercare un valore corrispondente in base a un solo criterio, ma se hai due o più criteri, come puoi fare?
3.2.1 VLOOKUP del valore corrispondente in base a due o più criteri con formule
In questi casi, le funzioni LOOKUP o MATCH e INDEX in Excel possono aiutarti a risolvere rapidamente e facilmente il problema.
Ad esempio, ho la seguente tabella dati; per restituire il prezzo corrispondente in base a uno specifico prodotto e taglia, le seguenti formule possono aiutarti.
Passaggio1: Applica una delle formule seguenti
Formula1: Inserisci la formula seguente e premi "Invio".
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))
Formula2: Inserisci la formula seguente e premi "Ctrl" + "Shift" + "Invio".
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
Risultato:
Note:
- Nelle formule sopra:
- "A2:A12=G1" significa cercare il criterio di G1 nell'intervallo A2:A12;
- "B2:B12=G2" significa cercare il criterio di G2 nell'intervallo B2:B12;
- "D2:D12" èl'intervallo da cui vuoi restituire il valore corrispondente.
- Se hai più di due criteri, basta aggiungere gli altri criteri alla formula, ad esempio:
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3.2.2 VLOOKUP del valore corrispondente in base a due o più criteri con Kutools per Excel
Può essere difficile ricordare le formule complesse sopra da applicare ripetutamente, rallentando così il tuo lavoro. Tuttavia, "Kutools per Excel" offre la funzione "Ricerca multi-condizione" che ti permette di restituire il risultato corrispondente in base a una o più condizioni con pochi clic.
- Clicca su "Kutools" > "Super RICERCA.VERT" > "Ricerca multi-condizione" per attivare questa funzione.
- Poi specifica le operazioni dalla finestra di dialogo in base ai tuoi dati.
3.3 VLOOKUP per restituire più valori con uno o più criteri
In Excel, la funzione VLOOKUP cerca un valore e restituisce solo il primo valore corrispondente se vengono trovati più valori. A volte potresti voler restituire tutti i valori corrispondenti in una riga, in una colonna o in una singola cella. In questa sezione vedremo come restituire più valori corrispondenti con una o più condizioni in una cartella di lavoro.
3.3.1 VLOOKUP di tutti i valori corrispondenti in base a una o più condizioni in orizzontale
Supponendo di avere una tabella di dati che contiene paese, città e nomi nell'intervallo A1:C14, e ora vuoi restituire tutti i nomi in orizzontale che provengono dagli "US" come mostrato nello screenshot seguente. Per risolvere questo compito, clicca qui per vedere la soluzione passo dopo passo.
3.3.2 VLOOKUP di tutti i valori corrispondenti in base a una o più condizioni in verticale
Se hai bisogno di eseguire un VLOOKUP e restituire tutti i valori corrispondenti in verticale in base a criteri specifici come mostrato nello screenshot seguente, clicca qui per vedere la soluzione in dettaglio.
3.3.3 VLOOKUP di tutti i valori corrispondenti in base a una o più condizioni in una sola cella
Se vuoi eseguire un VLOOKUP e restituire più valori corrispondenti in una sola cella con un separatore specifico, la nuova funzione TEXTJOIN può aiutarti a risolvere rapidamente e facilmente questo compito.
Note:
- La funzione TEXTJOIN è disponibile solo in Excel2019, Excel365 e versioni successive.
- Se utilizzi Excel2016 o versioni precedenti, usa la Funzione Definita dall'Utente dell'articolo seguente:
- VLOOKUP per restituire più valori in una cella in Excel
3.4 VLOOKUP per restituire l'intera riga di una cella corrispondente
In questa sezione vedremo come recuperare l'intera riga di un valore corrispondente utilizzando la funzione VLOOKUP.
Passaggio1: Applica la formula seguente
Copia o digita la formula qui sotto in una cella vuota dove vuoi visualizzare il risultato e premi il tasto "Invio" per ottenere il primo valore. Poi, trascina la cella della formula verso destra fino a visualizzare tutti i dati della riga.
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
Risultato:
Ora puoi vedere che tutti i dati della riga sono stati restituiti. Vedi screenshot:
Nota: nella formula sopra:
- "F2" è il valore da cercare in base al quale vuoi restituire l'intera riga;
- "A1:D12" è l'intervallo dati in cui vuoi cercare il valore;
- "A1" indica il numero della prima colonna all'interno dell'intervallo dati;
- "FALSO" indica una ricerca esatta.
Suggerimenti:
- Se vengono trovate più righe in base al valore corrispondente, per restituire tutte le righe corrispondenti, applica la formula qui sotto, poi premi contemporaneamente i tasti "Ctrl" + "Shift" + "Invio" per ottenere il primo risultato. Poi trascina il quadratino di riempimento verso destra. Continua quindi a trascinare il quadratino di riempimento verso il basso nelle celle per ottenere tutte le righe corrispondenti. Vedi la demo qui sotto:
=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
3.5 VLOOKUP annidato in Excel
A volte potresti dover cercare valori collegati tra più tabelle. In questi casi, puoi annidare più funzioni VLOOKUP per ottenere il valore finale.
Ad esempio, ho un foglio di lavoro che contiene due tabelle separate. La prima elenca tutti i nomi dei prodotti insieme ai rispettivi venditori. La seconda mostra le vendite totali di ciascun venditore. Ora, se vuoi trovare le vendite di ciascun prodotto, come mostrato nello screenshot seguente, puoi annidare la funzione VLOOKUP per ottenere il risultato.
Formula generica per la funzione VLOOKUP annidata:
Note:
- "lookup_value" è il valore che stai cercando;
- "Table_array1","Table_array2"sono le tabelle in cui esistono il valore da cercare e il valore da restituire;
- "col_index_num1" indica il numero della colonna nella prima tabella per trovare i dati intermedi comuni;
- "col_index_num2" indica il numero della colonna nella seconda tabella da cui vuoi restituire il valore corrispondente;
- "0" viene utilizzato per una corrispondenza esatta.
Passaggio1: Applica e copia la formula seguente
Applica la formula seguente in una cella vuota, poi trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula.
=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)
Risultato:
Ora otterrai il risultato come mostrato nello screenshot seguente:
Note: nella formula sopra:
- "G3"contiene il valore che stai cercando;
- "A3:B7","D3:E7"sono gli intervalli tabella in cui esistono il valore da cercare e il valore da restituire;
- "2"è il numero della colonna nell'intervallo da cui restituire il valore corrispondente.
- "0" indica una corrispondenza esatta in VLOOKUP.
3.6 Verificare se un valore esiste in base a un elenco dati in un'altra colonna
La funzione VLOOKUP può anche aiutarti a verificare se i valori esistono in base all'elenco dati in un'altra colonna. Ad esempio, se vuoi cercare i nomi nella colonna C e restituire solo Sì o No se il nome è presente o meno nella colonna A come mostrato nello screenshot seguente.
Passaggio1: Applica la formula seguente
Applica la formula seguente in una cella vuota, poi trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula.
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")
Risultato:
Otterrai il risultato desiderato, vedi screenshot:
Note: nella formula sopra:
- "C2" è il valore da cercare che vuoi verificare;
- "A2:A10" è l'elenco dell'intervallo da cui verificare se i valori da cercare saranno trovati o meno;
- "FALSO" indica di ottenere una corrispondenza esatta.
3.7 VLOOKUP e somma di tutti i valori corrispondenti in righe o colonne
Quando lavori con dati numerici, potresti aver bisogno di estrarre i valori corrispondenti da una tabella e sommare i numeri in più colonne o righe. In questa sezione verranno presentate alcune formule che possono aiutarti a svolgere questo compito.
3.7.1 VLOOKUP e somma di tutti i valori corrispondenti in una riga o più righe
Supponiamo di avere un elenco di prodotti con le vendite di diversi mesi, come mostrato nello screenshot seguente. Ora devi sommare tutti gli ordini di tutti i mesi in base ai prodotti forniti.
Passaggio1: Applica la formula seguente
Copia o inserisci la formula seguente in una cella vuota, poi premi contemporaneamente i tasti "Ctrl" + "Shift" + "Invio" per ottenere il primo risultato. Poi, trascina il quadratino di riempimento verso il basso per copiare questa formula nelle altre celle necessarie.
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
Risultato:
Tutti i valori in una riga del primo valore corrispondente sono stati sommati insieme, vedi screenshot:
Note: nella formula sopra:
- "H2" è la cella che contiene il valore che stai cercando;
- "A2:F9" è l'intervallo dati (senza intestazioni di colonna) che include il valore da cercare e i valori corrispondenti;
- "{2,3,4,5,6}" sono i numeri delle colonne utilizzati per calcolare il totale dell'intervallo;
- "FALSO" indica una corrispondenza esatta.
Suggerimento: Se vuoi sommare tutte le corrispondenze in più righe, usa la formula seguente:
-
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
3.7.2 VLOOKUP e somma di tutti i valori corrispondenti in una colonna o più colonne
Se vuoi sommare il valore totale per mesi specifici come mostrato nello screenshot seguente, la funzione VLOOKUP standard potrebbe non aiutarti. Qui dovresti utilizzare insieme le funzioni SOMMA, INDICE e CONFRONTA per creare una formula.
Passaggio1: Applica la formula seguente
Applica la formula qui sotto in una cella vuota, poi trascina il quadratino di riempimento verso il basso per copiare questa formula nelle altre celle.
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
Risultato:
Ora i primi valori corrispondenti in base al mese specifico in una colonna sono stati sommati insieme, vedi screenshot:
Note: nella formula sopra:
- "H2" è la cella che contiene il valore che stai cercando;
- "B1:F1" sono le intestazioni di colonna che contengono il valore da cercare;
- "B2:F9"è l'intervallo dati che contiene i valori numerici che vuoi sommare.
Suggerimenti: Per eseguire un VLOOKUP e sommare tutti i valori corrispondenti in più colonne, utilizza la formula seguente:
-
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
3.7.3 VLOOKUP e somma del primo o di tutti i valori corrispondenti con Kutools per Excel
Forse le formule sopra sono difficili da ricordare; in questo caso ti consiglio una funzione potente - "Cerca e somma" di "Kutools per Excel". Con questa funzione puoi eseguire un VLOOKUP e sommare il primo valore corrispondente o tutti i valori corrispondenti in righe o colonne in modo semplice e veloce.
- Clicca su "Kutools" > "Super RICERCA.VERT" > "Cerca e somma" per attivare questa funzione.
- Poi specifica le operazioni dalla finestra di dialogo in base alle tue esigenze.
3.7.4 VLOOKUP e somma di tutti i valori corrispondenti sia in righe che in colonne
Se vuoi sommare i valori quando devi confrontare sia la colonna che la riga, ad esempio per ottenere il valore totale del prodotto Maglione nel mese di Mar come mostrato nello screenshot seguente.
Qui puoi utilizzare la funzione SOMMAPRODOTTO per svolgere questo compito.
Applica la formula seguente in una cella, poi premi il tasto "Invio" per ottenere il risultato, vedi screenshot:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
Note: Nella formula sopra:
- "B2:F9" è l'intervallo dati che contiene i valori numerici da sommare;
- "B1:F1" sono le intestazioni di colonna che contengono il valore da cercare su cui vuoi sommare;
- "I2" è il valore da cercare tra le intestazioni di colonna;
- "A2:A9" sono le intestazioni di riga che contengono il valore da cercare su cui vuoi sommare;
- "H2" è il valore da cercare tra le intestazioni di riga.
3.8 VLOOKUP per unire due tabelle in base alle colonne chiave
Nel lavoro quotidiano, durante l'analisi dei dati, potresti aver bisogno di raccogliere tutte le informazioni necessarie in un'unica tabella in base a una o più colonne chiave. Per svolgere questo compito, puoi utilizzare le funzioni INDEX e MATCH invece di VLOOKUP.
3.8.1 VLOOKUP per unire due tabelle in base a una colonna chiave
Ad esempio, hai due tabelle: la prima contiene dati su prodotti e nomi, la seconda su prodotti e ordini. Ora vuoi combinare queste due tabelle abbinando la colonna prodotto comune in un'unica tabella.
Passaggio1: Applica la formula seguente
Applica la formula seguente in una cella vuota. Poi, trascina il quadratino di riempimento verso il basso nelle celle dove vuoi applicare questa formula
=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))
Risultato:
Ora otterrai una tabella unita con la colonna ordini aggiunta alla prima tabella in base ai dati della colonna chiave.
Note: Nella formula sopra:
- "A2" è il valore da cercare che stai cercando;
- "F2:F8" è l'intervallo dati da cui vuoi restituire i valori corrispondenti;
- "E2:E8" è l'intervallo di ricerca che contiene il valore da cercare.
3.8.2 VLOOKUP per unire due tabelle in base a più colonne chiave
Se le due tabelle che vuoi unire hanno più colonne chiave, per unirle in base a queste colonne comuni, segui i passaggi seguenti.
La formula generica è:
Note:
- "tabella_ricerca" è l'intervallo dati che contiene i dati da cercare e i record corrispondenti;
- "valore_ricerca1" è il primo criterio che stai cercando;
- "intervallo_ricerca1" è l'elenco dati che contiene il primo criterio;
- "valore_ricerca2" è il secondo criterio che stai cercando;
- "intervallo_ricerca2" è l'elenco dati che contiene il secondo criterio;
- "numero_colonna_restituita" indica il numero della colonna nella tabella_ricerca da cui vuoi restituire il valore corrispondente.
Passaggio1: Applica la formula seguente
Applica la formula qui sotto in una cella vuota dove vuoi visualizzare il risultato, poi premi contemporaneamente i tasti "Ctrl" + "Shift" + "Invio" per ottenere il primo valore corrispondente, vedi screenshot:
=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)
Passaggio2: Copia la formula nelle altre celle
Poi, seleziona la prima cella della formula e trascina il quadratino di riempimento per copiare questa formula nelle altre celle necessarie:
3.9 VLOOKUP di valori corrispondenti su più fogli di lavoro
Ti è mai capitato di dover eseguire un VLOOKUP su più fogli di lavoro in Excel? Ad esempio, se hai tre fogli con intervalli di dati e vuoi recuperare valori specifici in base a criteri da questi fogli, puoi seguire il tutorial passo dopo passo VLOOKUP di valori su più fogli di lavoro per svolgere questa operazione.
VLOOKUP: mantenere la formattazione delle celle dei valori corrispondenti
Quando si cercano valori corrispondenti, la formattazione originale della cella come colore del carattere, colore di sfondo, formato dati, ecc. non viene mantenuta. Per mantenere la formattazione della cella o dei dati, questa sezione presenta alcuni trucchi per risolvere il problema.
4.1 VLOOKUP del valore corrispondente e mantenimento del colore della cella, formattazione del carattere
Come sappiamo, la funzione VLOOKUP standard può solo recuperare il valore corrispondente da un altro intervallo dati. Tuttavia, può capitare di voler ottenere il valore corrispondente insieme alla formattazione della cella, come il colore di riempimento, il colore del carattere e lo stile del carattere. In questa sezione vedremo come recuperare i valori corrispondenti mantenendo la formattazione di origine in Excel.
Procedi con i seguenti passaggi per cercare e restituire il valore corrispondente insieme alla formattazione della cella:
Passaggio1: Copia il codice1 nel modulo codice del foglio
- Nel foglio che contiene i dati da cercare con VLOOKUP, fai clic destro sulla linguetta del foglio e seleziona "Visualizza codice" dal menu contestuale. Vedi screenshot:
- Nella finestra "Microsoft Visual Basic for Applications" aperta, copia il codice VBA qui sotto nella finestra del codice.
- Codice VBA1: VLOOKUP per ottenere la formattazione della cella insieme al valore cercato
-
Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Range(xDic.Keys(I)).Font.FontStyle = _ Range(xDic.Items(I)).Font.FontStyle Range(xDic.Keys(I)).Font.Size = _ Range(xDic.Items(I)).Font.Size Range(xDic.Keys(I)).Font.Color = _ Range(xDic.Items(I)).Font.Color Range(xDic.Keys(I)).Font.Name = _ Range(xDic.Items(I)).Font.Name Range(xDic.Keys(I)).Font.Underline = _ Range(xDic.Items(I)).Font.Underline Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End Sub
Passaggio2: Copia il codice2 nella finestra Modulo
- Sempre nella finestra "Microsoft Visual Basic for Applications", clicca su "Inserisci" > "Modulo" e poi copia il codice VBA2 qui sotto nella finestra "Modulo".
- Codice VBA2: VLOOKUP per ottenere la formattazione della cella insieme al valore cercato
-
Public xDic As New Dictionary Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = "" xDic.Add Application.Caller.Address, "" Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function
Passaggio3: Seleziona l'opzione per VBAproject
- Dopo aver inserito i codici sopra, clicca su "Strumenti" > "Riferimenti" nella finestra "Microsoft Visual Basic for Applications". Poi seleziona la casella "Microsoft Scripting Runtime" nella finestra di dialogo "Riferimenti – VBAProject". Vedi screenshot:
- Poi clicca su "OK" per chiudere la finestra di dialogo, quindi salva e chiudi la finestra del codice.
Passaggio4: Digita la formula per ottenere il risultato
- Ora torna al foglio di lavoro, applica la formula seguente. Poi trascina il quadratino di riempimento verso il basso per ottenere tutti i risultati insieme alla loro formattazione. Vedi screenshot:
=LookupKeepFormat(E2,$A$1:$C$10,3)
Note: nella formula sopra:
- "E2" è il valore da cercare;
- "A1:C10" è l'intervallo tabella;
- "3" è il numero della colonna della tabella da cui vuoi recuperare il valore corrispondente.
4.2 Mantenere il formato data da un valore restituito da VLOOKUP
Quando utilizzi la funzione VLOOKUP per cercare e restituire un valore con formato data, il risultato restituito potrebbe essere visualizzato come numero. Per mantenere il formato data nel risultato restituito, racchiudi la funzione VLOOKUP all'interno della funzione TESTO.
Passaggio1: Applica la formula seguente
Applica la formula qui sotto in una cella vuota. Poi trascina il quadratino di riempimento per copiare questa formula nelle altre celle.
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")
Risultato:
Tutte le date corrispondenti sono state restituite come mostrato nello screenshot seguente:
Note: Nella formula sopra:
- "E2" è il valore da cercare;
- "A2:C9" è l'intervallo di ricerca;
- "3" è il numero della colonna da cui vuoi che venga restituito il valore;
- "FALSO" indica di ottenere una corrispondenza esatta;
- "mm/gg/aaaa" è il formato data che vuoi mantenere.
4.3 Restituire il commento della cella da VLOOKUP
Ti è mai capitato di dover recuperare sia il dato della cella corrispondente che il relativo commento utilizzando VLOOKUP in Excel, come mostrato nello screenshot seguente? In tal caso, la Funzione Definita dall'Utente fornita qui sotto può aiutarti a svolgere questo compito.
Passaggio1: Copia il codice in un Modulo
- Tieni premuti i tasti "ALT" + "F11" per aprire la finestra "Microsoft Visual Basic for Applications".
- Clicca su "Inserisci" > "Modulo", poi copia e incolla il codice seguente nella finestra "Modulo".
Codice VBA: VLOOKUP e restituzione del valore corrispondente con commento della cella:Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant 'Updateby Extendoffice Application.Volatile Dim xRet As Variant 'could be an error Dim xCell As Range xRet = Application.Match(LookVal, FTable.Columns(1), FType) If IsError(xRet) Then VlookupComment = "Not Found" Else Set xCell = FTable.Columns(FColumn).Cells(1)(xRet) VlookupComment = xCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text End If End With End If End Function
- Poi salva e chiudi la finestra del codice.
Passaggio2: Digita la formula per ottenere il risultato
- Ora inserisci la formula seguente e trascina il quadratino di riempimento per copiare questa formula nelle altre celle. Verranno restituiti sia i valori corrispondenti che i commenti contemporaneamente, vedi screenshot:
=vlookupcomment(D2,$A$2:$B$9,2,FALSE)
Note: Nella formula sopra:
- "D2" è il valore da cercare di cui vuoi restituire il valore corrispondente;
- "A2:B9" è la tabella dati che vuoi utilizzare;
- "2" è il numero della colonna che contiene il valore corrispondente da restituire;
- "FALSO" indica di ottenere una corrispondenza esatta.
4.4 VLOOKUP di numeri memorizzati come testo
Ad esempio, ho un intervallo di dati in cui il numero ID nella tabella originale è in formato numerico e il numero ID nelle celle di ricerca è memorizzato come testo; potresti riscontrare un errore #N/D utilizzando la funzione VLOOKUP standard. In questo caso, per recuperare le informazioni corrette, puoi racchiudere le funzioni TESTO e VALORE all'interno della funzione VLOOKUP. Ecco la formula per ottenere questo risultato:
Passaggio1: Applica e copia la formula seguente
Applica la formula seguente in una cella vuota, poi trascina il quadratino di riempimento per copiare questa formula.
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))
Risultato:
Ora otterrai i risultati corretti come mostrato nello screenshot seguente:
Note:
- Nella formula sopra:
- "D2" è il valore da cercare di cui vuoi restituire il valore corrispondente;
- "A2:B8" è la tabella dati che vuoi utilizzare;
- "2" è il numero della colonna che contiene il valore corrispondente da restituire;
- "0" indica di ottenere una corrispondenza esatta.
- Questa formula funziona bene anche se non sei sicuro di dove ci siano numeri e dove testo.
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra, invece che in nuove finestre.
- Aumenta la tua produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Indice dei contenuti
- 1. Introduzione alla funzione VLOOKUP
- 2. Esempi base di VLOOKUP
- 2.1 VLOOKUP esatto e approssimativo
- Corrispondenza esatta
- Corrispondenza approssimativa
- 2.2 VLOOKUP sensibile alle maiuscole/minuscole
- 2.3 VLOOKUP da destra a sinistra
- 2.4 VLOOKUP del secondo, ennesimo o ultimo valore corrispondente
- Il secondo o ennesimo valore corrispondente
- L'ultimo valore corrispondente
- 2.5 VLOOKUP tra due valori
- Utilizzando una formula
- Utilizzando una funzione pratica - Kutools
- 2.6 VLOOKUP con corrispondenza parziale
- 2.7 VLOOKUP da un altro foglio di lavoro
- 2.8 VLOOKUP da un'altra cartella di lavoro
- 2.9 Correggere il valore0 o l'errore #N/D in VLOOKUP
- 3. Esempi avanzati di VLOOKUP
- 3.1 Ricerca bidirezionale
- 3.2 VLOOKUP in base a più criteri
- Utilizzando formule
- Utilizzando una funzione intelligente - Kutools
- 3.3 VLOOKUP di più valori corrispondenti
- Restituisci valori in orizzontale
- Restituisci valori in verticale
- Restituisci valori in una sola cella
- 3.4 VLOOKUP dell'intera riga
- 3.5 VLOOKUP annidato
- 3.6 Verifica se il valore esiste
- 3.7 VLOOKUP e somma
- Nelle righe
- Nelle colonne
- Con una funzione potente - Kutools
- Sia in righe che in colonne
- 3.8 VLOOKUP per unire due tabelle
- Per una colonna chiave
- Per più colonne chiave
- 3.9 VLOOKUP su più fogli di lavoro
- 4. VLOOKUP e mantenimento della formattazione delle celle
- 4.1 Mantenere colore e formattazione del carattere
- 4.2 Mantenere il formato data
- 4.3 Mantenere il commento della cella
- 4.4 Numeri memorizzati come testo
- I migliori strumenti di produttività per Office