La Funzione CERCA.VERT di Excel è un potente strumento che ti aiuta a cercare un valore specificato confrontando verticalmente la prima colonna di una tabella o un intervallo e quindi restituire un valore corrispondente da un'altra colonna nella stessa riga. Sebbene VLOOKUP sia incredibilmente utile, a volte può essere difficile da comprendere per i principianti. Questo tutorial ha lo scopo di aiutarti a padroneggiare VLOOKUP fornendo spiegazione passo passo degli argomenti, esempi utili o soluzioni agli errori comuni che potresti incontrare quando usi la funzione CERCA.VERT.

• Spiegazione passo passo degli argomenti
• Sintassi e argomenti
• CERCA.VERT Esempi
  Corrispondenza esatta vs. corrispondenza approssimativa   |   Vlookup con condizioni multiple
• VLOOKUP Errori comuni e soluzioni
  #N/A errore   |   #VALORE errore   |   Errore #REF   |   Valore errato

Esempi di VLOOKUP .xlsx Errori VLOOKUP .xlsx

Video correlati

Spiegazione passo passo degli argomenti

Come mostrato nello screenshot sopra, la funzione VLOOKUP viene utilizzata per trovare un'e-mail basata su un determinato numero ID. Ora fornirò una spiegazione dettagliata di come utilizzare CERCA.VERT in questo esempio suddividendo ogni argomento passo dopo passo.

Passaggio 1: avviare la funzione CERCA.VERT

Selezionare una cella (H6 in questo caso) per produrre il risultato, quindi avviare la funzione CERCA.VERT digitando il seguente contenuto nel campo Barra della formula.

=VLOOKUP(

Passaggio 2: specificare il valore di ricerca

Innanzitutto, specifica il valore di ricerca (che è quello che stai cercando) nella funzione CERCA.VERT. Qui, faccio riferimento alla cella G6 che contiene un certo numero ID 1005.

=VLOOKUP(G6

Passaggio 3: specificare l'array della tabella

Successivamente, specifica un intervallo di celle contenente sia il valore che stai cercando sia il valore che desideri restituire. In questo caso, seleziono l'intervallo B6:E12. La formula ora appare come segue:

=VLOOKUP(G6,B6:E12

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

Passaggio 4: specificare la colonna da cui si desidera restituire un valore

Quindi specificare la colonna da cui si desidera restituire un valore.

In questo esempio, poiché devo restituire l'e-mail in base a un numero ID, qui inserisco un numero 4 per dire a VLOOKUP di restituire un valore dalla quarta colonna dell'intervallo di dati.

=VLOOKUP(G6,B6:E12,4

Passaggio 5: trova una corrispondenza approssimativa o esatta

Infine, determina se stai cercando una corrispondenza approssimativa o una corrispondenza esatta.

• Per trovare un corrispondenza esatta, devi usare FALSO come ultimo argomento.
• Per trovare un corrispondenza approssimativa, Utilizzare TRUE come ultimo argomento o lasciarlo vuoto.

In questo esempio, utilizzo FALSE per la corrispondenza esatta. La formula ora è così:

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

Premere il tasto Invio per ottenere il risultato

Spiegando ogni argomento uno per uno nell'esempio sopra, la sintassi e gli argomenti della funzione VLOOKUP sono ora molto più facili da capire.

Sintassi e argomenti

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

• Valore di ricerca (obbligatorio): il valore (un valore reale o un riferimento di cella) che stai cercando. Ricorda che questo valore deve trovarsi nella prima colonna di table_array.
• Matrice di tabella (obbligatorio): un intervallo di celle contiene sia la colonna del valore di ricerca che la colonna del valore restituito.
• col_indice (obbligatorio): un numero intero rappresenta il numero di colonna che contiene il valore restituito. Inizia con il numero 1 per la colonna più a sinistra di table_array.
• Ricerca_intervallo (facoltativo): un valore logico che determina se si desidera che VLOOKUP trovi una corrispondenza approssimativa o una corrispondenza esatta.
  • Corrispondenza approssimativa - Impostare questo argomento su TRUE, 1 o lascialo vuoto.
    Consigli : Per trovare una corrispondenza approssimativa, i valori nella prima colonna di table_array devono essere ordinati in ordine crescente nel caso in cui VLOOKUP restituisca il risultato errato.
  • Corrispondenza esatta - Impostare questo argomento su FALSO or 0.

Esempi

Questa sezione mostra alcuni esempi per aiutarti ad avere una comprensione più completa della funzione CERCA.VERT.

Esempio 1: corrispondenza esatta vs. corrispondenza approssimativa in CERCA.VERT

Se sei confuso sulla corrispondenza esatta e sulla corrispondenza approssimativa quando usi VLOOKUP, questa sezione può aiutarti a chiarire questa confusione.

Corrispondenza esatta in VLOOKUP

In questo esempio, troverò i nomi corrispondenti in base ai punteggi elencati nell'intervallo E6:E8, quindi inserisco la seguente formula nella cella F6 e trascino la maniglia di Compilazione automatica fino a F8. In questa formula, l'ultimo argomento è specificato come FALSO per eseguire una ricerca della corrispondenza esatta.

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

Tuttavia, poiché il punteggio 98 non esiste nella prima colonna dell'intervallo di dati, CERCA.VERT restituisce un risultato di errore #N/D.

Corrispondenza approssimativa in CERCA.VERT

Sempre usando l'esempio precedente, se cambi l'ultimo argomento in TRUE, CERCA.VERT eseguirà una ricerca di corrispondenza approssimativa. Se non viene trovata alcuna corrispondenza, troverà il successivo valore più grande che è inferiore al valore di ricerca e restituirà il risultato corrispondente.

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

Poiché il punteggio 98 non esiste, CERCA.VERT trova il successivo valore più grande inferiore a 98, ovvero 95, e restituisce il nome del punteggio 95 come risultato più vicino.

Esempio 2: utilizzare CERCA.VERT con più criteri

Questa sezione illustra come utilizzare CERCA.VERT con più condizioni in Excel. Come mostrato nello screenshot qui sotto, se stai cercando di individuare uno stipendio in base a un nome fornito (nella cella H5) e al dipartimento (nella cella H6), segui i passaggi seguenti per farlo.

Passaggio 1: aggiungi una colonna helper per concatenare i valori dalle colonne di ricerca

In questo caso, dobbiamo creare una colonna helper per concatenare i valori dal file Nome colonna e il Shirts Department colonna.

1. Aggiungi una colonna helper a sinistra dell'intervallo di dati e dai un'intestazione a questa colonna. Vedi schermata:
   
   
2. In questa colonna helper, seleziona la prima cella sotto l'intestazione, inserisci la seguente formula nel file Barra della formulae premere Enter.
   

   =C6&" "&D6

   
   Note: In questa formula, usiamo una e commerciale (&) per unire il testo in due colonne per produrre un singolo pezzo di testo.

   • C6 è il primo nome del Nome colonna da unire, D6 è il primo dipartimento del Shirts Department colonna da unire.
   • I valori di queste due celle sono concatenati con uno spazio intermedio.
3. Seleziona questa cella del risultato, quindi trascina il file Maniglia di riempimento automatico verso il basso per applicare questa formula ad altre celle nella stessa colonna.
   
   

Passaggio 2: applicare la funzione CERCA.VERT con i criteri indicati

Seleziona una cella in cui desideri visualizzare il risultato (qui seleziono I7), inserisci la seguente formula nel file Barra della formulae quindi premere Enter.

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

Risultato

Errori e soluzioni VLOOKUP comuni

Questa sezione elenca gli errori comuni che potresti riscontrare durante l'utilizzo di CERCA.VERT e fornisce le soluzioni per correggerli.

#N/D errore restituito

L'errore più comune con VLOOKUP è l'errore #N/D, il che significa che Excel non è riuscito a trovare il valore che stavi cercando. Ecco alcuni motivi per cui CERCA.VERT potrebbe restituire un errore #N/D.

Motivo 1: il valore di ricerca non si trova nella prima colonna di table_array

Uno dei limiti di Excel VLOOKUP è che ti consente solo di guardare da sinistra a destra. Quindi, i valori di ricerca devono trovarsi nella prima colonna di table_array.

Come mostrato nello screenshot qui sotto, voglio restituire un nome basato sul titolo di lavoro dato. Qui il valore di ricerca (direttore vendite) si trova nella seconda colonna di table_array e il valore restituito si trova a sinistra della colonna di ricerca, quindi VLOOKUP restituisce l'errore #N/D.

Soluzioni

È possibile applicare una delle seguenti soluzioni per correggere questo errore.

• Riordina le colonne
  
  È possibile riorganizzare le colonne per posizionare la colonna di ricerca nella prima colonna di table_array.
• Utilizzare insieme le funzioni INDICE e CONFRONTA
  
  Qui usiamo le funzioni INDICE e CONFRONTA insieme come alternativa a CERCA.VERT 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 di ricerca non viene trovato nella colonna di ricerca (corrispondenza esatta)

Uno dei motivi più comuni per cui VLOOKUP restituisce l'errore #N/D è perché il valore che stai cercando non è stato trovato.

Come mostrato nell'esempio seguente, troveremo il nome in base al punteggio dato di 98 in E6. Tuttavia, questo punteggio non esiste nella prima colonna dell'intervallo di dati, quindi CERCA.VERT restituisce un risultato di errore #N/D.

Soluzioni

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

• Se vuoi che VLOOKUP cerchi il successivo valore più grande che è minore del valore di ricerca, cambia l'ultimo argomento FALSO (corrispondenza esatta) a TRUE (corrispondenza approssimativa). Per ulteriori informazioni, vedere Esempio 1: corrispondenza esatta vs. corrispondenza approssimativa utilizzando CERCA.VERT.
• Per evitare di modificare l'ultimo argomento e ricevere un promemoria nel caso in cui il valore di ricerca non venga trovato, è possibile includere la funzione VLOOKUP all'interno della funzione IFERROR:
  

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

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

Come mostrato nello screenshot qui sotto, stai eseguendo una ricerca di corrispondenza approssimativa. Il valore che stai cercando (il numero ID 1001 in questo caso) è inferiore al valore più piccolo 1002 nella colonna di ricerca, pertanto CERCA.VERT restituisce un errore #N/D.

Soluzioni

Ecco due soluzioni per te.

• Assicurarsi che il valore di ricerca sia maggiore o uguale al valore più piccolo nella colonna di ricerca.
• Se vuoi che Excel ti ricordi che il valore di ricerca non è stato trovato, nidifica semplicemente la funzione CERCA.VERT 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 vedere nello screenshot qui sotto, il risultato dell'errore #N/D in questo esempio è dovuto a una mancata corrispondenza del tipo di dati tra la cella di ricerca (G6) e la colonna di ricerca (B6:B12) della tabella originale. Qui il valore in G6 è un numero ei valori nell'intervallo B6:B12 sono numeri formattati come testo.

Soluzioni

Per risolvere questo problema, è necessario riconvertire il valore di ricerca in numero. Ecco due metodi per te.

• Applicare la funzione Converti in numero
  
  Fare clic sulla cella in cui si desidera convertire il testo in numero, selezionare questo pulsante  accanto alla cella e quindi selezionare Converti in numero.
  
• Applica uno strumento utile per convertire in batch tra testo e numero
  
  La Converti tra testo e numero caratteristica di Kutools for Excel ti aiuta a convertire facilmente un intervallo di celle da testo a numero e viceversa. Ottieni subito una prova gratuita completa di 30 giorni!
  

Motivo 5: table_array non è costante quando si trascina la formula VLOOKUP su altre celle

Come mostrato nello screenshot qui sotto, ci sono due valori di ricerca in E6 ed E7. Dopo aver ottenuto il primo risultato in F6, trascina la formula CERCA.VERT dalla cella F6 a F7, viene restituito un risultato di errore #N/D. Questo perché i riferimenti di cella (B6:C12) sono relativi per impostazione predefinita e vengono regolati mentre ti sposti tra le righe. L'array della tabella è stato spostato in B7:C13, che non contiene più il punteggio di ricerca 73.

Soluzione

Devi bloccare l'array della tabella per mantenerlo costante aggiungendo a $ segno prima delle righe e delle colonne nei riferimenti di cella. Per saperne di più sul riferimento assoluto in Excel, dai un'occhiata a questo tutorial: Riferimento assoluto di Excel (come creare e utilizzare).

#VALUE errore restituito

Le seguenti condizioni possono far sì che CERCA.VERT restituisca un risultato di errore #VALORE.

Motivo 1: il valore di ricerca supera i 255 caratteri

Come mostrato nello screenshot qui sotto, il valore di ricerca nella cella H4 supera i 255 caratteri, quindi CERCA.VERT restituisce un risultato di errore #VALORE.

Soluzioni

Per aggirare questa limitazione, puoi applicare una funzione di ricerca diversa in grado di gestire stringhe più lunghe. Prova una delle seguenti formule.

• INDICE e MATCH:
  

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

  
• Funzione XLOOKUP (disponibile in Excel 365, Excel 2021 e versioni successive):
  

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

Motivo 2: l'argomento col_index è minore di 1

L'indice di colonna specifica il numero di colonna nell'array della tabella che contiene il valore che si desidera restituire. Questo argomento deve essere un numero positivo che corrisponde a una colonna valida nell'array della tabella.

Se inserisci un indice di colonna inferiore a 1 (ovvero zero o negativo), CERCA.VERT non sarà in grado di individuare la colonna nell'array della tabella.

Soluzione

Per risolvere questo problema, assicurati che l'argomento dell'indice di colonna nella formula CERCA.VERT sia un numero positivo che corrisponda a una colonna valida nell'array della tabella.

Errore #REF restituito

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

Motivo: l'argomento col_index è maggiore del numero delle colonne

Come puoi vedere nello screenshot qui sotto, l'array table ha solo 4 colonne. Tuttavia, l'indice di colonna specificato nella formula CERCA.VERT è 5, che è maggiore del numero di colonne nell'array della tabella. Di conseguenza, VLOOKUP non sarà in grado di individuare la colonna e alla fine restituirà un errore #REF.

Soluzioni

• Specificare un numero di colonna corretto
  Assicurati che l'argomento dell'indice di colonna nella formula CERCA.VERT sia un numero che corrisponde a una colonna valida nell'array della tabella.
• Ottieni automaticamente il numero di colonna in base all'intestazione di colonna specificata
  Se la tabella contiene molte colonne, potresti avere difficoltà a determinare il numero di indice di colonna corretto. Qui puoi nidificare la funzione CONFRONTA nella funzione CERCA.VERT per trovare la posizione della colonna in base a un'intestazione di colonna certa.

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

  Note:: Nella formula precedente, il CONFRONTA("E-mail",B5:E5, 0) la funzione viene utilizzata per ottenere il numero di colonna del "Email" colonna nell'intervallo di date B6:E12. Qui il risultato è 4, che viene utilizzato come col_index nella funzione CERCA.VERT.

Valore errato restituito

Se ritieni che CERCA.VERT non restituisca il risultato corretto, ciò potrebbe essere causato dai seguenti motivi

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

Se hai impostato l'ultimo argomento su TRUE (o lasciato vuoto) per una corrispondenza approssimativa e la colonna di ricerca non è ordinata in ordine crescente, il valore risultante potrebbe non essere corretto.

Soluzione

Ordinare la colonna di ricerca in ordine crescente può aiutarti a risolvere questo problema. Per fare ciò, segui i passaggi seguenti:

1. Seleziona le celle di dati nella colonna di ricerca, vai a Dati scheda, fare clic Ordina dal più piccolo al più grande nel Ordina e filtra gruppo.
2. Nel Ordina avviso finestra di dialogo, selezionare il Espandi la selezione opzione e fare clic OK.

Motivo 2: una colonna viene inserita o rimossa

Come mostrato nello screenshot qui sotto, il valore che originariamente volevo restituire è nella quarta colonna dell'array table, quindi specifico il numero col_index come 4. Quando viene inserita una nuova colonna, la colonna del risultato diventa la quinta colonna della tabella matrice, facendo sì che VLOOKUP restituisca il risultato da una colonna errata.

Soluzioni

Ecco due soluzioni per te.

• È possibile modificare manualmente il numero di indice della colonna in modo che corrisponda alla posizione della colonna restituita. La formula qui dovrebbe essere modificata in:

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

• Se vuoi sempre restituire il risultato da una colonna certian, come la colonna Email in questo esempio. La seguente formula può aiutare a far corrispondere automaticamente l'indice di colonna in base all'intestazione di colonna specificata, indipendentemente dal fatto che le colonne siano inserite o rimosse dall'array della tabella.

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

Altre note di funzione

• VLOOKUP cerca solo il valore da sinistra a destra.
  Il valore di ricerca si trova nella colonna più a sinistra e il valore del risultato deve trovarsi in qualsiasi colonna a destra della colonna di ricerca.
• Se lasci vuoto l'ultimo argomento, CERCA.VERT usa la corrispondenza approssimativa per impostazione predefinita.
• CERCA.VERT esegue una ricerca senza distinzione tra maiuscole e minuscole.
• Per più corrispondenze, CERCA.VERT restituisce solo la prima corrispondenza trovata nell'array della tabella, in base all'ordine delle righe nell'array della tabella.

Articoli Correlati