Cerca verticale: esempio di cerca vert su fogli diversi

Esempio pratico sull’utilizzo del cerca verticale su più fogli.
Da un elenco di vini recuperiamo il prezzo unitario basandoci sul codice del vino.

Questo articolo tratta una situazione particolare del cerca vert, quella in cui la matrice tabella si trova in un altro foglio. Se hai bisogno delle nozioni di base sulla funzione puoi leggere anche il tutorial sul cerca vert.

Partiamo da due fogli: listino prezzi e ordini.

Listino prezzi: elenco dei vini con varie informazioni, tra cui codice, nome e prezzo unitario.

listino prezzi per cerca vert

Ordini: elenco che nel tempo si popola degli ordini dei vini registrando il codice del vino, la data dell’ordine e il numero delle bottiglie

elenco ordini

Obbiettivo: nel foglio Ordini scrivere una formula che ci permetta di calcolare il prezzo totale sulla base del codice vino ordinato e del relativo prezzo unitario.

Soluzione: con il cerca vert troviamo il prezzo unitario del vino ordinato e moltiplichiamo per il numero di bottiglie.

I passaggi sono i seguenti:

 A  Ci posizioniamo sul foglio Ordini e scriviamo (o richiamiamo dall’elenco delle funzioni) il CERCA.VERT

cerca vert passaggio 1

 B  Popoliamo i parametri del cercavert specificando il valore da cercare (A4), la matrice tabella (l’elenco dei vini del foglio ‘Listino prezzi’), il numero della colonna del prezzo unitario (5) e il fatto che vogliamo una corrispondenza esatta (0)

cerca vert parametri

La formula è quindi:

=CERCA.VERT(A4;’Elenco vini con prezzi’!A$4:E$69;5;0)

Da notare che usare il cerca vert su fogli diversi non è diverso rispetto ad utilizzarlo su un unico foglio. Excel infatti nella matrice tabella scrive il nome del foglio prima dell’intervallo delle celle.

 C  Moltiplichiamo poi il risultato per il numero di bottiglie, posto nella cella accanto a quella dove stiamo inserendo la formula

prezzo per numero bottiglie

Questo quindi il risultato finale:

risultato finale

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

Veniamo ora ad alcune domande che potrebbero sorgerci rispetto a quanto trovato finora.

Se io mi preparo la formula nella prima cella e poi trascino per qualche decina di righe in modo da poter in futuro scrivere le informazioni del nuovo ordine e veder calcolato in automatico il prezzo ottengo #N/D nelle righe dove non ho ancora inserito ordini. Perché? Come posso evitarlo?
Come mostrato nel tutorial sull’utilizzo standard del cerca vert, l’errore #N/D indica che il cerca vert non riesce a trovare il codice che stiamo cercando. In questo caso è come se stesse cercando un codice inesistente.

errore se codice mancante

Possiamo evitare che scriva #N/D utilizzando la funzione SE.ERRORE ed impostando il valore restituito in caso di errore uguale a stringa vuota

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);””)

se errore stringa vuota

Se io scrivessi un codice di vino non presente nel listino prezzi?
In questo caso il cerca verticale non trova il codice e mi restituisce il risultato #N/D.

errore-nd

Potremmo gestire la cosa informando dell’assenza di quel codice:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale’!A4;’Elenco vini con prezzi’!A$4:E$69;5;0);”codice non presente nel listino”)

errore nd personalizzato

Se noi avessimo l’esigenza di attribuire un prezzo standard a tutti i vini che non sono presenti nel ‘Listino prezzi’? Questo perché magari ipotizzo che se il codice non c’è nel listino, il vino è di quelli “sfusi” che vendiamo a 5 euro a bottiglia.

Potremmo attribuire il prezzo direttamente nel parametro se_errore, scrivendo:

=SE.ERRORE(CERCA.VERT(‘Ordini risultato finale (2)’!A11;’Elenco vini con prezzi’!A$4:E$69;5;0);5)

prezzo costante se codice non trovato

Se invece che scrivere un codice di vino non presente nel listino prezzi sbagliassimo a scrivere il codice? Potremmo ad esempio scrivere 59ù al posto del 59, invece che pigiare il tasto INVIO pigio il tasto acccanto… Come fare per capire che quello è un errore di digitazione?

Potremmo fare due tipi di controlli.
1. il valore è numerico? se non lo è c’è qualcosa che non va
2. il valore è numerico ma è superiore al numero del codice vino più alto?

1. il valore è numerico?
Con la funzione TIPO verifichiamo se il dato è numerico (avremmo il risulato 1) o meno. Con la funzione SE diciamo che se non è numerico il codice è errato.

=SE(TIPO(A8)>1;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

codice non numerico

2. il valore è numerico ma è superiore al numero del codice vino più alto?
Ipotizziamo che il valore più alto sia 1000. Con un SE valutiamo se il codice è superiore o meno a 1000.

codice errato se maggiore mille

=SE(A8>1000;”codice errato”;CERCA.VERT(‘Ordini varianti’!A8;’Elenco vini con prezzi’!A$4:E$69;5;0))

Da notare che questa scrittura risolverebbe anche il controllo 1. Excel valuterebbe infatti un codice testuale come superiore a 1000 ed a qualsiasi altro numero.

52 Comments

  1. Bravo! Hai trovato il modo di rendere semplice una funzione spiegata in maniera criptica dalla stessa Microsoft. Grazie e bravo (ancora)!

  2. Grazie per i complimenti Giuseppe! Mi fa molto piacere che tu abbia trovato utile l’articolo e che tu abbia voluto spendere del tempo per farmelo sapere, molto gentile 🙂

  3. E se uso solo codici alfanumerici come posso cercare ugualmente il relativo prezzo?

  4. Ciao Filippo. Che il codice del vino sia numerico o alfanumerico poco cambia secondo me. Dovrebbe funzionarti seguendo la stessa logica. Fammi sapere se non dovesse essere così. O inviami pure un estratto dei tuoi dati a samuele@amicoexcel.it

    Se vuoi trovi qualche esempio ulteriore di applicazione della funzione nel tutorial sul cerca vert.

  5. Grande Filippo! Mi sfuggiva una cosa, ma mentre ti scrivevo l’ho risolta perchè ho capito dove sbagliavo… e cioè, se mi preparo la formula nella prima cella e la trascino, ovviamente la formula delle celle sotto avrà numeri crescenti!! Quindi se partivamo da A4:B69 nelle celle sottostanti automaticamente si avranno A5:B70….A6:B71 ecc. con una perdita di celle da esaminare nella MATRICE_TABELLA! A meno che si “DIGITI” ( invece di selezionare semplicemente le celle col muose) il simbolo “$”….A$4:B$69 in modo tale che l’area MATRICE_TABELLA resti sempre la stessa. E’ corretto?

  6. Ciao! Corretto. Devi usare il simbolo del dollaro 🙂 La matrice tabella va “bloccata” altrimenti scendendo i riferimenti vengono incrementati.

  7. Ciao e complimenti! finalmente esempi più chiari. io sono autodidatta … insomma mi arrangio, ho dei dati che devo “copiare” tra due file differenti ma non riesco a fargli capire da che file leggere il dato da copiare, il percorso insomma… grazie mille

  8. Ciao Simona. Grazie! Mi stai chiedendo come fare per far capire al cerca vert che la matrice tabella si trova in un altro file? Se è così allora apri il file con i dati a cui vuoi far riferimento, poi quando stai scrivendo il cerca vert e sei nel secondo parametro prova a spostarti sul file con i dati e selezionare l’area che ti interessa… per spostarti clicca semplicemente nella barra delle applicazioni, come faresti per spostarti tra due file…

  9. complimenti per il tutorial! a me succede che con listini contenuti funzioni tutto alla perfezione, invece con listini con molte “righe” (ho listini da 15/16 mila righe) i riferimenti vanno a casaccio! immagino sia un problema di excel o sbaglio qualcosa?

  10. Ciao Marco. Non è un problema di dollari vero? Intendo relativi al bloccare la matrice tabella?

  11. Ciao e complimenti per il tutorial. Davvero esplicativo!
    Io ho un problema con un cerca.vert che restituisce un valore che non è presente nelle colonna da cui fare la restituzione. Mi spiego meglio. Uno il cerca.vert cercando un codice presente su foglio 1 nella tabella (bloccata con i $) presente nel foglio 2 e chiedo la restituzione del valore contenuto in una certa colonna. Si tratta di uno 0 o un 1. Niente di più ordinario!
    Fino ad un certo punto il cerca.vert mi restituisce i dati corretti…mentre successivamente continua a restituire 0 anche se c’è 1. Da questo deduco che sta trovando il codice cercato, altrimenti mi darebbe #N/D, ma piuttosto che 1, come contenuto nella cella da restituire, mi da 0.
    Ho provato a riformattare le celle, ma nulla.
    A cosa può essere dovuto? :-SSS
    Grazie

  12. Ciao, io già utilizzo questo che tu hai molto bene spiegato ma ora mi chiedevo se è possibile trascinare la formula e in ogni cella dove ho trascinato deve andare a cercare in un foglio diverso.. Devo dirgli di effettuare la stessa ricerca in una ventina di fogli senza dover riscrivere la formula e ottimizzarla in ogni foglio. Dello stesso file ovviamente.

  13. Ciao Erika, che sia solo un problema di dollari nella matrice tabella? Mandami uno stralcio del file se credi.

  14. Potrebbe essere fattibile… secondo me giocando con il concatena, o meglio con il simbolo di & commerciale, e la funzione INDIRETTO.. nel senso che all’interno del CERCA.VERT potresti tentare di “comporti” il riferimento alla matrice tabella… tipo =CERCA.VERT(A2;INDIRETTO(“nome_foglio”&”!”&A1:G100);…

    Se vuoi mandarmi uno stralcio del tuo file vedo la situazione particolare… Ciao!

  15. Ciao, complimenti per tutte le tue spiegazioni, premetto che è da molto tempo che uso Excel ma ho iniziato a scoprire le funzioni da poco!
    Ho un problema su un Cerca.Vert che devo creare,ho più fogli ( matrice ) dove cercare la “Descrizione “che mi serve. Ma non riesco a fare funzionare la formula per cercarla nei 2 fogli. Ne trova sempre uno e non l’altro…

  16. Ciao Fabio, mandami pure un esempio di file così te lo risolvo direttamente lì…. Grazie.

  17. Ciao e complimenti per l’articolo! Vorrei utilizzare questa funzione per gestire un file di gestione lavoro dove uno user A scrive sul file A selezionando le risorse, l’alto file B cerca verticale sul suo nome e restituisce alcuni valori. Allo stesso tempo il file A riprendere tramite un altro cerca verticale lo stato dei lavori che l’altro è utente compila. Spero di essere stato abbastanza chiaro. La mia domanda è l’aggiornamento dei dei come avviene? Uso la versione 2013 e non credo sia in tempo reale. Grazie per la risposta!

  18. Intendevo l’aggiornamento dei dati con qualche frequenza avviene e se si può intervenire in qualche maniera.

  19. Fantastico! Sei l’Aranzulla di Excel! Finalmente ho risolto l’errore che mi dava la formula cerca.vert. Grazie, grazie, grazie 🙂

  20. Complimenti in maniera chiara ed esauriente hai illustrato sta funzione in maniera semplice ed intuitiva rispetto alla “munnezza” d’informazioni che si trova sparsa in internet !!!!

    Grazie grazie grazie

  21. Ciao Samuele , io invece ho qs problema…ho convertito un file pdf in excel.. tt ok..Perche se in un altro file excel usando il CERCA.VERT.. dove ricerco un testo scritto uguale anche su quello generato dal pdf , mi da N/D? se vado a riscrivere il nome manualmente nella cella del file generato dal pdf.. funziona. cosa bisogna fare?

  22. Ciao Ettore. Excel vede i due contenuti diversi. Ci sono spazi? Ci sono caratteri strani?

    Prova con la funzione LUNGHEZZA a farti restituire il numero di caratteri.

    Se ci sono spazi usa la funzione ANNULLA.SPAZI. Se ci sono caratteri strani prova la funzione LIBERA.

    Fammi sapere.

  23. CIAO COMPLIMENTI PER LA TUA PAGINA
    ho un problema:imposto la ricerca verticale in una cella e mi trova il valore giusto per quella cella;se copio nelle altre celle mi da’ l’errore RIF, dove sbaglio’ ?
    grazie in anticipo
    Emilio

  24. Grazie Emilio!

    Solitamente l’errore #RIF nel cerca.vert viene dato quando si inserisce nel terzo parametro un numero di colonna superiore al numero di colonne della matrice tabella.

    Però nel tuo caso l’errore ti compare solo quando trascini. Mi vengono in mente due possibilità: nella tua formula il terzo parametro è frutto di una espressione che, trascinata, ti crea problemi oppure trascinando qualche riferimento si aggiorna perdendoti i riferimenti definiti nella prima cella.

    In entrambi i casi potresti forse risolvere bloccando con il dollaro.

    Verifica e casomai riportami la formula che utilizzi così magari vedendola ti posso dare qualche info in più.

  25. risolto!! ho usato la funziona ANNULLA.SPAZI come suggerito..grazie mille davvero gentilissimo. complimenti. ciaooo

  26. grazie samuele della tua risposta,come inserisco il dollaro nella formula?
    Qui sotto ti copio la formula con un accenno a cio’ che dovrei fare:
    Dovrei trovare i codici bilancia di alcuni prodotti che stanno in un foglio A ,il codice articolo di questi prodotti e’ il dato che hanno in comune i due fogli.vado su una cella del foglio B clicco ” inserisci funzione”–cerca vert.Nel campo “VALORE” seleziono tutta la colonna dei dati in comune
    (copio questi codici nella prima colonna a sin dopo avere numerato in alto dal 1 in poi le colonne .),poi nel campo” MATRICE” seleziono tutto il foglio A(dove devo ricercare i valori che mi servono) nel campo indice metto il numero della colonna del foglio A che contiene i codici bilancia che cerco ed infine nel campo INTERVALLO Digito FALSO In maiuscolo.
    a questo punto nella prima cella appare il codice bilancia corretto che cerco ma come ti spiegavo se copio nelle celle successive in basso mi da’ #RIF!
    ora ti incollo qui la formula che mi appare nella barra superiore
    .VERT(‘elenco plu gastr. 17-3-2012′!A5:A552;’elenco plu gastr. 17-3-2012’!#RIF!;11;FALSO)
    Scusa ancora se abuso della tua pazienza
    grazie ancora
    Emilio

  27. Ciao Emilio. Due cose:

    1) su valore non serve selezionare tutta la colonna ma solo la cella del valore che devi trovare. Quindi invece che ‘elenco plu gastr. 17-3-2012′!A5:A552 basta ‘elenco plu gastr. 17-3-2012′!A5
    Questo cmq non è la causa dell’errore, è più una miglioria tecnica

    2) il problema dipende dal fatto che nel secondo parametro, la matrice tabella, tu selezioni tutto il foglio. Così facendo Excel ti scrive qualcosa come nome_foglio!Foglio1!1:1048576
    Quando tu trascini in basso i riferimenti vengono incrementati ma così è come se finissi fuori del foglio 🙂 quel 1048576, che è il numero di righe totali del foglio, non può aumentare, Excel ti fa capire con #RIF! che c’è qualche cosa che non va.

    Risolvi così: invece di selezionare tutto il foglio (immagino tu vada a cliccare sul pulsante sopra 1 e a sinistra di A) seleziona le colonne che ti interessano….parti da A (se lì si trova il codice articolo) e seleziona fino all’ultima colonna da cui ti serve estrarre valori.

    Se così non funziona mandami pure uno stralcio del file a samuele@amicoexcel.it

  28. grazie samuele
    provero’ a fare cosi’ appena rientro e ti faccio sapere!!
    Sei un grande e grazie ancora della disponibilita’…a presto!!!!

  29. Grazie Samuele
    ho seguito la tua spiegazione e il problema sulla ricerca verticale e’ risolto.
    grazie ancora per la tua disponibilta’
    sei stato preziosissimo
    a presto e grazie
    Emilio

  30. È possibile utilizzare il cerca.Bert, utilizzando come prima colonna una lista di prodotti con un menù a tendina? In pratica: scegliendo il prodotto dal menù a tendina nella colonna A, dovrebbe comparire il prezzo nella colonna B.
    Grazie mille.
    Roberto

  31. Buonasera Roberto. Direi di si. Se lei ha un elenco di riferimento con prodotti e prezzi può inserire nella colonna B il cerca vert che avrà come valore quel che è stato scelto nella tendina. Quindi =CERCA.VERT(A2;intervallo_tabella_di_riferimento;colonna_prezzo_in_tabella_riferimento;0).

    Se ha modo di provare mi faccia sapere.

  32. Ho scritto questa formula
    CERCA.VERT(C4;’indirizzi Consorziati’!A:H;3;FALSO)
    per estrarre il numero civico da un elenco di indirizzi. Quando il numero non è presente mi estrae 0(zero) invece che lasciare il campo vuoto.
    come posso correggere la formula? anticipatamente ringrazio dell’aiuto oltre a esprimere ammirazione per quanto si legge nel sito.

  33. Grazie Marcello 🙂

    Si potrebbe risolvere in più modi:

    1) con un SE che verifichi se il CERCA.VERT restituisce zero… tipo =SE(CERCA.VERT(A2;B11:C12;2;0)=0;””;CERCA.VERT(A2;B11:C12;2;0))

    2) modificando il formato della cella: si imposta il formato personalizzato e nella casella del formato si digita 0;-0;;@
    Sai che i quattro valori, divisi da punto e virgola, rappresentano rispettivamente il formato da dare ai numeri positivi, ai numeri negativi, agli zeri ed al testo
    0 significa ripeti i numeri… @ significa ripeti il carattere testuale

    Su questo secondo metodo ti rimando, se hai tempo, ad una pagina ufficiale Microsoft :https://support.office.com/en-us/article/display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

    Nella pagina trovi anche altri sistemi… occhio che sul formato personalizzato loro suggeriscono 0;;;@ ma io non sono d’accordo del tutto… si perderebbero i numeri negativi così come lo scrivono.

    Fammi sapere se riesci a risolvere. Ciao!

  34. Geniale. GRAZIEEEEEEEE
    Ho modificato la formula con i riferimenti della mia tabella, e cambiando il parametro =0 con =” ” (segue la formula )
    SE(CERCA.VERT(C4;’indirizzi Consorziati’!A:I;3;0)=” “;””;CERCA.VERT(C4;’indirizzi Consorziati’!A:I;3;0))

    questo non bastava ed allora ho formattato le colonne con il formato personalizzato 0;-0;;@ ed improvvisamente mi ha restituito i dati nel modo corretto in cui volevo fossero estratti.
    mi piace lavorare con excel e sicuramente approfitterò ancora
    di nuovo un immenso grazie.

  35. Salve ho digitato la formula cerca vert in questo modo

    =CERCA.VERT(D63;'[90 Italy Serie C – Group A.xlsm]ITA C – A’!$F$13:$EM$260;138;FALSO)

    e fin qui tutto ok.
    Il mio problema sta nel fatto che il nome del file a cui faccio riferimento lo vorrei prendere da una cella di una colonna di appoggio

    e pertanto ho riscritto la formula nel seguente modo

    =CERCA.VERT(D63;'[K63]ITA C – A’!$F$13:$EM$260;138;FALSO)

  36. Ciao Giovanni. Devi usare la funzione INDIRETTO costruendo al suo interno la stringa con la &. Quindi qualcosa tipo:
    =CERCA.VERT(D63;INDIRETTO(“‘”&K63&”ITA C – A’!$F$13:$EM$260”);138;FALSO)

  37. Grazie per la chiarissima spiegazione!
    Ho bisogno di completare un’analisi urgentemente ma al momento il mio cercaverticale continua a non funzionare come dovrebbe.

    -2 tab dello stesso file in uso. nessuna delle tab dispone di un valore univoco per cui per individuare il “valore da ricercare” ho fatto concatena di tre colonne in entrambe le tab di ricerca – al momento unica apparente differenza sono il “/” invece che “-” nelle date ( che sono parte del concatena)

    -se metto FALSE a fine formula: continuo ad avere #N/A come risultato del cerca verticale e temo il problema sia con il formato dati ( le due tabs da cui sto lavorando sono state scaricate da due software diversi ma sembra che cambio formato nn avvenga anche volendolo applicare)

    -se metto TRUE a fine formula e la trascino, vengono fuori tutti valori uguali ( ma corretti solo per la prima riga)

    La matrice l’avevo bloccata con i dollari quindi nn dovrebbe essere questo il problema. Come si fa in questi casi?
    Grazie mille!
    Sara

  38. Ciao Sara, dovresti lasciare FALSO nel CERCA.VERT e fare in modo che le date siano scritte nello stesso modo, magari modificando il divisore con la funzione SOSTITUISCI, tipo =SOSTITUISCI(A2;”-“;”/”)

  39. Salve avrei bisogno di un aiuto con excel.
    Ho 2 colonne G ed H dove in G ho una lista di giocatori mentre in H ho le squadre,vorrei che in automatico nelle colonne A-B-C-D si generino le squadre.Grazie

  40. Ciao Filippo.

    Ci sono almeno un paio di sistemi, dipende da come strutturati i tuoi dati.

    Una soluzione prevede di utilizzare INDIRETTO per far riferimento nella matrice tabella a più intervalli.

    Un’altra soluzione potrebbe essere quella di “accodare” i dati tramite power query e poi effettuare la ricerca in una sola tabella.

    Se vuoi farmi capire meglio come sono strutturati i tuoi dati mandami pure una sintesi dei tuoi dati su file… samuele@amicoexcel.it

  41. Ciao Samuele,
    per effettuare una ricerca su piu fogli sto usando :
    =SE.ERRORE(CERCA.VERT(I4;’SETTIMANA 01′!F:J;2;FALSO); “0”)
    vorrei trascinarla su piu campi ma senza dover scrivere il nome settimana 01 ,02,03, ecc tutte le volte .
    ho seguito molto di quello detto qui sopra, ho provato con la funzione indiretto ma non riesco a farla funzionare.
    puoi aiutarmi

  42. Ciao Marco,
    io scriverei: =SE.ERRORE(CERCA.VERT(I4;INDIRETTO(“’SETTIMANA “&A2&”′!F:J”);2;FALSO); “0”)
    nell’ipotesi che su A2 si trovi il tuo 01 (scritto dopo aver impostato il formato testo nella cella).

    Fammi sapere o mandami due dati in un file a samuele@amicoexcel.it

Comments are closed.