Il CERCA.VERT è una delle funzioni più note di Excel – e SENZA una buona ragione! Questa funzione fa schifo. Cioè, quello che la funzione compie è utilissimo, solo che lo fa veramente molto male. Sono pochissime le situazioni nelle quali il Cerca Verticale non è totalmente surclassato dalla funzione Indice e Confronta. Il Cerca.Vert fa così schifo che la Microsoft ha sviluppato una nuova funzione apposta per sostituirla, ovvero il Cerca X.

Attualmente la funzione Cerca X è disponibile solo per le ultimissime versioni di Excel. Ma in questa guida andremo a parlare sia della Cerca Verticale sia dei suoi altri (decisamente più efficienti e meritevoli) rivali: il Cerca X, e l’Indice e Confronta appunto.

Conoscere la Cerca Verticale è essenziale perché appena ne vediamo una, la possiamo eliminare e sostituire con una funzione migliore, che non possa causare danni irreparabili alla nostra azienda. So bene che in azienda ti hanno detto che devi usare la cerca verticale, e che devi conoscere solo la cerca verticale. Fin quando hai intenzione di chinare la testa? Credo tu stia leggendo questa guida per elevarti e saperne di più di una persona comune, è arrivato il momento di diventare un vero esperto di Excel. Se sei qui per rasentare la mediocrità hai sbagliato sito. Qui siamo su MasterExcel, non MediocreExcel.

Un vero esperto conosce l’argomento molto più del comune interlocutore, e soprattutto, un vero esperto, non ha paura nel fare quello che gli viene richiesto di fare, portando a termine il lavoro in un modo diverso, migliore, e più efficiente. Tu hai mai visto un esperto che ti da ragione su tutto? Ma che esperto è se ne sa quanto te? Con questa guida vedrai tutti i difetti del cerca verticale e saprai come porre rimedio alla incuria e alla pigrizia dei tuoi colleghi.

La facilità di utilizzo del CERCA.VERT è eguagliabile solo alla sua rigidità e capacità nel creare problemi, errori, ed ambiguità.

• Vorresti imparare le basi del CERCA.VERT passo dopo passo?
• La tua formula CERCA.VERT non funziona?
• Ti sei stancato di questa funzione e vuoi qualcosa di più?

Se la risposta è Sì, allora sei venuto nel posto giusto!

Questa è la Guida Completa per imparare ad utilizzare il CERCA.VERT e i suoi molto più efficienti sostituti!

Indice

1. Avvertimenti
2. Cerca Verticale Passo Passo
3. Il Cerca Verticale Non Funziona?
4. Indice e Confronta
5. Funzione Cerca X

 

Avvertimenti

“Un CERCA.VERT cerca qualcosa in una serie di celle e restituisce a sua volta qualcos’altro che è situato nella stessa riga del valore che si sta cercando.” Detto questo iniziamo con gli avvertimenti:

Avvertimento 1

Il valore che si sta cercando, non può avere duplicati. In altre parole, deve essere un valore univoco ed identificativo. Se ci sono ripetizioni non è univoco. Se è scritto male perché il tuo collega ha messo uno spazio alla fine della parola (e lo spazio non si vede), oppure c’è stato un errore di battitura o qualsiasi altro problema, il cerca verticale non fa quello che vorresti.

Questo vale per qualsiasi funzione, per il cerca x, per l’indice e confronta, ecc. Sono tutte funzioni basate sulla strutturazione appropriata di dati. E di per loro non possono porre rimedio all’uso sbagliato di Excel da parte degli utenti.

Avvertimento 2

Nel cerca verticale, la ricerca avviene solo ed esclusivamente in verticale. Ovvio. Per cercare in orizzontale puoi utilizzare il cerca orizzontale, il cerca x o l’indice e confronta. Per cercare contemporaneamente sia in verticale sia in orizzontale, puoi usare l il cerca x, o l’indice e confronta.

Avvertimento 3

Nel cerca verticale il valore da cercare deve per forza essere nella prima colonna della matrice (intervallo di dati – lo vediamo tra poco). Il che implica che la colonna (dico colonna e non riga perché cerca solo in verticale quindi deve essere tutto incolonnato) contenente il codice univoco ed identificativo (da cercare), deve sempre e comunque essere a sinistra del valore da restituire. Questa non è sempre la situazione che hai di fronte, e costituisce una forte limitazione della funzione Cerca Vert.

Avvertimento 4

Occorre impostare un indice numerico (intero – 1,2,3,4, ecc) per dire al cerca verticale di restituire un valore in una data colonna (all’interno della matrice). Ora non importa che tu lo sappia fare, lo vediamo dopo, qui il punto è che se sbagli contando, se sbagli digitando, se c’è una colonna nascosta e non te ne accorgi, se ci sono una ventina o trentina di colonne, con il cerca verticale avrai sicuramente problemi.

Avvertimento 5

Nel Cerca Vert, l’indice, che è importantissimo perché dice al cerca verticale da quale colonna deve prendere i valori da restituirci, non si aggiorna. Cioè, non si aggiorna proprio per niente! Facciamo un esempio, hai scritto indice 4. Quindi il cerca verticale prende la quarta colonna nella tua matrice. Se per caso, lavorando elimini una colonna, sposti una colonna, o aggiungi una colonna, all’interno della tua matrice (cose che capitano), il cerca verticale non si aggiorna e ti restituisce un valore che originariamente non doveva restituire.

Questo di solito causa problemi non visibili immediatamente. Normalmente ti accorgi dell’errore solo dopo che è successo il disastro. Questo è il motivo principale per cui dico: NON USARE IL CERCA.VERT E SE LO VEDI METTI UN INDICE E CONFRONTA O UN CERCA X!
Altrimenti dovresti entrare in ogni singolo cerca verticale e modificare l’indice (il numerino) a mano per ogni modifica apportata alle colonne. Il che, nella mia azienda è inaccettabile! Impiegati occupati a non produrre? Siamo pazzi!

Avvertimento 6

Se la leggi, non la capisci. Ovvero se leggi una funzione cerca verticale non capisci cosa stia facendo. Quando utilizzi una funzione di questo tipo, di solito, stai compiendo azioni complesse, ed è molto utile poter leggere la funzione e capire subito cosa stia cercando e dove. L’indice e confronta e il cerca x sono tremendamente più comprensibili del cerca verticale!

Non solo, con la gestione nomi (funzione molto professionale) possiamo rinominare interi campi, righe, celle, o colonne. Più avanti vediamo degli esempi. Il cerca verticale di fatto non è compatibile con i campi rinominati dalla gestione nomi. Mentre indice e confronta e cerca x sono totalmente compatibili con i campi rinominati, e se si usano dei nomi appropriati sono comprensibili quanto l’italiano.

Per aiutarti a capire meglio le capacità di CERCA.VERT, e delle funzioni che lo sostituiscono, abbiamo creato un File esempio. Con questi dati tra le mani andremo a creare uno strumento che restituisce i dati che vogliamo utilizzando le funzioni sopracitate. Quindi scarica il file qui sotto:

 

 

*Ti suggerisco di scaricare il nostro File esempio proprio QUI SOPRA, poiché questa guida sarà basata su una serie di dati forniti proprio nel File esempio.

Lo hai scaricato e aperto? Ottimo, ora sei pronto per iniziare!

Cerca Verticale Passo Passo

Supponiamo di dover cercare lo stipendio di uno dei dipendenti. Sarebbe una perdita di tempo cercare manualmente il suo nome nella colonna A per poi digitare o copiaincollare, sempre manualmente, il suo stipendio nella cella dentro la quale hai bisogno che sia presente. Il CERCA.VERT può svolgere questo lavoro per te.

Costruiamo uno strumento nel quale possiamo inserire il nome di un dipendente e vedere poi automaticamente il suo stipendio, tutto nel giro di pochi secondi. Il processo per costruire un meccanismo del genere è suddiviso in 6 (semplici) step. Previo l’operare con dati ben organizzati e strutturati, ogni volta che avrai bisogno della funzione CERCA.VERT, ricordati che con tutta probabilità sarebbe molto meglio usare o l’Indice e Confronta o il Cerca X (quest’ultimo forse non è disponibile nella tua versione).

Ecco cosa devi fare per inserire un Cerca Verticale, in effetti lo vediamo solo perché concettualmente è analogo alle altre due funzioni:

1. Vai nella cella dove vuoi inserire la formula
2. =CERCA.VERT(inserisci quello che stai cercando;
3. Dove lo stai cercando;
4. Cosa ti deve restituire;
5. Vuoi un valore preciso? Di solito si. Altrimenti puoi ricevere un valore approssimato
6. Chiudi parentesi, Invio. Fatto

Sono solo 6 semplici passi. Nulla di oltremodo astruso. Le difficoltà più comuni insorgono nei dati. Ho qualche centinaio di persone all’anno (che non avendo seguito i miei corsi) non sanno come strutturare i dati in maniera appropriata. Se vuoi imparare la causa di e soluzione a tutti i problemi di Excel: clicca qui (Materiale Gratuito).

L’altro errore tipico per il quale vengo contattato sono le persone che cercano un leopardo in un pagliaio. Di nuovo, non hanno seguito i miei corsi e chissà come hanno imparato Excel, da chi, o quando… A tuo giudizio, puoi trovare un leopardo in un pagliaio? Io direi che un leopardo sta nella savana, non in un pagliaio. Ricevo spesso email da persone che cercano qualcosa che non c’è, ed ovviamente Excel restituisce errore #N/D. Si suppone che quello che voglia trovare sia dove lo stai cercando. Inoltre, Excel non comprende gli errori di battitura, i valori (testi o numeri) non devono avere errori o varianti. Presta un po’ di attenzione, perché è molto probabile che ci sia qualche errore di battitura, uno spazio finale o simili all’interno dei tuoi dati.

Fortunatamente, questo processo non è così sofisticato come potrebbe sembrare. Ora passiamo al nostro File esempio:

 

La formula è la seguente:

=CERCA.VERT(F2;B:D;3;FALSO)

Dove F2 è la cella che contiene il valore da cercare. Le colonne dalla B, C, e la D costituiscono tutte assieme la matrice, che ricordo deve contenere il valore univoco (quello da cercare) nella prima colonna (di se stessa, B in questo caso). Lo rispiego? Non importa quale sia la prima colonna nel foglio di lavoro. Per la funzione Cerca Verticale, l’importante è solo la matrice, ripeto: non il foglio di lavoro. Nella matrice, che non è il foglio di lavoro, ma in questo esempio la matrice è costituita dalle colonne dalla B, C e D, la posizione 1 è occupata dalla colonna B, nella posizione 2 abbiamo la C, ed infine in posizione 3 abbiamo la colonna D. Questo che ho appena detto, so che può apparire controintuitivo soprattutto all’inizio, ma l’unica cosa che conta è la matrice specificata, non il foglio di lavoro. Prima di proseguire dobbiamo farci qualche domanda: all’interno della matrice selezionata, abbiamo il nome (e cognome) che dobbiamo cercare in posizione 1? Si? Bene, possiamo continuare. Cosa vogliamo che ci venga restituito? Il salario. In che posizione è (all’interno della matrice e non all’interno del foglio di lavoro)? All’interno della matrice il salario è in posizione 3. E per finire, specifichiamo la corrispondenza esatta scrivendo: FALSO.

Rileggendo quanto ho appena scritto mi accorgo che può essere macchinoso. Ma non ti preoccupare, ti serve solo un po’ di pratica. E forse, troverai più facili le prossime due funzioni. Riprendiamo la formula:

=CERCA.VERT(F2;B:D;3;FALSO)

Questa formula cerca il valore contenuto in F2, dentro una matrice (da B a D), restituendo come risultato un valore nella terza colonna della matrice (quindi in colonna D), ed esegue il tutto con corrispondenza esatta (quindi se non trova l’esatto valore contenuto in cella F2 all’interno della prima colonna della matrice – colonna B – la funzione restituisce errore #N/D).

Rispiego in dettaglio? Si dai!

Step 1: Una Cella per la Tua Formula

La maggior parte delle persone inserisce le formule in celle a caso. Non essere quella persona! Trovo che queste azioni portino generalmente al “caos del foglio elettronico” (niente di positivo) ed è in ultima analisi la causa di tutti i problemi di Excel. Perché?

Perché i calcoli e l’archiviazione dei dati vengono mischiati, ed impostati per non poter sfruttare le funzioni di Excel che altrimenti potrebbero semplicemente tremendamente il lavoro. Puoi porre rimedio a dati sbagliati, ma generalmente è più semplice riiniziare da capo quando hai un foglio preparato coi piedi.

Se non vuoi iniziare tutto da capo tra qualche ora (o tra qualche giorno, o anche tra qualche mese) dopo aver lavorato sodo o se hai intenzione di lavorare a lungo su un foglio particolare – parti sin da subito con l’impostazione giusta! Struttura i dati e i calcoli con attenzione.

Vi sono diversi modi per farlo, alcuni un po’ più complicati (o efficaci) di altri. A seguire troverai il mio approccio per creare un CERCA.VERT, un approccio che è stato perfezionato negli anni e che per me rimane la soluzione più efficiente. Approccio che possiamo applicare anche al Cerca X e all’Indice e Confronta. Facciamo il primo passo.

Seleziona la cella dove vuoi che il risultato della tua formula (la funzione CERCA.VERT) sia mostrato. Andremo a cercare un valore con una funzione di ricerca, perciò includi anche una cella da utilizzare per inserire il dato da cercare.

Prima o dopo aver inserito il valore da cercare e la formula, ricordati di mettere sopra ciascuna cella l’intestazione! Intestazione chiara, non ambigua, questa aiuterà tutti i tuoi colleghi che non avendo letto questa guida, brancolano beato buoi dell’ignoranza.

Inoltre, per evitare ulteriori ambiguità, ti consiglio di inserire anche i bordi attorno a queste 4 celle (1 con il valore da cercare, una con la funzione cerca verticale, e 2 di intestazione). Questo lo facciamo per far capire a chi guada il file che queste 4 celle sono staccate dai dati e costituiscono un blocco unico. Puoi aggiungere il bordo dalla Home (in alto sulla Barra Multifunzione), non credo tu abbia difficoltà in questo. Per evitare dubbi ho messo una bella freccia rossa nell’immagine sottostante:

 

Nota come grazie al bordo e alle intestazioni è chiaro che le 4 celle siano decisamente staccate da tutto il resto. Puoi anche scegliere una struttura analoga applicando gli stessi elementi: bordi ed intestazioni chiare.

È stato piuttosto semplice, vero? È arrivato il momento di proseguire ed affrontare il prossimo step!

 

Step 2: Cosa Stai Cercando

Ora è il momento di analizzare quello che stai cercando tramite la formula cerca verticale e quanto diremo per questa formula vale anche per qualsiasi altra funzione di ricerca analoga; le già citate cerca orizzontale, cerca x, e indice e confronta. L’unica cosa che cambia tra queste funzioni è la sintassi. “Sintassi” è solamente una brutta parola della quale non ne capiamo tanto il significato a meno che non l’abbiamo già incontrata in precedenti esperienze. In realtà, ciò che significa veramente è molto semplice:

“La sintassi è l’ordine, la combinazione degli argomenti che dobbiamo rispettare all’interno di una funzione per farla funzionare”

Una funzione CERCA.VERT necessità di questi 4 input per poter lavorare:

• Ciò che stai cercando, può essere un valore in una cella o un valore inserito nella funzione
• La matrice, che nella prima colonna (di se stessa e non del foglio di lavoro) deve contenere il valore da cercare
• La colonna contiene il valore da cercare, espressa con il numero della posizione all’interno della matrice (e non della posizione all’interno del foglio di lavoro)
• Un campo facoltativo, non so cosa tu stia facendo ma credo proprio tu debba eseguire una ricerca per corrispondenza esatta

Quando inizi ad inserire la formula in Excel, ci sarà lo screen tip ad aiutarti nell’inserimento dei dati. Tutti questi input inseriti nella formula CERCA.VERT devono essere separati da un punto e virgola (come in tutte le funzioni nella versione italiana). Ogni volta che si inserisce un punto e virgola, il riquadro dei suggerimenti (screen tip) ti dirà a che punto sei nella formula, evidenziando in grassetto la parte corrente della sintassi e facilitando la scrittura della formula. Ora però, basta con i tecnicismi! Immagina che la funzione CERCA.VERT faccia più o meno le stesse cose che faresti tu quando vuoi cercare qualcosa.

Nell’esempio precedente, abbiamo cercato per nome (e cognome) e se ci fesse un caso di omonimia (una ripetizione), non potremo utilizzare una funzione Cerca Verticale, Indice e Confronta o Cerca X. Inoltre, dobbiamo stare attenti ad errori di battitura.

Step 3: Dove stai Cercando

Questo è lo step dove specifichiamo dove cercare il nostro valore. Quando stai cercando manualmente, utilizzando i tuoi occhi e non le tue abilità su Excel, dove stai guardando? Dobbiamo dire ad Excel di “guardare” in quella colonna. O meglio, nel cerca verticale dobbiamo specificare la matrice contenente i dati, la prima colonna di questa matrice deve contenere il valore da cercare. Non deve essere per forza la prima colonna del foglio di lavoro, ma la colonna con il valore da cercare deve per forza essere alla sinistra del valore da restituire. Altre funzioni hanno altre sintassi, molto più flessibili.

Quindi, nel cerca verticale, seleziona come pima colonna quella con il valore da cercare, ed estendi la matrice verso la tua destra fino a dove vuoi tu. Il resto lo vediamo nel prossimo step. Comunque l’aspetto della matrice dovrebbe essere più o meno questo:

 

Step 4: Cosa deve restituire

Quando utilizzi una formula CERCA.VERT vuoi sicuramente una risposta alla tua ricerca. Ciò che vuoi che ti venga restituito è ciò che Excel chiama indice, e che nel Cerca Verticale è un numero intero. In breve: l’indice è la colonna contenente i dati da restituire.

Troviamo l’indice in altre funzioni, come l’Indice e Confronta appunto, ma qui nel Cerca Verticale l’indice è un numero. Ovvero la posizione all’interno della matrice della colonna avente i dati da restituire. La posizione è quindi relativa alla matrice e non è data dal foglio, questo lo ripeterò spesso. Oltre tutto deve essere per forza a destra della prima colonna della matrice avente i dati da cercare. E l’indice del Cerca Verticale, non si aggiorna! Non automaticamente, lo puoi aggiornare solo manualmente.

 

La colonna A è fuori dalla matrice, quindi per la funzione Cerca Verticale che ho scritto è come se non esistesse. La colonna B è in posizione 1, colonna C in posizione 2, e colonna D in posizione 3.

Se cerco per nome non posso fare altrimenti, perché la prima colonna della matrice deve contenere il valore da cercare. Ormai credo sia ovvio il fatto che il Cerca Verticale non sia la scelta migliore. Altre funzioni hanno sintassi molto più facili e flessibili, ma per ora passiamo al prossimo step.

 

Step 5: Preciso o Approssimato?

Questa opzione è comune un po’ a tutte le funzioni di ricerca, cambia solo la sintassi. Taglio la testa al toro. Preciso. Corrispondenza Esatta.

Sono pochissimi i casi nei quali ha senso fare una ricerca approssimata e sono tutti casi nei quali si esegue una ricerca per numero. Numero, non codice alfanumerico, ma valore numerico, numero vero e proprio. Cerchi per testo? Preciso. Cerchi per codice alfanumerico? Preciso. Cerchi per codice numerico? Preciso. Cerchi per nome e cognome? Preciso.

Nella cerca verticale si imposta semplicemente scrivendo FALSO come ultimo argomento.

Come Regola Generale: imposta sempre la corrispondenza esatta! E questo vale per tutte le funzioni analoghe alla cerca verticale.

I casi nei quali occorre una corrispondenza approssimata sono molto poco comuni e tendono ad essere solo causa di un susseguirsi di errori.

Step 6: Invio

Cosa facciamo generalmente per completare una formula? Premiamo Invio. Ed è esattamente quello che dovresti fare ora! Nel momento in cui premiamo “Invio” non sappiamo in realtà se la formula funzioni o meno. Dobbiamo inserire un valore nella cella che la funzione utilizza per sapere cosa cercare.

Se non hai inserito nulla nella cella, la funzione ti restituirà errore #N/D. Questo è comune a tutte le funzioni di ricerca, e significa solo che Excel non ha trovato nulla. Questo errore capita anche se sbagli digitando, se ci sono spazi (invisibili) iniziali o finali, o quando semplicemente quello che stai cercando non è in elenco. Non devi quindi spaventarti quando vedi questo errore.

Congratulazioni!
Hai appena creato uno strumento per cercare lo stipendio di una persona in una tabella degli impiegati con la funzione Cerca Vert!

Prova ad inserire il nome di altre persone e assisti alla potenza del tuo nuovissimo strumento. Sfortunatamente, a volte, qualcosa può andare storto con la CERCA.VERT. Come puoi rimediare?

Continua a leggere e impara a risolvere i problemi della formula CERCA.VERT.

Il Cerca Verticale Non Funziona?

Possono esserci molteplici ragioni per le quali la formula CERCA.VERT non funziona nel modo in cui vorresti. La maggior parte degli errori derivano dal dimenticare le dinamiche di funzionamento e/o non vi è corrispondenza dei dati. Tutto ciò provocherà un errore (la maggior parte delle volte #N/D or #RIF). Di seguito ti mostrerò le cause più comuni per le quali la formula CERCA.VERT non funziona come dovrebbe.

• Il famigerato errore #N/D.

1. Errori di battitura, invisibili spazi iniziali o finali, simboli che sembrano spazzi o punti ma non lo sono nel termine di ricerca
2. Errori di battitura, invisibili spazi iniziali o finali, simboli che sembrano spazzi o punti ma non lo sono nei dati da cercare
3. Errori dovuti alla formattazione
4. Hai selezionato la matrice sbagliata, o hai impostato l’indice sbagliato

• L’errore #RIF:

1. La colonna di indice è maggiore del numero delle colonne.
2. I riferimenti nella formula CERCA.VERT fanno riferimento a celle che non esistono più.

Il Famigerato errore #N/D

Quando la tua formula CERCA.VERT restituisce #N/D significa che il valore che stai cercando non è disponibile. Non temere! Uno dei prossimi step riparerà la tua formula.

Errori di Battitura e simili

Parliamo ora di come risolvere uno dei problemi più frequenti. Quanto sto per dire vale per tutte le funzioni di ricerca. Partiamo con gli errori di battitura, e simili, nel valore che stai cercando, quello inserito nella cella di riferimento.

Errori nella cella di riferimento

Per risolvere gli errori di battitura (o simili) all’interno della cella che contiene il termine da cercare, devi solo controllare i tuoi dati. Per prevenire gli errori di battitura all’interno di questa cella, inserisci un menu a tendina. Se non sai come inserire un menu a tendina su Excel: clicca qui.

Errori nella colonna che contiene i dati da cercare

Spesso abbiamo errori di battitura (o simili) all’interno della colonna contenente i dati da cercare. Per risolverli non c’è un modo semplice o universale. Certo puoi controllare tutti quanti i dati manualmente, oppure utilizzare delle funzioni Excel per semplificarti il lavoro. Ti rimando all’articolo già citato in precedenza sui menu a tendina dove mostro come utilizzare il Rimuovi Duplicati, il che ti farà individuare gli errori con rapidità. Altre funzioni utili che non credo tu abbia problemi ad utilizzare per correggere i tuoi dati sono: l’Annulla Spazi, e la funzione Sostituisci.

Problemi dovuti alla formattazione

Spesso non ti accorgi che la funzione ti ha restituito esattamente quello che volevi, ma in un formato differente. Oppure, hai inserito qualcosa ma nel formato sbagliato. Come si risolve? Facile, dai un’occhiata alla Barra Multifunzione e assicurati di applicare i formati corretti.

Se in una lista di numeri stai cercando dei numeri, dovresti sempre assicurarti che essi non siano espressi in formato testo. Di tanto in tanto questo può accadere ma, allo stesso tempo, può essere facilmente risolto. Excel identificherà i numeri presentati nel formato testo e lo segnalerà con una piccola icona di avviso. Se clicchi sopra questo avviso, puoi permettere ad Excel di convertire il testo in numero con un semplice click. Altrimenti puoi cambiare il formato manualmente dalla barra, o con tasto destro formato cella.

Matrice sbagliata

Selezionare la matrice sbagliata è un errore comune. Ricontrolla la formula ed esegui le dovute correzioni. Oppure, cambia funzione e mettine una più dinamica (vediamo le altre funzioni tra poco).

L’Errore #RIF

Questo tipo di errore deriva da uno dei problemi seguenti.

L’indice è maggiore del numero delle colonne

Se Excel ti restituisce errore #Rif, la causa principale è che l’inserimento di un indice troppo grande. Di solito questo capita quanto si conta a partire dalla colonna A, e non a partire dalla prima colonna della matrice. Quindi devi solo fare un po’ di attenzione ed inserire l’indice corretto.

Quando impostiamo su Excel il valore da cercare, di solito lo inseriamo in una cella. Quando utilizziamo il nostro strumento CERCA.VERT spesso lo “progettiamo” in modo che sia la funzione sia il valore di riferimento risultatino (fisicamente) molto vicini tra loro sul nostro foglio elettronico.

Facciamo ciò per risparmiare un po’ di tempo e per non perderne quando cerchiamo il risultato con la formula. Questo significa che abbiamo la tendenza a dimenticare dove CERCA.VERT guarda realmente per “tirar fuori” i dati.

Se la matrice (il posto dal quale CERCA.VERT prende il risultato) è per qualche motivo cancellata o spostata, allora la nostra formula CERCA.VERT restituirà un errore #RIF perché un riferimento nella formula non funziona più.

Anche se inserisci dei nuovi numeri nella matrice, la formula continuerà a non funzionare in quanto i riferimenti sono andati distrutti. Per provocare questo errore, basta solamente selezionare una colonna della matrice e cancellarla.

Analogo problema lo abbiamo quando inavvertitamente cancelliamo la cella di riferimento, quella contenente il valore da cercare. Anche in questo caso, i riferimenti vengono distrutti ottenendo così un errore #RIF!

Cancellando semplicemente il contenuto delle celle, otterrai un errore #N/D – poiché i valori non possono essere più trovati. Se cancelli letteralmente la cella o la colonna, otterrai #Rif. Ecco come puoi risolvere o prevenire questi problemi, che sono comuni anche con le funzioni che vedremo tra poco.

Per poter risolvere l’eliminazione, devi solo recuperare i dati originali (se sono ancora recuperabili), inserire i dati in maniera appropriata, e infine rientrare nella formula per inserire i nuovi riferimenti.

Per prevenire problemi del genere, devi mettere un blocco. Vai sulla Barra Multifunzione> Revisione> Proteggi foglio di lavoro> e se vuoi inserisci una password.

Complimenti! Ora sai davvero tutto sul cerca verticale!

Abbiamo parlato tantissimo di questa funzione e lo abbiamo fatto perché tutte le dinamiche di utilizzo sono comuni anche con le altre funzioni che sto per mostrarti. Quindi, senza indugi, vediamo l’Indice e Confronta!

Indice e Confronta

Questa non è una funzione, bensì sono due funzioni nidificate tra loro. L’Indice e il Confronta appunto. Tra gli anglofoni è conosciuta come: Index Match. Presenta molti vantaggi rispetto al Cerca Verticale. Prima di vederli, vediamo come possiamo scrivere questa funzione:

 

 

Ed ecco la formula:

=INDICE(D:D;CONFRONTA(F7;B:B;0))

L’indice si inserisce all’inizio, e non è un numero relativo alla posizione di una selezione iniziale, è una colonna, una selezione di celle, o riga. Dico riga perché questa funzione può cercare sia in verticale sia in orizzontale, eventualmente anche contemporaneamente.
Il valore da cercare in questo esempio è in cella F7, nulla di nuovo.
Colonna dove cercare il valore, colonna B, chiara e visibile.
Lo Zero indica la corrispondenza esatta, qua si indica con lo 0, nel Cerca Verticale con FALSO, è solo una differenza di sintassi.

Ora vediamo cosa succede con la gestione nomi:

 

Ho rinominato i campi e ho assegnato ad essi dei nomi abbastanza chiari. Basta leggere la funzione ora per capire che restituisce lo stipendio confrontando il valore da cercare tra i nomi (con corrispondenza esatta). Con il Cerca Verticale dimenticati una cosa del genere!

Con l’Indice e Confronta, abbiamo i campi che si aggiornano in automatico, quindi se sposto, aggiungo o elimino delle colonne (senza eliminare le colonne utilizzate nella formula ovviamente) tutta la formula si aggiorna in automatico! Scordati una cosa del genere con il Cerca Verticale! E questa dinamicità non ha nulla a che vedere con la gestione nomi, l’Indice e Confronta lo fa di default.

Ci sono tanti altri motivi per non usare il Cerca Verticale.
Vuoi saperne di più sul Cerca Verticale vs. Indice e Confronta? Guada questo video! (gratis)
Per ulteriori approfondimenti: vai qui. (gratis)

Funzione Cerca X

Concludiamo con il Cerca X. Questa funzione non è disponibile in tutte le versioni, ma ne ho potuto testare le potenzialità in anteprima e devo dire che sicuramente eliminerà la Cerca Verticale in futuro.

Quindi vediamo questa funzione in attesa (nella speranza) che la pubblichino in tutte le versioni di Excel più recenti con i prossimi aggiornamenti.

 

Vediamo questa fantastica funzione:

=CERCA.X(F10;Nomi;Stipendio;”non lo trovo”;0)

Ecco la sua sintassi:

= CERCA.X (valore da cercare; matrice che contiene il valore; campo che contiene i dati da restituire; se non lo trova; modalità di confronto [di solito corrispondenza esatta – 0]; modalità di ricerca [ordine – dal primo all’ultimo di solito])

È possibile che quando la pubblicheranno possano cambiare alcune cose. Comunque io la trovo perfetta, non solo ha tutti quanti i pregi del Cerca Verticale, e tutti quanti i pregi dell’Indice e Confronta (si, potenzialmente anche il Cerca X può cercare sia in verticale sia in orizzontale anche contemporaneamente, i campi si aggiornano in automatico, e via discorrendo), ma il Cerca X ha anche qualcosa in più! Ovvero possiamo specificare cosa avviene se non trova il valore! Bellissimo! Come se avessero integrato la Se Errore al suo interno.

Il Cerca X funziona benissimo con i campi rinominati, il che è molto utile. Offre una nuova opzione ovvero la modalità di ricerca: dall’alto verso il basso (che è la predefinita e quella più utilizzata), dal basso verso l’alto, e due tipi di ricerche binarie (che non credo ti occorrano). Quando hanno rilasciato la Cerca X ufficialmente per la versione 365 ho esultato! Questa funzione è un cambiamento epocale in Excel.