Funzione CERCA.VERT (cerca verticale)

La funzione cerca vert permette di cercare un determinato valore nella prima colonna di un elenco e di restituire il contenuto di una delle celle della stessa riga del valore trovato. 

La funzione CERCA VERTICALE è una delle funzioni di Excel più importanti perché trova applicazione in tantissime situazioni quotidiane.

Il cerca verticale serve, ad esempio, per recuperare un prezzo di un prodotto dato il codice prodotto. Oppure consente di associare le informazioni anagrafiche di un cliente dato il suo codice.

Il cerca vert lavora su due elenchi che hanno almeno un’informazione in comune (ad esempio il codice prodotto) e consente di riportare nel primo elenco una o più informazioni presenti nel secondo elenco (il prezzo ad esempio).

Vediamo subito un esempio pratico sulla funzione cerca.vert.

Abbiamo due elenchi. Il primo è un elenco di clienti che appartengono ad una specifica area geografica. Il secondo intervallo è un elenco delle aree geografiche e dei venditori che seguono ogni specifica area.

cerca vert: valori da ricercare

Il risultato finale del cerca verticale dovrà essere il seguente: l’area del cliente1 (Sud), il nostro valore, viene cercata dalla funzione nella prima colonna del secondo elenco, quando l’area viene trovata il cerca.vert deve restituire il venditore associato all’area cercata.

risultato finale cerca vert

Notiamo il messaggio di errore #N/D restituito dalla funzione: ci dice che il valore “Isole” non è stata trovata nella matrice tabella. Nel secondo paragrafo vedremo come scrivere “informazione mancante” al posto del messaggio #N/D.

Nel paragrafo successivo vedremo come scrivere il cerca.vert. Oltre a testo ed immagini troverai anche la spiegazione video di come si usa il cerca verticale.

1. Come scrivere la funzione cerca.vert?

cerca verticale: funzioni ricerca riferimento

Ci si posiziona nella cella nella quale si vuole ottenere il dato. Si richiama l’elenco delle funzioni di excel. Si individua la funzione CERCA.VERT nella categoria RICERCA E RIFERIMENTO (oppure da “Usate di recente” se abbiamo da poco usato la funzione o dalla categoria “Tutte”, dove le funzioni di Excel sono elencate in ordine alfabetico).

Popoliamo i parametri della funzione:

valore da cercare

 1  il valore da cercare: nel nostro caso C3, la cella in cui è scritta l’area

 2  l’intervallo sulla cui prima colonna cercare il valore, chiamata matrice_tabella: nell’esempio è l’intervallo H3:I6

cerca vert: matrice tabella

 3  il numero della colonna della tabella da cui estrarre il valore, chiamato indice: nel nostro caso è 2, poiché 1 indica la prima colonna della matrice tabella (l’intervallo Area), 2 indica la seconda colonna (Venditore) a destra della colonna iniziale della matrice.

funzione cerca vert: indice

 4  Il quarto parametro della funzione va solitamente posto a zero (o FALSO), questo fa capire ad Excel che vogliamo che venga cercato esattamente il valore di partenza. In caso di assenza di tale valore verrà restituito il risultato “non disponibile” (#N/D). Si parla di ricerca con “corrispondenza esatta”. Ti mostrerò poco più avanti un esempio di utilizzo della funzione cerca.vert in cui il quarto parametro dovrà essere 1 oppure VERO. In quel caso si parla di corrispondenza approssimativa.

funzione cerca verticale: intervallo

La funzione cerca.vert di questo esempio sarà quindi:

=CERCA.VERT(C3;H3:I6;2;0)

e il risultato della funzione cerca.vert sarà il seguente:

risultato cerca vert

Se proviamo a trascinare il nostro risultato nelle celle successive otteniamo però degli errori:

trascinamento-errori

Il problema sta nel fatto che trascinando la funzione verso il basso cambia il riferimento alla matrice tabella

problema matrice tabella

Risolviamo bloccando con il dollaro il riferimento alle righe della matrice tabella del cerca.vert

La forma corretta della funzione è quindi =CERCA.VERT(C3;H$3:I$6;2;0)

Se hai bisogno di dare una “rinfrescatina” alle tue conoscenze su come usare il dollaro nelle formule puoi leggere il tutorial su come usare il dollaro, corredato di video YouTube.

dollaro blocca matrice tabella

Possiamo quindi trascinare la funzione nella riga successiva e in tutte le celle sottostanti. Otteniamo così il risultato corretto.

risultato finale cerca vert

Questo il video su Excel che mostra i passaggi con cui inserire la funzione cerca.vert:

Riassumendo ciò che è stato fatto nell’esempio precedente della funzione, possiamo dire che la funzione cerca.vert ricerca in questo modo:
il “Valore”, il primo argomento della funzione cerca verticale, viene cercato nelle celle della prima colonna della “Matrice_tabella”;
– una volta trovata la prima riga contenente il valore cercato, la funzione restituisce l’informazione specificata dal parametro “Indice”, il quale rappresenta il numero della colonna da cui prelevare il dato.
– Il parametro facoltativo “Intervallo” va posto a zero quando, situazione molto frequente, si chiede di cercare esattamente il “valore” e di mostrare il messaggio “non disponibile” (#N/D) nel caso esso non venga individuato.

Vediamo ora come personalizzare il messaggio di errore della funzione cerca verticale. Con #N/D Excel ci dice che non ha trovato quel particolare valore nella colonna più a sinistra della matrice.

Guida PDF su CERCA.VERT

Vuoi scoprire tutto sulla funzione CERCA.VERT?

Diventa un esperto con la mia guida pdf dedicata a CERCA.VERT, CERCA.X, INDICE e CONFRONTA.

Con esempi pratici e video di approfondimento.

Scopri la guida
Guida in PDF su CERCA.VERT

2. Gestire l’errore #N/D

#N/D, valore non disponibile. Questo il messaggio che otteniamo quando il cerca.vert non riesce a trovare il valore che noi stiamo cercando.

Come evitare di vedere la scritta #N/D? Come personalizzare il messaggio errore della funzione cerca.vert?

L’errore #N/D nel nostro esempio non dipende da errori nella funzione (come ad esempio quello visto in precedenza legato all’assenza dei dollari sul nostro intervallo di ricerca) ma dal fatto che la voce Isole, il valore di ricerca, non trova corrispondenza nella matrice tabella.

Nell’esempio vogliamo restituire il valore “informazione mancante” al posto di #N/D. In questo modo il nostro lavoro sarebbe più comprensibile, anche agli occhi di altre persone a cui dovessimo eventualmente girare il nostro file.

risultato completo se errore

La funzione che ci aiuta è il SE.ERRORE.

La funzione logica SE.ERRORE analizza il risultato di una cella o di una espressione . Se la formula restituisce un errore allora la funzione SE.ERRORE consente di specificare che cosa visualizzare nella cella al posto dell’errore.

La funzione prevede quindi due parametri: la cella (o l’espressione) da valutare e il contenuto da scrivere nel caso di errore.

La sintassi della funzione cerca.vert è:

=SE.ERRORE(valore;valore_se_errore)

Nel nostro caso scriveremo

=SE.ERRORE(D3;”informazione mancante”)

funzioni logiche se errore

oppure richiamiamo la funzione dalla tipologia Logiche

La funzione legge il contenuto di D3. Se D3 è testo/numero/data/cella-vuota la funzione restituisce il contenuto di D3. Se D3 è un errore la funzione restituisce la scritta “informazione mancante”.

Questi i parametri della funzione:

parametri se errore

Questo il risultato della funzione sulla prima cella:

risultato-se-errore-su-prima-cella

E questo il risultato dopo aver riportato la formula anche nella riga successiva e in quelle sottostanti:

risultato completo se errore

Capito l’utilizzo del SE.ERRORE possiamo annidare CERCA.VERT e SE.ERRORE sulla stessa cella.

se errore nel cerca vert

Ci posizioniamo su D3. Dopo l’uguale e prima del cerca vert scriviamo SE.ERRORE(

Punto e virgola dopo la chiusura del cerca vert. Scriviamo il secondo parametro della funzione SE.ERRORE: “informazione mancante” e chiudiamo la parentesi

secondo-parametro dopo cerca vert

Trasciniamo la funzione nelle celle sottostanti ed otteniamo il risultato

risultato se errore con cerca vert

Possiamo ora cancellare le elaborazioni fatte nella colonna E ed allargare la D

colonna e cancellata

Una breve considerazione utile per chi ha una versione di Excel inferiore alla 2007.

La funzione SE.ERRORE è stata introdotta con Excel 2007. Nelle versioni precedenti lo stesso risultato poteva essere raggiunto annidando le funzioni VAL.ERRORE e SE:

=SE(VAL.ERRORE(D3);”informazione mancante”;D3)

Se la cella D3 è un errore allora scrivi “informazione mancante” altrimenti restituisci D3.

Chi utilizza versioni recenti di Excel troverà anche la funzione SE.NON.DISP. Molto simile concettualmente al SE.ERRORE. Con la differenza che SE.NON.DISP valuta solamente l’errore #N/D.

3. Come usare il carattere jolly *

Non sempre il valore che stiamo cercando è scritto esattamente nello stesso modo nella prima colonna della matrice tabella. A volte il valore è una parte del contenuto della cella.

Esempio pratico. Nell’immagine sottostante possiamo vedere cosa succederebbe se nella matrice tabella ci fosse scritto “Area Nord-ovest” e “Area Nord-est” al posto di “Nord-ovest”e “Nord-est”.

matrice colonna modificata

Il cerca vert non troverebbe i valori relativi a quelle due etichette. E il SE.ERRORE ci restituirebbe “informazione mancante”.

Come sistemare? Potremmo modificare la matrice tabella togliendo “Area ” dalle prime due celle. Ma questo metodo ci costringerebbe ad utilizzare funzioni, tipo SINISTRA o STRINGA.ESTRAI, e non sarebbe molto “elastico”.

Risolviamo con il carattere jolly “*”.

Trasformiamo il nostro cerca vert iniziale da

CERCA.VERT(C3;H$3:I$6;2;0)

a

CERCA.VERT(“*”&C3;H$3:I$6;2;0)

asterisco nel valore

Cosa vuol dire quel “*”&C3 che abbiamo messo nel parametro valore della funzione cerca.vert?

Stiamo concatenando (con il simbolo &) il carattere * ed il contenuto di C3 (Sud): il risultato di questa concatenazione, quel che Excel legge, è “*Sud”

L’asterisco sta per “un qualsiasi numeri di caratteri”, da zero in su.

Quindi scrivere “*Sud” vuol dire che Excel si fermerà dove è scritto solo “Sud” oppure dove “Sud” è la parte finale del contenuto della cella: quindi “Area Sud” verrà trovato dal cerca vert, così come qualsiasi altra scritta che termini con “Sud”, ad esempio “Zona Sud”, “Area geografica Sud”, spazi seguiti da “Sud”.

Trascinando otteniamo il risultato corretto del cerca.vert:

correzione con carattere jolly asterisco

Scrivendo “*”&C3&”*” diciamo ad Excel che ci va bene che trovi o le celle in cui è scritto solo “Sud” oppure quelle in cui “Sud” è contenuto. L’asterisco finale quindi fa capire ad Excel che anche dopo “Sud” potrebbe esserci altro, spazi compresi.

Lo conosci il trucchetto per verificare se Excel esegue la concatenazione “*”&C3 nel modo da noi atteso???

Nella formula selezioniamo “*”&C3

selezione valore

Tasto F9, otteniamo il risultato della concatenazione che Excel effettuerà: “*Sud”

tasto F9 risultato

ATTENZIONE: se non vogliamo perdere parte della nostra formula, dopo aver verificato con il tasto F9 dobbiamo tornare indietro con CTRL+Z, altrimenti all’interno del cerca vert resterà scritto “*Sud” invece che “*”&C3

4. Perché FALSO il quarto parametro?

Perché quasi sempre poniamo a 0 o FALSO il quarto parametro? La risposta che diamo è, spesso, “perché ci hanno sempre detto di fare così”. Ok ma cerchiamo di capire perché è così importante farlo.

Se riuscirai ad arrivare sano e salvo alla fine di questo esempio un po’ complicato le tue competenze sul cerca vert saranno già superiori a quelle della gran parte degli utenti che mettono 0 o FALSO perchè qualcuno ha “sempre detto di fare così”. 🙂

Iniziamo. Quando nel quarto parametro scriviamo 0 o FALSO stiamo chiedendo ad Excel una “corrispondenza esatta”.

Vuol dire che se stiamo chiedendo di cercare il valore “Sud” vogliamo che Excel cerchi nella matrice tabella la prima cella il cui contenuto è “Sud”. Eventuamente giocando con l’asterisco come detto nel precedente paragrafo.

Se stiamo cercando il codice AZ980 vogliamo che Excel cerchi esattamente quel codice e che ci restituisca #N/D nel caso il codice non sia presente. Vogliamo una corrispondenza esatta.

Quando lasciamo vuoto il quarto parametro oppure lo poniamo uguale a 1 oppure a VERO stiamo cercando una corrispondenza più simile, detta anche corrispondenza approssimativa. Excel ci dice che per poter applicare una corrispondenza approssimativa i dati della prima colonna della matrice tabella devono essere ordinati.

E se i dati non sono ordinati cosa succede?

Succede che i risultati possono essere errati!

Ecco cosa succede nel nostro esempio. Togliamo il quarto parametro dal cerca vert iniziale, quello senza il se.errore:

quarto parametro omesso

Questo il risultato. In rosso i risultati errati del cerca.vert.

errori ricerca piu simile

E se ordiniamo la prima colonna della matrice tabella?

matrice tabella ordine crescente

Succede che i dati in rosso, quelli che prima erano sbagliati, ora sono corretti. Ma adesso c’è un altro problema: l’area Isole è associata al venditore 3 ma noi invece dovremmo avere il messaggio #N/D.

risultato con matrice tabella ordinata

Perché Isole è ora associato a venditore 3? Perché stiamo attuando una ricerca approssimativa, il che vuol dire che se Excel non trova il valore che stiamo cercando (Isole) è libero di individuare nella matrice tabella un valore “simile” a quello cercato. “Simile” in termini di vicinanza alfabetica in questo caso.

Riassumendo, lasciando vuoto il quarto parametro pare che i problemi siano 2:

se la prima colonna non è ordinata il rischio è di ottenere risultati non corretti

se la prima colonna è ordinata il rischio è di ottenere risultati anche per quei valori che in realtà nella matrice tabella non compaiono

Ma allora il quarto parametro non serve a niente??? Eh no, se c’è vuol dire che in qualche situazione ha senso che venga utilizzato. Giusto.

Ecco la situazione: il quarto parametro va lasciato vuoto quando la matrice tabella è composta da intervalli.

Ecco un esempio pratico sul cerca vert con corrispondenza più simile.

Nella tabella dell’immagine seguente i dati si riferiscono a punteggi conseguiti e dobbiamo associare ad ogni punteggio il relativo risultato (scarso, sufficiente, buono, ottimo).

tabella con range

Possiamo usare il cerca vert nel modo classico, con il quarto parametro posto a FALSO? NO!!!, perché, come abbiamo visto, mettere FALSO (o zero) significa chiedere una corrispondenza esatta: cerca esattamente il punteggio (il primo ad esempio è 120) nella prima colonna della matrice tabella. Se non lo trovi restituisci #N/D. In questo caso il 120 e tutti gli altri punteggi non verrebbero mai trovati ed avremmo #N/D in ogni riga.

Ma allora ci creiamo una matrice tabella che abbia tutti i singoli possibili punteggi, da 1 a 300, ed accanto ad ogni punteggio scriviamo il risultato, no? Tecnicamente è possibile, ma sai quanto tempo perdiamo per fare una cosa del genere 🙁

Facciamo una cosa più furba: riscriviamo la matrice tabella semplicemente riportando in ogni cella il valore iniziale del singolo intervallo. Come nell’immagine seguente.

matrice tabella range

Sulla matrice così creata andiamo ad applicare un CERCA.VERT lasciando vuoto il quarto parametro. Questa la nostra funzione:

=CERCA.VERT(B5;F$11:G$14;2)

cerca vert quarto parametro vuoto

Il che equivale, nella finestra dell’inserimento funzione, all’inserimento delle seguenti informazioni:

finestra parametri cerca verticale

Perché funziona? Come ragiona Excel se lasciamo vuoto (o VERO o 1) il quarto parametro?

La descrizione nella funzione ci dice che usare così il quarto parametro significa “trovare la corrispondenza più simile nella prima colonna (in ordine ascendente)“.

Ci dice che la prima colonna deve essere ordinata in maniera ascendente. Perché? Perché l’ordine è funzionale al modo di “ragionare” di Excel. Il singolo punteggio, ad esempio il primo, che è 120, verrà cercato nella prima colonna ed Excel si fermerà sul 120 se questo è uno dei valori oppure sul più grande tra i minori di 120, in questo caso nel valore 101.

Nel dettaglio, Excel legge il primo valore dell’intervallo, che qui è 0, e prosegue verso il basso. Legge il valore della seconda riga, 50, vede che il non è il valore che sta cercando (120) e che è inferiore. Legge quindi il terzo valore, il 101, e prosegue perché non è 120 ed è ancora inferiore allo stesso 120. Legge il quarto parametro, 201, capisce di aver già superato il 120, questo proprio perché i valori della prima colonna della matrice devono essere in ordine ascendente. Dato che ha superato il 120 Excel fa un passo indietro e si ferma al valore precedente, il 101, prendendo così il più grande tra i minori di 120.

A questo punto abbiamo capito perché ci hanno sempre detto di impostare il quarto parametro a FALSO (o zero) nel cerca.vert: perché quasi sempre noi NON abbiamo una matrice tabella che rappresenta intervalli ma, al contrario, dobbiamo cercare un particolare valore all’interno di una matrice tabella e saper con certezza se quel valore è presente o no.

Se, facendo una ricerca esatta, ci dimenticassimo di impostare a FALSO il quarto parametro rischieremmo di ottenere una risultato assolutamente errato, in quando Excel andrebbe ad applicare la logica poco sopra descritta, ipotizzando una prima colonna in ordine ascendente e restituendoci probabilmente un valore simile a quello da noi cercato.

Ma è ben diverso dire che l’azienda X ha fatto acquisti per 100.000 euro quando magari l’azienda X non è affatto presente nel nostro elenco ma il nostro cerca vert, avendo il quarto parametro vuoto, ha cercato una corrispondenza più simile e si è fermato ad un’azienda dal nome simile a quello da noi cercato!!! 🙂

5. Un trucco per poter aggiungere colonne

Che succede se nella matrice tabella aggiungiamo colonne prima della colonna da cui estraiamo il dato?

Il cerca vert non restituisce più il dato corretto.

Esempio pratico. Riprendiamo la tabella dei primi paragrafi ed aggiungiamo una colonna all’interno della matrice tabella.

Il cerca vert CERCA.VERT(C3;H$3:J$6;2;0) restituisce il dato della colonna 2, come specificato nel terzo parametro, quindi 0. Zero perché trova cella vuota nella colonna 2

colonna nuova errore

Come risolvere? Utilizziamo la funzione CONFRONTA.

CONFRONTA ci restituisce in maniera dinamica la posizione della cella “Venditore” all’interno delle intestazioni.

Si scrive così: CONFRONTA(D2;H2:J2;0)

funzione-confronta-sintassi

Il risultato è 3, significa che la colonna “Venditore” è al terzo posto tra le intestazioni. A noi quindi nel cerca vert serve restituire l’informazione della colonna 3.

risultato funzione confronta

Proviamo ora ad annidare CONFRONTA e CERCA.VERT

Doppio clic sulla cella del CONFRONTA. Selezioniamo tutto eccetto l’uguale. Premiamo ESC.

seleziona confronta

Doppio clic sulla funzione CERCA.VERT. Selezioniamo il terzo parametro

cerca-vert selezione terzo parametro

Incolliamo il CONFRONTA precedentemente copiato

confronta nel terzo parametro

Otteniamo la funzione finale

risultato finale confronta

Se cerchi una guida alle funzioni di Excel (e nel contempo vuoi sostenere questo blog 🙂 ) valuta l’acquisto della mia guida alle funzioni: un file pdf che illustra 75 funzioni importanti e mostra 100 esempi pratici.

6. INDICE e CONFRONTA per risolvere problema della prima colonna

Sappiamo che il CERCA.VERT cerca un valore all’interno della PRIMA COLONNA della matrice_tabella. In questa puntata del tutorial sul cerca verticale vediamo cosa fare quando la colonna dove cercare il valore NON è in prima posizione!

Spostiamo la colonna, direte! Sì, ma questo non è sempre possibile. Può essere infatti che siano già state applicate delle funzioni che necessitano di un particolare ordine. Magari proprio dei cerca.vert, che “salterebbero” se cambiassimo l’ordine delle colonne.

Dobbiamo quindi ricorrere a due funzioni alternative al cerca verticale: INDICE e CONFRONTA.

La situazione iniziale è la seguente: il cerca.vert associa il venditore a ciascun cliente sulla base dell’area di appartenenza di venditore e cliente

cerca vert corretto

Introduciamo il problema: cambiamo l’ordine delle colonne della matrice tabella! Il cerca vert non funziona più.

cerca vert problema colonne invertite

Risolviamo il problema con la funzione INDICE, la quale restituisce un valore di una tabella sulla base dell’incrocio di una riga e di una colonna specificate, una specie di battaglia navale.

=INDICE(H2:J6;4;1)

Dalla matrice H2:J6 mi devi restituire il valore che si trova all’intersezione della quarta riga e della prima colonna!

funzione indice

Ora dobbiamo solo cercare di rendere dinamici i parametri 4 e 1, relativi alla riga e alla colonna. Lo facciamo con la funzione CONFRONTA, la quale ci restituisce la posizione di una determinata cella all’interno di un intervallo di celle:

=CONFRONTA(C3;I2:I6;0)

Cerca C3 cioè “Sud” nell’intervallo I2:I6 e restituiscimi la posizione (4). Lo zero finale ha lo stesso significato che ha l’ultimo parametro del cerca.vert, quindi “ricerca esatta”.

funzione confronta

Rendiamo dinamico anche il parametro della colonna, sempre con la funzione CONFRONTA

=CONFRONTA(D2;H2:J2;0)

funzione confronta secondo parametro

Concludiamo annidando le funzioni INDICE e le due funzioni CONFRONTA. Impostando i necessari dollari per bloccare alcuni riferimenti.

=INDICE(H$2:J$6;CONFRONTA(C3;$I$2:$I$6;0);CONFRONTA(D$2;$H$2:$J$2;0))

risultato finale cerca vert con indice confronta

7. Approfondimenti

49 Comments

  1. questa guida è fantastica! stavo avendo dei problemi nel mantenere fissa la matrice della tabella e non riuscivo a capire il perché..e poi ho trovato nel tuo articolo la soluzione, grazie e complimenti! 🙂

  2. Ciao Samuele,
    sei super!!! Ho acquisito un sacco di DRITTE dal tuo sito.
    Ho un quesito da porti, sul comando CERCA.VERT:
    ho un file con vari fogli, e quindi varie Matrice_tabella, vorrei impostare un foglio principale con il comando CERCA.VERT, ma con una casella a parte per definire il foglio Matrice_tabella.
    Ho provato in modo banale inserendo =J5 (identificativo casella) nella formula, ma non funzia!!! ovviamente nella casella J5 ho digitato il nome del foglio.
    Riesci a risolvere anche questa?
    Grazie e complimenti per il sito .
    Andrea

  3. Ciao Andrea, grazie! 🙂

    Per impostare una matrice tabella variabile nel Cerca vert potresti utilizzare la funzione INDIRETTO e il concatena (&).

    Ipotizzando che l’intervallo della matrice tabella di ciascun foglio sia A1:B3 potresti provare così:

    =CERCA.VERT(valore_da_cercare;INDIRETTO(J5&”!”&”A1:B3″);colonna;intervallo)

    La funzione INDIRETTO legge quel che tu concateni al suo interno come un intervallo a cui far riferimento. Quindi la funzione INDIRETTO legge “nome_del_foglio_scritto_in_J5!A1:B3” e si aspetta che questo sia un riferimento ad una o più celle.

    Se il tuo intervallo non è sempre lo stesso, non è quindi sempre A1:B3 del mio esempio, nella cella J5 scriverai il riferimento completo e lo darai sempre in pasto ad INDIRETTO. Su J5 quindi scriverai ad esempio nome_del_foglio!A1:C100 e, nel secondo parametro del cerca vert, INDIRETTO(J5).

    Prova ad adattare l’idea alla tua situazione e fammi sapere, se ti va.

    Ciao!

  4. Ciao Samuele,
    complimenti per il sito e per le precise spiegazioni che dai! Eccezionale!
    Avrei un’osservazione relativamente all’ultimo paragrafo.
    Nella funzione Confronta, non dovremmo bloccare i valori in questo modo?=CONFRONTA(D$2;H$2:J$2;0)
    Altrimenti il risultato è errato se non blocchiamo le celle, corretto?
    Ti ringrazio per l’aiuto
    Eleonora

  5. Ti ringrazio, ho risolto!
    Ho notato anche che il nome del foglio non deve contenere caratteri speciali, con il – non funziona!!!

    Ciao

  6. ottima guida!!! complimenti per il sito. la tua spiegazione sul cerca.vert mi ha salvato dal dover cercare ogni volta ad occhio i singoli valori da un database di dati. Particolarmente utile la funzione jolly che non ne sapevo proprio dell’esistenza.

    Bravo

  7. Ciao Samuele

    avrei un quesito da porti sul CERCA.VERT. Quando utilizzo il cerca vert per estrarre un valore in una matrice di dati come faccio nel caso in cui ci sono due valore molto simili da ricercare. Ad esempio utilizzo la formula =CERCA.VERT(“*”& “società x” & “*”; A3:D340; 4;0) i due valore da ricercare sono Società X (gruppo) e Società X (capogruppo), in questo caso il CERCA.VERT mi dà solamente il valore della Società X (gruppo), ovvero della società inserita prima in successione. Come faccio ad avere restituito il valore dell’altra società, Società X (capogruppo)?

    Grazie mille

  8. Ciao Francesco. La vedo dura. Il cerca.vert infatti restituisce solo il primo valore.

    Nel caso specifico potresti forse inserire (capogruppo) dopo il secondo asterisco.
    Quindi modificare la tua formula da
    =CERCA.VERT(“*”& “società x” & “*”; A3:D340; 4;0)
    a
    =CERCA.VERT(“*”& “società x” & “*(capogruppo)”; A3:D340; 4;0)

    Però dubito che questo ragionamento possa poi essere applicato anche alle altre tue ricerche.

  9. Eccezionale sapevo dell’utilizzo “indiretto” ma non avevo capito che il punto esclamativo doveva essere fra apici “!”
    Grazie!!!!

  10. grazie samuele!! mi hai cambiato la VITA, i compiti di informatica erano un inferno ma ora, grazie a te e al tuo sito, ho la media del 6!!!! e non è facile

  11. Che soddisfazione leggere le tue parole 🙂 Mi fa molto piacere! Brava!!! Fra quattro mesi aspetto il tuo commento in cui mi dirai che sei arrivata alla media del 7, ok? 🙂

  12. Ciao Samuele, ho trovato i tuoi insegnamenti veramente d’aiuto. La mia professoressa non spiega nulla e senza di te sarei persa. Adesso ho la media dell’8 ed è tutto merito tuo!!! Ti ringrazio di vero cuore!!!!! 🙂

  13. Salve Samuele,

    ho trovato chari e molto funzionali le sue speigazioni e i suoi video.

    Da qualche giorno sono passato ad excell 2016, e quando uso la funzione cerca.vert su un file con molte colonne, noto che la possibilità di vedere il numero di colonne che formano l’area di riferimento, dove andrò a fare il cerca.vert, NON ESIDTE PIU’.

    Questa funzionalità del cerca vert mi era molto utile, specie quando dovevo considerare un’area molto grande e qui considerare la 23-esima colonna. Nelle precedenti versioni di excell era presente, mentre in quella del 2016 non la trovo attiva nè sono riuscito ad attivarla.

    Sono così “obbligato ” a contare ” a mano il numero di colonne fino ad identifiare il numero di quella di mio interesse: saprebbe cortesemente indicarmi come mai questa funzionalità è stato dis attivatà? e se non lo è, indicarmi come posso renderla ancora operativa?

    Grazie

    Leonardo

  14. Niente, volevo solo dirti che grazie a te non sono costretta a fare millemila ore di straodinari.
    Grazie di cuore.

  15. Ciao Samuele,
    innanzitutto complimenti per la guida
    io sto cercando di utilizzare il CERCA.VERT su un elenco di numerosi dati che ho importato in Excel da un file Access e che ho successivamente convertito da celle di testo a celle con numero!
    Il problema è che nonostante queste attenzioni mi viene continuamente fuori un errore di tipo #RIF! ogni volta che il cerca verticale trova il numero da posizionare nella casella. Cosa sto sbagliando?

    Grazie per la risposta, buona giornata

  16. Ciao Giorgia. Grazie 🙂

    Solitamente #RIF! viene restituito dal CERCA.VERT quando nel terzo parametro inseriamo un numero che eccede il numero di colonne della matrice tabella. Prova a controllare questo. Altrimenti mandami pure un estratto dei tuoi dati samuele@amicoexcel.it

  17. Oddio avevi ragione!
    grazie mille, ho controllato e ora funziona! Mi hai salvato giorni di lavoro:)

  18. Complimentissimi!!!
    Non usavo da tempo queste funzioni di excel, alcune non le conoscevo, e devo dire che è spiegato tutto molto bene, in maniera precisa ed elementare alla portata di tutti!

    Bravo!!!

  19. Grazie mille ottima guida, finalmente tutto funziona come deve, ma c’è modo di cambiare il valore della colonna in automatico trascinando le celle a destra? il valore 2 della formula =CERCA.VERT($A2;$’Lista Alimenti’.$A$2:$AS$1000;2;0) ,forse ho messo qualche dollaro in piu’

  20. Ciao Massimo.

    Puoi rendere dinamico il terzo parametro utilizzando una qualsiasi funzione che trascinata verso destra si aggiorni. Ad esempio RIF.COLONNA.
    RIF.COLONNA(B1) ti restituisce all’inizio 2, poi se trascini si incrementa.

    Quindi potrebbe essere =CERCA.VERT($A2;$’Lista Alimenti’.$A$2:$AS$1000;RIF.COLONNA(B1);0)

  21. Splendida guida!! Spiegazioni semplici e chiare. Ho appena acquistato il tuo libro. Complimenti!!

  22. Grazie mille Gabriele per i tuoi complimenti, mi fanno proprio piacere. E buoni esercizi 🙂

  23. Ciao Samuele,
    complimenti per la spiegazione,
    Vorrei porti un quesito: su la cella A1 ho una formula che restituisce un valore in base a delle scelte fatte.
    Su altre celle voglio applicare il mio cerca.vert passando come valore proprio la cella A1 che è però una funzione.
    Così facendo il cerca.vert mi da sempre errore, mentre se inserisco a mano il valore sulla cella A1 funziona.
    Come posso ovviare la cosa?
    Grazie mille!

  24. Ciao Francesco. Secondo me il problema sta nel risultato che restituisce la funzione inserita in A1. Controlla che non ci siano spazi e che il valore sia coerente con quelli della prima colonna della matrice tabella del CERCA.VERT… se in A1 hai un testo (magari il risultato di una funzione testuale) anche nella prima colonna della matrice tabella devi avere un testo… se in A1 hai un numero, in matrice tabella devi avere numeri.

  25. D’Accordo sulle competenze ma è la chiarezza e la semplicità di espressione che sono il vero valore aggiunto. Almeno a mio parere. Mi sembra importante. Saluti

  26. Salve Samuele,
    vorrei sviluppare ulteriormente la seguente formula, in modo tale che il cercavert possa controllare più fogli contemporaneamente:
    SE(B7=CERCA.VERT(B7;[Cartel2]Foglio1!$A$7:$A$10;1;FALSO);A7-(SOMMA.SE([Cartel2]Foglio1!$A$7:$A$10;B7;[Cartel2]Foglio1!$B$7:$B$10));”F”)
    In questo caso abbiamo in B7 il nome dell’articolo da ricercare negli altri fogli, il risultato è un inventario semiautomatico in cui il numero di articoli presente cala in base al venduto giornaliero. Il mio problema è che il venduto giornaliero si suddivide in un foglio excel per ogni giorno.
    Ritieni possibile creare con una sola formula un controllo su più fogli contemporaneamente?

  27. salve
    ho un centinaio di "casi" come i precedenti che cerca.vert dovrebbe individuare per restituirmi il risultato della formula a loro associata
    ho provato a numerarli ma ottengo sempre 0 come risultato
    esiste una soluzione?
    grazie in anticipo
    albertodorlando

  28. Salve , ho tre fogli excel , sul primo l’anagrafica clienti, sul secondo il listino degli articoli prezzi e sconti, sul terzo l’ordine da compilare.
    Inserendo il codice cliente sul terzo foglio mi compaiono tutti i dati anagrafici del cliente.
    Inserendo il codice articolo sul terzo foglio si compila la riga del prodotto con gli sconti abbinati.
    Fin qui tutto bene, ora vorrei implementare questo programmino con un secondo listino differente dal primo solo per gli sconti.
    Come faccio a far capire a cerca verticale quale dei due listini utilizzare, considerando che gli articoli sono sempre gli stesi e quindi anche i codici articolo?
    Ho provato ad usare SE (ingrosso) Cerca Vert ( . listino 1. ) ; Cerca Vert ( listino 2 ) ma la formula non funziona .
    Mi potete aiutare?

  29. Ciao Samuele,
    Grazie a te sto ripassando le formule di Excel che non usavo da tempo, la mia preferita è il cerca verticale .
    Ti faccio una domanda, è possibile fare un cerca verticale di un codice che può trovarsi su diversi fogli E se lo trova farsi restituire il valore della colonna successiva?
    Ho una lista di codici articolo a cui associare il prezzo ma il listino è stato diviso su più fogli in base alla tipologia di articoli quindi dovrei cercare su tutti i fogli perché mi restituisca il prezzo corrispondente.
    Grazie

  30. Ciao Nina, la matrice tabella deve trovarsi su un foglio… se i fogli non sono troppi potresti annidare dei SE.ERRORE e dei CERCA.VERT… ma procedimento macchinoso…

  31. Ciao Samuele,
    grazie per le ottime dritte che leggo sul tuo sito.
    Ti volevo chiedere se esiste un modo per ripetere in colonna un valore per un numero di volte scritto in un’altra tabella,
    Esempio: ho da un lato una tabella con scritto nella prima colonna A, B, C, nella seconda colonna Rossi, Verdi, Gialli e nella terza colonna 2, 7, 3.
    Dall’altra parte ho una tabella con diverse volte scritti A, B e C e vorrei che nella seconda colonna mi compaiano in corrispondenza degli A 2 volte il valore Rossi e poi ND, in corrispondenza dei B 7 volte il valore Verdi e poi ND, in corrispondenza dei C 3 volte il valore Gialli e poi ND.

    Grazie per l’aiuto!

  32. Ciao Anna.

    Ipotizzando che la seconda tabella si trovi nelle colonne G ed H, una formula potrebbe essere:

    =SE(CONTA.SE(G$1:G1;G1)<=CERCA.VERT(G1;A:C;3;0);CERCA.VERT(G1;A:C;2;0);NON.DISP()) Fammi sapere. Ciao.

  33. Funziona! Mitico!
    Ho però un’altra esigenza che complica questo discorso. Se la tabella è fatta in questo modo:

    A Rossi 2
    A Blu 5
    A Grigi 10
    B Verdi 7
    B Bianchi 2
    C Gialli 3
    C Neri 4

    Posso far sì che nella seconda colonna mi compaiano in corrispondenza degli A 2 volte il valore Rossi, poi 5 volte il valore Blu, poi 10 volte il valore Grigi e poi ND, in corrispondenza dei B 7 volte il valore Verdi, poi 2 volte il valore Bianchi, poi ND, in corrispondenza dei C 3 volte il valore Gialli, poi 4 volte il valore Neri e poi ND?

    Grazie mille!

  34. Ciao Anna. Questa ulteriore specifica non la capisco, scusami.
    Se vuoi mandami un file con elenco di partenza ed elenco che vuoi ottenere. Grazie. Così risolviamo. Scrivi a samuele@amicoexcel.it

  35. Ciao Samuele.
    La tua guida è ottima e spiegata benissimo, ma ho un caso particolare che non so come gestire. Puoi aiutarmi?
    Ho una tabella A dove prendo il valore (un codice Ateco di 6 cifre) e una matrice tabella B dove c’è una lista ridotta di questi valori e una 2a colonna SI/NO.
    Dovrei quindi usare il cerca.vert sulle 2 tabelle con corrispondenza esatta ed evidenziare i mancanti, ma il problema è che nella matrice tabella B i valori sono spesso troncati alle prime 2, 3 o 4 cifre, per indicare che tutti i valori in quel range sono compresi nel dato SI/NO accanto.
    Mi chiedevo quindi se c’era il modo di usare i caratteri jolly anche sulla colonna 1 della matrice.
    Grazie 1000!

  36. Ciao Fabrizio! Grazie 🙂

    E se invece di cercare tutto il codice Ateco di 6 cifre ne cercassi solo l’inizio? Con un SINISTRA ad esempio potresti prenderti i primi 2,3 caratteri?

    =CERCA.VERT(SINISTRA(A2;3);matrice_tabella;2;0)

    Oppure, meglio ancora:
    =SE(VAL.ERRORE(CERCA.VERT(A2;matrice_tabella;2;0));CERCA.VERT(SINISTRA(A2;3);matrice_tabella;2;0);CERCA.VERT(A2;matrice_tabella;2;0))
    in sostanza, come avrai capito, cerchi prima l’ateco normale, di 6 cifre, e solo se esso non viene trovato provi a cercarlo in base ai primi 2 (o 3/4) caratteri solamente.

  37. ciao Samuele,
    ho un quesito da farti, ho due fogli nel quale uno i dati immessi si muovo da sinistra verso destra, e nell’altro dall’alto verso il basso, ora io voglio far eseguire delle operazioni nel foglio1 in base a dove si trova la corrispondenza sul foglio2… ma non sono riuscito a far in modo che sostandomi sulla successiva colonna del foglio1, mi raccolga i valori nelle righe successive del foglio2..
    probabilmente la mia richiesta è impossibile, e lo farò manualmente

    grazie edoardo

  38. Ciao Edoardo. Nulla è impossibile per Excel!!! 🙂

    Da come me la descrivi si potrebbe tentare con la funzione INDIRETTO e le funzioni RIF.RIGA e/o RIF.COLONNA.
    Se vuoi mandami due dati due a samuele@amicoexcel.it

Comments are closed.