In questa guida descriverò tutti i passi necessari per scrivere correttamente la famosa formula di Excel Cerca.Vert, nota in inglese come VLookup. Si tratta di una formula molto importante e di uso frequente in quanto permette di combinare i dati presenti in fogli differenti. Tuttavia il suo utilizzo non è semplicissimo e probabilmente se ne sono resi conto anche gli sviluppatori di Excel, che infatti hanno reso disponibile nelle ultime versioni una formula analoga di nome Cerca.X il cui utilizzo è sicuramente più intuitivo e che spiegherò in questo articolo. Ma facciamo un passo alla volta e iniziamo con il Cerca Verticale!
Funzione CERCA.VERT: primo caso d’uso
Vediamo come utilizzare la funzione Cerca.Vert con un primo esempio pratico che rappresenta un caso d’uso abbastanza frequente. Nel foglio Excel nell’immagine in basso vogliamo sapere se i clienti presenti nella colonna F sono presenti anche nell’elenco della colonna A.
L’idea è quella di iniziare a verificare cosa accade per il cliente presente nella cella F2, per poi trascinare la formula verso il basso ed estendere il risultato a tutte le altre righe.
Ecco allora tutti gli step da seguire per ricercare il valore presente nella cella F2 all’interno della colonna A. Alcuni di essi, come i primi tre e l’ultimo, sono comuni ad ogni funzione. Inoltre, tra un parametro e l’altro va sempre inserito il carattere separatore “punto e virgola”:
- scrivere =
- scrivere il nome della funzione (CERCA.VERT)
- aprire la parentesi tonda
- selezionare la cella che vogliamo cercare (F2)
- selezionare la colonna dopo vogliamo cercare il contenuto
- valorizzare il terzo parametro con 1
- valorizzare il quarto parametro con 0
- chiudere la parentesi
Alla fine avremo questa formula:
=CERCA.VERT(F2; A:A; 1; 0)
e questo risultato
Trascinando la formula verso il basso, possiamo cercare anche gli altri valori della colonna F. La formula restituirà un errore in corrispondenza delle celle con valori che non sono presenti nella colonna A. In questo caso possiamo “circondare” la funziona CERCA.VERT con la funzione SE.ERRORE, per convertire l’errore in un messaggio a nostra scelta.
= SE.ERRORE(CERCA.VERT(F2; A:A; 1; 0);”non presente”)
Funzione Cerca Vert: riportare un risultato diverso dal valore cercato
Anche se nel secondo argomento input della funzione Cerca Vert selezionassimo più di una colonna, ai fini della ricerca sarà considerata sempre e soltanto la prima. Tuttavia, tramite il terzo argomento di input, possiamo fare in modo di riportare come risultato della formula un altro valore, diverso da quello cercato. Sempre facendo riferimento all’immagine di prima, per visualizzare l’importo del 2017 dei clienti della colonna F presenti anche in A, occorre:
- scrivere =
- scrivere il nome della funzione (CERCA.VERT)
- aprire la parentesi tonda
- selezionare la cella che si vuole ricercare (nel nostro caso F2)
- selezionare un insieme di colonne: la prima sarà usata per fare la ricerca, le altre per restituire i valori (nel nostro caso A:D)
- scegliere la colonna contenente il valore da restituire (nel nostro caso 2 perché la colonna 2017 è la seconda all’interno della nostra selezione da A a D)
- scrivere 0 nel quarto parametro
- chiudere la parentesi
La formula finale sarà dunque
=CERCA.VERT(F2; A:D; 3; 0)
ampliabile con il SE.ERRORE in
= SE.ERRORE(CERCA.VERT(F2; A:D; 3; 0);”non presente”)
Ecco il risultato finale
Cerca.X: cosa cambia rispetta al Cerca.Vert su Excel?
La funzione CERCA.VERT ha secondo me quattro aspetti poco intuitivi:
- nel secondo parametro occorre selezionare un insieme di colonne, e non solo la colonna all’interno della quale cercare;
- la modalità per scegliere quali dati visualizzare in output è astrusa (devo utilizzare un numero corrispondente all’ordine della colonna all’interno della selezione precedentemente);
- è fondamentale ricordarsi di valorizzare sempre il quarto parametro con zero (oppure con FALSE), altrimenti il comportamento di default eseguirebbe delle ricerche approssimate con utilità pressoché nulla tranne che in rarissime eccezioni;
- per gestire i valori non trovati abbiamo bisogno di utilizzare un’ulteriore funzione (SE.ERRORE) e complicare ulteriormente la situazione.
La funzione Cerca.X risolve tutti e quattro questi aspetti. Infatti:
- nel secondo parametro dobbiamo selezionare unicamente la colonna in cui cercare i dati;
- nel terzo parametro selezioneremo invece la colonna contenente i dati che vogliamo visualizzare;
- non è necessario ricordarsi di valorizzare alcun parametro opzionale;
- il quarto parametro ci permette di indicare il valore da riportare nel caso in cui la ricerca non vada a buon fine.
La formula precedente
= SE.ERRORE(CERCA.VERT(F2; A:D; 3; 0);”non presente”)
diventerà dunque molto più snella e intuitiva
= CERCA.X(F2; A:A; C:C;”non presente”)
Le due funzioni si comportano diversamente nel caso in cui la cella indicata nel primo argomento sia vuota. Facendo riferimento alla formula precedente:
- se F2 è vuota allora Cerca.Vert restituirà sempre l’errore “#N/D“, a prescindere dal contenuto delle celle nel secondo argomento;
- se F2 è vuota allora Cerca.X restituirà un valore dipendente dall’eventuale presenza di celle vuote nel secondo argomento.
Punti di attenzione nell’utilizzo di Cerca Verticale e Cerca X
Facendo riferimento all’esempio precedente, se nella colonna A fosse presente più di un’occorrenza del valore cercato, le funzioni restituirebbe soltanto il valore nella colonna 2017 associato alla prima occorrenza.
Inoltre se non possiamo selezionare le colonne per intero, perché ad esempio il foglio Excel contiene più in basso anche dati di tabelle diverse da quella di interesse, è fondamentale ricordarsi di utilizzare nella formula i riferimenti assoluti.
Scriveremo dunque
= CERCA.X(F2; $A$1:$A$11; $C$1:$C$11;”non presente”)
invece di
= CERCA.X(F2; A1:A11; C1:C11;”non presente”)
altrimenti avremo dei risultati sbagliati quando trasciniamo la formula verso il basso. Lo stesso vale ovviamente anche per il cerca verticale.
Per entrambe le funzioni, se la cella individuata dal terzo argomento e contenente il risultato è vuota, allora sarà visualizzato uno zero come risultato della formula.
Cercare un valore in una colonna Excel
Abbiamo visto come le funzioni Cerca.Vert e Cerca.X possano essere utilizzate per costruire una formula che restituisca l’informazione se un valore è presente o meno in una colonna. Ad esempio la formula
=CERCA.X(“Molise”; A:A; A:A;”non presente”)
restituirà:
- Molise se questa stringa è presente almeno una volta nella colonna A;
- “non presente” altrimenti.
Possiamo inoltre costruire una formula che combini le funzioni di ricerca e la funzione SE, per restituire dei messaggi personalizzati. Ad esempio
=SE(CERCA.X(“Molise”; A:A; A:A)=”Molise”;”Presente almeno una volta”;”Non presente”)
restituirà i due messaggi codificati in base al risultato della ricerca del valore nella colonna Excel.
Continua a imparare
- Studiamo le tabelle Pivot
- Una lista di esercizi sulle principali funzionalità di Excel
- Le competenze da acquisire per lavorare efficientemente con Excel