La Funzione Cerca Verticale, spiegazione ed esempi di applicazione

La Funzione Cerca Verticale  di Excel  è una delle Funzioni più utilizzate per trovare e restituire dei valori presenti in una tabella.
La Funzione cerca verticale permette di cercare un valore nella prima colonna di una matrice tabella e restituire i successivi valori presenti nella stessa riga.
Cerca Verticale (CERCA.VERT) è sicuramente una delle Funzioni più importanti e maggiormente applicate in Excel e appartiene alla categoria delle Funzioni di Ricerca e riferimento.

CERCA.VERT è particolarmente utile per gestire e consultare tabelle di dati nelle quali si vuole trovare le informazioni associate a un identificativo, come ad esempio un codice prodotto o cliente.
CERCA.VERT è pertanto uno strumento essenziale per l'analisi dei dati, rendendo più efficienti operazioni come l'estrazione di prezzi, categorie, e dettagli di prodotto in base a codici identificativi univoci o altri riferimenti. La Funzione semplifica notevolmente la gestione di database e cataloghi, automatizzando la ricerca e la visualizzazione di informazioni, evitando l'inserimento manuale dei dati e riducendo pertanto il rischio di errori.

::Suggerimento. In Excel 365 ed Excel 2021, è possibile utilizzare la nuova Funzione CERCA.X Funzione di ricerca più flessibile e potente del CERCA.VERT
Per info si rimanda alla sezione: La nuova Funzione CERCA.X

La Funzione CERCA.VERT

[01] - La Funzione Cerca Verticale - elementi introduttivi
[02] - La sintassi della Funzione Cerca Verticale - CERCA.VERT
[03] - La Funzione CERCA.VERT - esempio di applicazione
[04] - Il codice di errore Dato non disponibile N/D
[05] - La Funzione CERCA.VERT - esempio di ricerca con risultato per approssimazione
[06] - Nidificare la Funzione CONFRONTA con CERCA.VERT per rendere dinamica l'impostazione dell'indice
[07] - Semplificare la Funzione attribuendo alla matrice un nome definito (intervallo denominato) - esempio di ricerca
[08] - CERCA.VERT: 6 Cose Essenziali da Ricordare!
[09] - La Funzione Cerca Verticale spiegata con un video  
[10] - Impostare Argomenti Funzione | La Finestra di dialogo della Funzione CERCA.VERT
[11] - Considerazioni finali e osservazioni su alcuni limiti della Funzione CERCA.VERT

Nota. Gli esempi riportati nel tutorial sono applicati alla Versione Excel 365 (del 2020), ma sono ugualmente riferibili anche alle versioni Excel 2007-2019

La Funzione Cerca Verticale

Cerca Verticale cerca un valore nella prima colonna di una tabella-matrice e restituisce il contenuto delle celle presenti a destra sulla stessa riga.
Cerchiamo di semplificare questa Funzione di Excel definendo in primo luogo che cos'è una matrice-tabella.
La matrice non è altro che una tabella che Excel trattata come un'unica identità (senza interruzioni di riga o colonna e senza intestazione).
All'interno di questa Matrice CERCA.VERT può ricercare i valori solo sulla prima colonna, e restituire i dati che sono presenti sulla stessa riga.
:: IMPORTANTE. Una volta trovato un valore nella prima colonna della matrice, CERCA VERTICALE può restituire i dati della stessa riga solo a destra della cella trovata. Va pertanto tenuto presente che il valore da ricercare deve trovarsi sempre nella prima colonna dell'intervallo.
Ad esempio, se il valore di ricerca fosse nella colonna C del foglio di lavoro, (vedi immagine sottostante) la matrice dovrà iniziare con C come prima colonna escludendo la colonna B dove si trova il Codice. Ovviamente in questo caso non sarebbe possibile avere la restituzione dei dai dati presenti in colonna 1.

La sintassi della Funzione Cerca Verticale: CERCA.VERT

La sintassi della Funzione Cerca verticale (VLOOKUP nella versione originale del programma) è la seguente: 
=CERCA.VERT(valore;tabella_matrice;indice;[intervallo])
Nella quale:
valore: è il dato che deve essere ricercato nella matrice.
tabella_matrice: è l'area del Foglio di lavoro che contiene i dati (entro i quali ricercare il valore).
indice: è il riferimento al numero della colonna della quale si richiede la restituzione del valore. Esempio: Indice uguale a 2 restituisce il valore presente nella seconda colonna della tabella_matrice, indice uguale a 3 restituisce il valore della terza colonna  e così via.
intervallo: argomento opzionale. Per questo nella sintassi viene indicato fra parentesi quadrata.
Si tratta di un valore logico che consente di specificare se CERCA.VERT deve trovare una corrispondenza esatta o per approssimazione. L'intervallo potrà pertanto essere VERO (ricerca per approssimazione) o FALSO (ricerca esatta). Se nell'impostazione della Funzione l'argomento viene omesso Excel presuppone che esso sia impostato come VERO.
:: NOTA. In alternativa a VERO o FALSO è possibile inserire rispettivamente 1 (uno) oppure 0 (zero)
L'argomento intervallo della Funzione cerca verticale seppur opzionale è molto importante.
Per chiarire la corretta impostazione di questo argomento nella successiva parte del tutorial verranno presentati alcuni esempi che spiegheranno come inserire correttamente una ricerca per corrispondenza esatta o per approssimazione.

La Funzione CERCA.VERT - esempio di applicazione

Nella tabella riportata nell'immagine sottostante, si desidera utilizzare la Funzione Cerca verticale per individuare i valori corrispondenti alla Categoria prodotto e al Prezzo unitario associati a uno specifico codice prodotto. Nell'esempio mostrato, il codice "5" è inserito nella cella B13.

 

Ripartiamo dalla sintassi della Funzione applicandola all'esempio illustrato nell'immagine. =CERCA.VERT(valore;tabella_matrice;indice;[intervallo])
La Funzione inserita nella cella C13 è la seguente:
=CERCA.VERT(B13;$B$3:$D$11;2;falso)
valore; B13 è il valore che si vuole cercare nella prima colonna della matrice. In questo caso, il valore inserito nella cella B13 è "5", e rappresenta il codice di un prodotto.
tabella_matrice; è la matrice o intervallo di celle in cui si vuole cercare il valore. La matrice comprende le celle da B3 a D11, che includono i dati di Codice, Categoria prodotto, e Prezzo unitario. Nella Funzione la matrice è stata bloccata con i riferimenti assoluti $B$3:$D$11
Indice; Gli indici corrispondono alle colonne della matrice. Quando la Funzione Cerca verticale trova il codice di riferimento nella prima colonna della matrice, restituirà il valore della colonna specificata dall'indice. Se l'indice è "2", la Funzione restituirà la Categoria prodotto; se l'indice fosse "3", verrebbe invece restituito il Prezzo unitario.
Intervallo; L'intervallo è un valore logico pertanto può essere VERO o FALSO. Se nella Funzione viene omesso Excel presuppone che sia VERO!
Nel caso dell'esempio proposto è fondamentale che vi sia corrispondenza esatta, quindi andrà definito come FALSO.
Pertanto la Funzione sarà la seguente.

Risultato della formula

La formula restituisce il valore corrispondente nella seconda colonna della riga in cui si trova il codice "5", cioè "Giacche Snowboard K2".
Nella cella D13 è stata imputata, una Funzione molto simile
=CERCA.VERT(B13; B3:D11; 3; FALSO)
In questo caso  viene utilizzata per ottenere il valore della colonna Prezzo unitario per il codice "5", che è "€ 158,50".

Dato non disponibile #N/D

Nella Funzione CERCA.VERT, se il valore da ricercare non è presente nella prima colonna della matrice di dati (e l'argomento intervallo è impostato su FALSO), la Funzione restituisce il codice di errore #N/D.
Questo codice di errore indica che il valore non è disponibile, ovvero che CERCA.VERT non è riuscita a trovare il valore richiesto nella colonna di ricerca.

Il codice di errore #N/D viene restituito anche anche se la cella contenente il valore da ricercare è vuota.
In questo caso, CERCA.VERT non riesce a trovare una corrispondenza e restituisce comunque il codice #N/D. Questo accade perché la Funzione cerca una corrispondenza esatta, e una cella vuota non rappresenta un valore valido per la ricerca.

Nella tabella raffigurata nel sottostante screenshot, è stata impostata la ricerca del codice 10 che non è nell'elenco dei codici disponibili. in questo caso la Funzione CERCA.VERT restituisce il codice di errore #N/D

::ATTENZIONE: la presenza del codice di errore #/ND, può diventare un problema per eventuali operazioni di calcolo.
Per la gestione del codice di Errore #N/D si rimanda alla specifica sezione: Gestire il messaggio di errore #N/D

CERCA.VERT: Esempio di una ricerca con risultato per approssimazione

Se il quarto argomento della Funzione CERCA.VERT non viene definito oppure viene impostato: VERO, e nella prima colonna della matrice non fosse presente una corrispondenza esatta del valore ricercato il risultato restituito dalla ricerca sarebbe per corrispondenza approssimativa.
Nella ricerca per approssimazione Cerca verticale restituisce come risultato il valore più vicino (minore) al codice ricercato.
E' molto importante porre attenzione a questo argomento!
Per meglio spiegare il funzionamento di una ricerca per approssimazione si propone, nella successiva immagine, l'esempio di una serie di valori relativi ad una analisi e un commento di essi descritto nelle seconda colonna.
L'obiettivo è quello di cercare un risultato nella prima colonna per avere la restituzione del commento presente in colonna (indice) 2.
Mentre i valori contenuti nella prima colonna della matrice sono dei numeri interi i valori da ricercare possono avere una parte decimale. Nell'esempio il valore inserito nella cella E3 è uguale a 6,3.
Nella Cella F3 è stata inserita la Funzione:> =CERCA.VERT(E3;B3:C9;2;VERO)
Il valore che viene restituito dalla Funzione Cerca verticale è quello più vicino (minore) ossia il risultato riferito al valore 6 = MOLTO BUONO

::Osservazione. Se fosse stata impostata una ricerca esatta (quarto argomento FALSO oppure 0 "zero") il risultato restituito sarebbe  stato #N/D

Nidificare la Funzione CONFRONTA con CERCA.VERT per rendere dinamica l'impostazione dell'indice

Nidificare la Funzione CONFRONTA all'interno della Funzione CERCA.VERT consente di rendere dinamico il terzo argomento del cerca verticale che indica il numero di colonna da cui estrarre il valore.
Normalmente, CERCA.VERT richiede un numero fisso per sapere da quale colonna prelevare i dati. Tuttavia, quando si desidera che la colonna da cercare cambi in base a un valore variabile (come il nome di un prodotto o un'agenzia), la Funzione CONFRONTA viene utilizzata per identificare automaticamente la posizione di quella colonna.

Nell'immagine sottostante viene proposta una tabella di valori numerici, organizzati in colonne e righe.
Le colonne (da C a H) rappresentano agenzie denominate “alfa”, “beta”, “gamma”, “delta”, “epsilon” e “omega”. Le righe (da 3 a 14) indicano i mesi dell'anno, da gennaio a dicembre.

La cella evidenziata, L2, contiene la seguente formula di Excel:
=CERCA.VERT(J2;B3:H14;CONFRONTA(K2;B2:H2;0);0)

Dettaglio della Formula =CERCA.VERT(J2;B3:H14;CONFRONTA(K2;B2:H2;0);0)
CERCA.VERT cerca un valore nella prima colonna della tabella matrice specificata.
J2: Rappresenta il valore di ricerca nella prima colonna. In questo caso, il mese indicato è “maggio”, quindi Excel cerca la riga relativa a maggio nella prima colonna dell'intervallo.
B3:H14 rappresenta l'intervallo della matrice tabella.
CONFRONTA(K2;B2:H2;0): Questa sintassi determina quale colonna specifica deve essere utilizzata nell'indice del CERCA.VERT.
K2 contiene il nome dell'agenzia o del prodotto da cercare (“epsilon” in questo caso).
lala Funzione CONFRONTA cerca la posizione di “epsilon” nella riga di intestazione (B2:H2), restituendo la posizione relativa della colonna corrispondente. ovviamente la posizione restituita da CONFRONTA, rappresenta l'argomento indice del CERCA.VERT. l'ultimo argomento 0 della Funzione CONFRONTA indica la ricerca esatta.

L'ultimo argomento di CERCA.VERT impostato come 0 (zero) indica la ricerca esatta.
Il risultato della formula rappresenta il risultato di vendita dell’agenzia "epsilon" per il mese di "maggio", visualizzando il risultato nella cella L2.

Qualora si scegliesse di cercare il risultato di un'altra agenzia, sarà automaticamente la Funzione CONFRONTA a modificare il riferimento dell'indice. 

Semplificare la Funzione attribuendo alla matrice un nome definito (etichetta)

Per semplificare la sintassi della Funzione è consigliabile identificare l'area della matrice (nella quale ricercare le informazioni) attribuendole un nome definito (etichetta).
Costruire una etichetta: una volta selezionata la matrice nella casella nome (presente a sinistra della barra della formula) digitare il nome dell'etichetta (nel caso dell'esempio: listino) e successivamente confermare l'inserimento premendo invio. Questa operazione equivale al comando Definisci nome presente nella scheda Formule della Barra multiFunzione.

 

Il risultato del'aver creato un intervallo denominato (etichetta) per l'area dati della matrice, è che ora nella sintassi anzichè fare riferimento alla matrice attraverso la definizione di una area sarà possibile utilizzare il nome dell'etichetta. (vedi immagine sottostante)
Anzichè descrivere la Funzione =CERCA.VERT(B15;B3:D11;2;FALSO) sarà possibile scrivere =CERCA.VERT(B15;listino;2;falso)

::Consiglio. Nell'argomento matrice di un Funzione è buona regola utilizzare sempre o un nome definito oppure i riferimenti di cella assoluti. In questo modo l’intervallo di ricerca rimane sempre uguale qualora si trascinasse la Funzione nelle celle sottostanti.
:: Per un approfondimento su come creare un intervallo denominato (etichetta) si rimanda al tutorial: Effettuare ricerche dati con gli Intervalli denominati (Nomi definiti)

CERCA.VERT: 6 Cose Essenziali da Ricordare!

La Funzione CERCA.VERT (VLOOKUP) di Excel è molto utile, ma per sfruttarla al meglio è importante conoscere alcuni dettagli fondamentali. Ecco i sei aspetti chiave da considerare:
1- Ricerca solo a destra: CERCA.VERT può cercare solo nella colonna più a sinistra della matrice e restituisce valori dalle colonne a destra. Se si necessita di una ricerca verso sinistra, è consigliato utilizzare la combinazione delle Funzioni INDICE e CONFRONTA (INDEX e MATCH) oppure INDICE e XMATCH in Excel 365, che consente di ignorare la posizione delle colonne di ricerca e di ritorno.
2- No è case sensitive (ovvero non c'è distinzione tra maiuscole e minuscole): CERCA.VERT non distingue tra maiuscole e minuscole nei valori. Per eseguire ricerche che distinguano tra caratteri maiuscoli e minuscoli, è necessario utilizzare formule apposite per una ricerca case-sensitive.
3- Parametro di corrispondenza: Il quarto parametro di CERCA.VERT è fondamentale per determinare la modalità di corrispondenza. Impostando questo parametro su FALSO si ottiene una corrispondenza esatta, mentre con VERO si ottiene una corrispondenza approssimativa. In caso di dubbio, consultare la guida specifica su VERO vs. FALSO per il comportamento di CERCA.VERT. Ordinamento per corrispondenze approssimative: Quando si utilizza CERCA.VERT per una corrispondenza approssimativa, è importante che i dati della colonna di ricerca siano ordinati in ordine crescente. In caso contrario, la Funzione potrebbe restituire risultati inaspettati. Errore #N/D: Se il valore cercato non è presente nella colonna di ricerca, CERCA.VERT restituirà un errore #N/D. Questo errore indica che la Funzione non è riuscita a trovare una corrispondenza. Per informazioni su altri possibili errori e relative soluzioni, consultare la guida su come risolvere gli errori di CERCA.VERT. Coerenza di formattazione: È essenziale che il tipo di dati cercato sia coerente con quello dei dati presenti nella tabella. Ad esempio, se nella tabella è presente una stringa numerica formattata come testo, una ricerca con un numero potrebbe non trovare il valore. Assicurarsi sempre che i formati siano coerenti tra il valore cercato e quelli presenti nella tabella. Questi punti sono cruciali per utilizzare CERCA.VERT in modo efficace e prevenire errori. Conoscere queste caratteristiche permette di sfruttare al massimo questa Funzione nelle analisi dei dati di Exc

La Funzione Cerca Verticale spiegata con un video  

Approfondimento: Impostare Argomenti Funzione | La Finestra di dialogo della Funzione CERCA.VERT

Se imputare la Funzione Cerca Verticale può sembrare troppo complicato, rimane sempre la possibilità di utilizzare la Finestra di dialogo Argomenti Funzione.
Per accedervi è sufficiente cliccare sul pulsante Fx della Barra della Formula e selezionare la Funzione CERCA.VERT fra le Funzioni Ricerca e Riferimento o fra le Funzioni recenti.
Una volta individuata e selezionata la Funzione CERCA.VERT si aprirà la finestra di dialogo Argomenti Funzione.
Nella finestra di dialogo Argomenti Funzione, è sufficiente compilare i campi relativi al Valore; Matrice_tabella; Indice e Intervallo, e automaticamente nella Barra della Formula si comporrà la Funzione CERCA.VERT nella sua corretta sintassi.

Nella finestra di dialogo Argomenti Funzione si devono solo inserire i valori nei campi corrispondenti. Compilare i dati nella finestra di dialogo equivale pertanto a scrivere la corretta sintassi della Funzione =CERCA.VERT(B15;listino;2;falso)

Considerazioni finali e osservazioni su alcuni limiti della Funzione CERCA.VERT

Sicuramente CERCA.VERT è la Funzione di Ricerca e Riferimento maggiormente utilizzata in Excel. Si tratta di una Funzione non semplicissima da applicare e frequentemente viene impostata in maniera errata soprattutto nel quarto argomento (VERO-FALSO).
Oltre a queste difficoltà interpretative la Funzione Cerca Verticale ha una serie di limiti.
Il primo è che necessita che il valore da ricercare sia nella prima colonna della matrice. Qualora questa impostazione non fosse possibile bisogna utilizzare in maniera combinata le due Funzioni INDICE e CONFRONTA, oppure, se si dispone di una versione Microsoft 365 della nuova e più completa Funzione CERCA.X
Il secondo limite del CERCA.VERT è che richiede che i valori nella prima colonna siano univoci. Se ci fossero due codici uguali Cerca verticale quando incontra il primo codice che soddisfa la ricerca si ferma e restituisce il valore di colonna richiesto. Il programma non restituisce errori che avvertano che esiste un secondo valore che potrebbe soddisfare la ricerca.
Con il CERCA.VERT è pertanto possibile effettuare ricerche su valori (normalmente codici) fra i quali non siano presenti valori doppi (omocodie). Sarebbe impensabile applicare il Cerca verticale su un cognome all'interno di una anagrafica poichè non sarebbe così remota la possibilità che vi siano più persone con lo stesso cognome.
Un terzo limite del CERCA.VERT è nelle ricerche per approssimazioni, nelle quali la Funzione restituisce sempre e solo il valore più piccolo prossimo al valore di ricerca.
Alcuni di questi limiti sono risolvibili con l'applicazione combinata di più Funzioni di Excel (ad. esempio INDICE e CONFRONTA ma non solo)
Come alternativa al Cerca verticale, molto interessante è la nuova Funzione CERCA.X

Novità ! Con Microsoft365 da marzo 2020 è disponibile la nuova Funzione CERCA.X questa funziona corregge alcuni limiti della Funzione CERCA.VERT
Per info si rimanda alla pagina: La nuova Funzione CERCA.X

Indice Generale Argomenti: EXCEL

[Valter Borsato: ottobre - 2013 | Ultimo aggiornamento 10/11/2024]