Funzione CERCA.VERT di Excel

La funzione VLOOKUP di Excel è uno strumento potente che ti permette di cercare un valore specifico confrontandolo con la prima colonna di una tabella o di un intervallo organizzato in verticale, restituendo un valore corrispondente da un’altra colonna nella stessa riga. Sebbene VLOOKUP sia estremamente utile, può risultare complessa da comprendere per i principianti. Questa guida ti aiuterà a padroneggiarla offrendoti una spiegazione passo passo degli argomenti, esempi pratici e soluzioni agli errori più comuni che potresti incontrare utilizzando la funzione VLOOKUP.

• Spiegazione passo passo degli argomenti
• Sintassi e argomenti
• Esempi di VLOOKUP
  Corrispondenza esatta vs. approssimativa | VLOOKUP con condizioni multiple
• Errori comuni di VLOOKUP e soluzioni
  Errore #N/D|Errore #VALORE!|Errore #RIF!|Valore errato

Esempi VLOOKUP.xlsx Errori VLOOKUP.xlsx

Video correlati

Spiegazione passo passo degli argomenti

Come illustrato nello screenshot precedente, la funzione VLOOKUP viene utilizzata per trovare un indirizzo email in base a un determinato ID. Di seguito ti spiego passo dopo passo come usare VLOOKUP in questo esempio, analizzando ciascun argomento nel dettaglio.

Passaggio 1: Avvia la funzione VLOOKUP

Seleziona una cella (in questo caso H6) per visualizzare il risultato, quindi avvia la funzione VLOOKUP digitando quanto segue nella Barra delle formule.

=VLOOKUP(

Passaggio 2: Specifica il valore da cercare

Innanzitutto, specifica il valore da cercare (ossia ciò che stai cercando) nella funzione CERCA.VERT. In questo caso, faccio riferimento alla cella G6, che contiene il numero ID 1005.

=VLOOKUP(G6

Passaggio 3: Specifica la matrice tabella

Successivamente, specifica un intervallo di celle che includa sia il valore cercato sia quello che desideri restituire. In questo caso, seleziono l’intervallo B6:E12. La formula ora appare così:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Passaggio 4: Specifica la colonna da cui desideri restituire un valore

Quindi, specifica la colonna da cui desideri recuperare un valore.

In questo esempio, poiché devo recuperare l’indirizzo email in base all’ID, inserisco il numero 4 per indicare a VLOOKUP di restituire il valore dalla quarta colonna dell’intervallo dati.

=VLOOKUP(G6,B6:E12,4

Passaggio 5: Trova una corrispondenza approssimativa o esatta

Infine, scegli se desideri una corrispondenza approssimativa o esatta.

• Per trovare una corrispondenza esatta, è necessario utilizzare FALSO come ultimo argomento.
• Per trovare una corrispondenza approssimativa, utilizza VERO come ultimo argomento oppure lascialo vuoto.

In questo esempio, utilizzo FALSO per ottenere una corrispondenza esatta. La formula ora appare così:

=VLOOKUP(G6,B6:E12,4,FALSE

Premi il tasto Invio per ottenere il risultato

Spiegando singolarmente ciascun argomento nell’esempio precedente, la sintassi e gli argomenti della funzione VLOOKUP risultano ora molto più chiari.

Sintassi e argomenti

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Valore_ricerca (obbligatorio): il valore (un numero reale o un riferimento di cella) che si desidera cercare. Ricorda che questo valore deve trovarsi nella prima colonna della matrice_tabella.
• Matrice_tabella (obbligatorio): un intervallo di celle che include sia la colonna del valore da cercare sia quella del valore restituito.
• Indice_col (obbligatorio): un numero intero che indica la colonna da cui restituire il valore. Il conteggio inizia da 1, corrispondente alla prima colonna a sinistra della matrice_tabella.
• Intervallo_ricerca(facoltativo): un valore logico che determina se si desidera che VLOOKUP trovi una corrispondenza approssimativa o esatta.
  • Corrispondenza approssimativa – Imposta questo argomento su VERO, 1 oppure lascialo vuoto.
    Importante: per trovare una corrispondenza approssimativa, i valori nella prima colonna del table_array devono essere ordinati in ordine crescente; altrimenti, VLOOKUP potrebbe restituire un risultato errato.
  • Corrispondenza esatta – Imposta questo parametro su FALSO oppure 0.

Esempi

Questa sezione offre alcuni esempi per aiutarti a comprendere meglio la funzione VLOOKUP.

Esempio 1: Corrispondenza esatta contro corrispondenza approssimativa in VLOOKUP

Se hai dubbi sulla differenza tra corrispondenza esatta e approssimativa quando utilizzi VLOOKUP, questa sezione ti aiuterà a chiarirli.

Corrispondenza esatta in VLOOKUP

In questo esempio, cerco i nomi corrispondenti in base ai punteggi elencati nell’intervallo E6:E8, quindi inserisco la seguente formula nella cella F6 e trascino il quadratino di riempimento automatico fino a F8. In questa formula, l’ultimo argomento è impostato su FALSO per eseguire una ricerca con corrispondenza esatta.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Tuttavia, poiché il punteggio 98 non esiste nella prima colonna della Intervallo dati, VLOOKUP restituisce un errore #N/D.

Corrispondenza approssimativa in VLOOKUP

Riprendendo l’esempio precedente, se modifichi l’ultimo argomento in VERO, VLOOKUP eseguirà una ricerca con corrispondenza approssimativa: qualora non trovi una corrispondenza esatta, individuerà il valore più grande immediatamente inferiore a quello cercato e restituirà il risultato corrispondente.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Poiché il punteggio 98 non esiste, VLOOKUP trova il valore più grande immediatamente inferiore a 98, ovvero 95, e restituisce il nome associato al punteggio 95 come risultato più vicino.

Esempio 2: Utilizza VLOOKUP con criteri multipli

Questa sezione mostra come utilizzare VLOOKUP con condizioni multiple in Excel. Come illustrato nello screenshot seguente, se devi trovare uno stipendio in base a un nome fornito (nella cella H5) e a un reparto (nella cella H6), segui i passaggi descritti di seguito per ottenere il risultato.

Passaggio 1: Aggiungi una colonna ausiliaria per unire i valori delle colonne di ricerca

In questo caso, dobbiamo creare una colonna ausiliaria per unire i valori della colonna Nome e della colonna Reparto.

1. Aggiungere una colonna di supporto a sinistra del proprio Intervallo dati e assegnarle un’intestazione. Vedere lo screenshot:
   
   
2. Nella colonna di supporto, seleziona la prima cella sotto l’intestazione, inserisci la seguente formula nella Barra delle formule e premi Invio.
   

   =C6&" "&D6

   
   Note: in questa formula viene utilizzato il simbolo «e commerciale» (&) per unire il testo di due colonne in un unico risultato.

   • C6 è il nome della colonna Nome da unire, D6 è il primo reparto della colonna Reparto da unire.
   • I valori di queste due celle vengono concatenati con uno spazio in mezzo.
3. Seleziona questa cella risultato, quindi trascina il quadratino di riempimento automatico verso il basso per applicare la formula alle altre celle della stessa colonna.
   
   

Passaggio 2: Applica la funzione VLOOKUP con i criteri specificati

Seleziona la cella in cui desideri visualizzare il risultato (qui ho scelto I7), inserisci la seguente formula nella Barra delle formule, quindi premi Invio.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Risultato

Errori comuni di VLOOKUP e relative soluzioni

Questa sezione presenta gli errori più comuni che potresti incontrare utilizzando VLOOKUP e le relative soluzioni per risolverli.

Restituzione dell’errore #N/D

L’errore più comune con VLOOKUP è #N/D, che indica che Excel non è riuscito a trovare il valore cercato. Di seguito sono riportati alcuni motivi per cui VLOOKUP potrebbe restituire questo errore.

Motivo 1: Il valore da cercare non si trova nella prima colonna della matrice_tabella

Uno dei limiti della funzione VLOOKUP di Excel è che permette di cercare solo Da sinistra a destra. Pertanto, il Intervallo di valore di ricerca deve trovarsi nella prima colonna della matrice_tabella.

Come mostrato nello screenshot seguente, voglio recuperare un nome in base al Titolo di lavoro specificato. In questo caso, il valore da cercare ()responsabile vendite) si trova nella seconda colonna della matrice_tabella, mentre il valore da restituire si trova a sinistra della colonna di ricerca; per questo motivo, VLOOKUP restituisce un errore #N/D.

Soluzioni

Puoi applicare una qualsiasi delle seguenti soluzioni per risolvere questo errore.

• Riorganizzare le colonne
  
  È possibile riorganizzare le colonne in modo da posizionare la colonna di ricerca come prima colonna della matrice_tabella.
• Utilizzare insieme le funzioni INDICE e CONFRONTA
  
  Qui utilizziamo in combinazione le funzioni INDICE e CONFRONTA come alternativa a VLOOKUP per risolvere questo problema.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Utilizzare la funzione CERCA.X (disponibile in Excel 365, Excel 2021 e versioni successive)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Motivo 2: Il valore da cercare non è presente nella colonna di ricerca (corrispondenza esatta)

Uno dei motivi più comuni per cui VLOOKUP restituisce un errore #N/D è che il valore cercato non è stato trovato.

Come mostrato nell’esempio seguente, cerchiamo il nome in base al punteggio specificato 98 in E6. Tuttavia, tale punteggio non esiste nella prima colonna della Intervallo dati, quindi VLOOKUP restituisce un errore #N/D.

Soluzioni

Per risolvere questo errore, puoi provare una delle seguenti soluzioni.

• Se si desidera che VLOOKUP trovi il valore più grande inferiore o uguale al valore cercato, modificare l’ultimo argomento FALSO (corrispondenza esatta) in VERO (corrispondenza approssimativa). Per ulteriori informazioni, vedere Esempio 1: corrispondenza esatta vs. approssimativa con VLOOKUP.
• Per evitare di modificare l’ultimo argomento e ricevere un promemoria nel caso in cui il valore di ricerca non venga trovato, è possibile racchiudere la funzione VLOOKUP all’interno della funzione SE.ERRORE:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Motivo 3: Il valore da cercare è inferiore al valore più piccolo nella colonna di ricerca (corrispondenza approssimativa)

Come illustrato nello screenshot seguente, stai eseguendo una ricerca con corrispondenza approssimativa. Poiché il valore cercato (in questo caso l’ID 1001) è inferiore al valore più piccolo presente nella colonna di ricerca (1002), VLOOKUP restituisce l’errore #N/D.

Soluzioni

Ecco due soluzioni pensate per te.

• Assicurarsi che il valore di ricerca Valore maggiore o uguale a sia il valore più piccolo nella colonna di ricerca.
• Se si desidera che Excel avvisi quando il valore di ricerca non viene trovato, è sufficiente annidare la funzione VLOOKUP nella funzione SE.ERRORE come segue:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Motivo 4: I numeri sono formattati come testo

Come puoi notare nello screenshot seguente, l’errore #N/D in questo esempio è causato da un’incompatibilità di formato tra la cella di ricerca (G6) e la colonna di ricerca (B6:B12) della tabella originale: il valore in G6 è un numero, mentre i valori nell’intervallo B6:B12 sono numeri formattati come testo.

Soluzioni

Per risolvere questo problema, devi convertire nuovamente il valore da cercare in numero. Ecco due metodi a tua disposizione.

• Applicare la funzione Converti in numero
  
  Fare clic sulla cella da convertire da Da testo a valore, selezionare questo pulsante accanto alla cella e quindi scegliere Converti in numero.
  
• Applicare uno strumento pratico per convertire in blocco Conversione tra testo e valore
  
  La funzione Conversione tra testo e valoredi Kutools per Excelconsente di convertire facilmente un intervallo di celle da testo a valore e viceversa.Prova subito gratuitamente per 30 giorni con tutte le funzionalità!

Motivo 5: la matrice_tabella non è costante quando si trascina la formula CERCA.VERT in altre celle

Come mostrato nello screenshot seguente, ci sono due Intervallo di valore di ricerca in E6 ed E7. Dopo aver ottenuto il primo risultato in F6, trascinando la formula CERCA.VERT dalla cella F6 a F7 viene restituito un errore #N/D. Ciò accade perché i riferimenti di cella (B6:C12) sono relativi per impostazione predefinita e vengono aggiornati spostandosi verso il basso nelle righe. La matrice tabella è stata spostata a B7:C13, che non contiene più il valore cercato 73.

Soluzione

Per mantenere la matrice tabella costante, devi bloccarla aggiungendo un $ davanti alle righe e alle colonne nei riferimenti di cella. Per saperne di più sui riferimenti assoluti in Excel, consulta questo tutorial: Riferimento assoluto in Excel (come creare e utilizzare).

Viene restituito l'errore #VALORE

Le seguenti condizioni possono causare la restituzione dell'errore #VALORE! da parte di CERCA.VERT.

Motivo 1: il valore cercato supera i 255 caratteri

Come mostrato nello screenshot seguente, il valore cercato nella cella H4 supera i 255 caratteri, quindi CERCA.VERT restituisce un errore #VALORE.

Soluzioni

Per aggirare questa limitazione, è possibile utilizzare una funzione di ricerca diversa in grado di gestire stringhe più lunghe. Provare una delle seguenti formule.

• INDICE e CONFRONTA:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Funzione CERCA.X(disponibile in Excel 365, Excel 2021 e versioni successive):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Motivo 2: l'argomento indice_colonna è inferiore a 1

L'indice di colonna specifica il numero della colonna nella matrice tabella che contiene il valore da restituire. Questo argomento deve essere un numero positivo corrispondente a una colonna valida nella matrice tabella.

Se si immette un indice di colonna inferiore a 1 (ovvero zero o negativo), CERCA.VERT non riuscirà a individuare la colonna nella matrice tabella.

Soluzione

Per risolvere questo problema, assicurati che l'argomento indice_colonna nella formula CERCA.VERT sia un numero positivo corrispondente a una colonna valida all'interno della matrice tabella.

Viene restituito l'errore #RIF

Questa sezione elenca un motivo per cui CERCA.VERT restituisce l'errore #RIF e fornisce soluzioni a questo problema.

Motivo: l'argomento indice_colonna è maggiore del numero di colonne

Come si può notare nello screenshot seguente, la matrice tabella ha solo 4 colonne. Tuttavia, l'indice di colonna specificato nella formula CERCA.VERT è 5, che è maggiore del numero di colonne presenti nella matrice tabella. Di conseguenza, CERCA.VERT non riesce a individuare la colonna e restituisce un errore #RIF.

Soluzioni

• Specificare un numero di colonna corretto
  Verificare che l’argomento indice_colonna nella formula VLOOKUP corrisponda a un numero valido per una colonna presente nella matrice tabella.
• Ottieni automaticamente il numero di colonna in base all’intestazione specificata
  Se la tabella contiene molte colonne, individuare manualmente l’indice corretto della colonna può diventare complicato. In questi casi, puoi annidare la funzione CONFRONTA all’interno di VLOOKUP per trovare automaticamente la posizione della colonna a partire dalla sua intestazione.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Nota: nella formula sopra, la funzione CONFRONTA(«Email»,B5:E5, 0) viene utilizzata per ottenere il numero di colonna corrispondente all’intestazione "Email" nell’intervallo B6:E12. Il risultato è 4, che viene usato come indice_colonna nella funzione VLOOKUP.

Viene restituito un valore errato

Se si riscontra che CERCA.VERT non restituisce il risultato corretto, ciò potrebbe essere causato dai seguenti motivi

Motivo 1: la colonna di ricerca non è ordinata in ordine crescente

Se si imposta l'ultimo argomento su VERO(oppure)lo si lascia vuoto) per una corrispondenza approssimativa e la colonna di ricerca non è ordinata in ordine crescente, il risultato potrebbe essere errato.

Soluzione

Ordinare la colonna di ricerca in ordine crescente può aiutare a risolvere questo problema. Per farlo, seguire i passaggi indicati di seguito:

1. Seleziona le celle dati nella colonna di ricerca, vai alla scheda Dati e fai clic su Ordina dal più piccolo al più grande nel gruppo Ordina e filtra.
2. Nella finestra di dialogo Avviso ordinamento, seleziona l’opzione Espandi selezione e fai clic su OK.

Motivo 2: è stata inserita o rimossa una colonna

Come mostrato nello screenshot seguente, il valore che originariamente si desiderava restituire si trovava nella quarta colonna della matrice tabella, quindi si è specificato il numero indice_colonna come 4. Poiché è stata inserita una nuova colonna, la colonna dei risultati diventa la quinta colonna della matrice tabella, causando la restituzione da parte di CERCA.VERT di un risultato proveniente da una colonna errata.

Soluzioni

Ecco due soluzioni per te.

• È possibile modificare manualmente il numero dell’indice di colonna in modo che corrisponda alla posizione del Colonna di restituzione. La formula qui dovrebbe essere modificata in:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Se si desidera sempre restituire il risultato da una determinata colonna, ad esempio la colonna Email in questo esempio, la seguente formula può aiutare a individuare automaticamente l’indice di colonna in base all’intestazione fornita, indipendentemente dall’inserimento o eliminazione di colonne dalla matrice tabella.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Altre note sulla funzione

• VLOOKUP cerca il valore solo da sinistra a destra.
  Il valore di ricerca deve trovarsi nella colonna più a sinistra, e il valore restituito deve essere situato in una qualsiasi colonna a destra della colonna di ricerca.
• Se si lascia vuoto l’ultimo argomento, VLOOKUP utilizza per impostazione predefinita la corrispondenza approssimativa.
• VLOOKUP esegue una ricerca senza distinzione tra maiuscole e minuscole.
• In caso di corrispondenze multiple, VLOOKUP restituisce solo la prima corrispondenza trovata nella matrice tabella, in base all’ordine delle righe nella matrice stessa.

Articoli correlati