In diversi articoli precedenti, abbiamo esplorato diversi aspetti della funzione di Excel CERCA.VERT. Se ci avete seguito fin qua, dovreste ormai essere esperti della materia 🙂 .
Tuttavia, non è senza una ragione che molti specialisti di Excel considerano CERCA.VERT come una delle funzioni più complesse di Excel. Ha una tonnellata di limitazioni e specificità, che sono la fonte di vari problemi ed errori.
In questo articolo, troverete semplici spiegazioni dei possibili errori #N/D, #NOME? e #VALORE! che potreste incontrare nell’utilizzo della funzione CERCA.VERT, nonché soluzioni e piccole correzioni. Inizieremo con i casi più frequenti e le ragioni più ovvie per cui un CERCA.VERT potrebbe non funzionare, quindi vi consigliamo di controllare i suggerimenti seguenti nell’ordine in cui li scriviamo.
Gli errori #N/D nel CERCA.VERT di Excel
Nelle formule CERCA.VERT, un messaggio di errore #N/D (che significa “non disponibile”) viene visualizzato quando Excel non riesce a trovare un valore di ricerca. Ci possono essere diversi motivi per cui questo può accadere.
Un refuso o un errore di battitura nel valore di ricerca
È sempre una buona idea controllare la cosa più ovvia prima 🙂 . Gli errori di battitura si verificano di frequente quando si lavora con insiemi molto grandi di dati, costituiti da migliaia di righe, o quando un valore di ricerca viene digitato direttamente nella formula.
#N/D nel CERCA.VERT con corrispondenza approssimativa
Se si utilizza una formula con la corrispondenza approssimativa (argomento intervallo impostato su VERO o omesso), la formula CERCA.VERT potrebbe restituire l’errore #N/D in due casi:
- Se il valore di ricerca è inferiore al valore più piccolo presente nella matrice di ricerca
- Se la colonna di ricerca non è ordinata in ordine crescente.
#N/D nel CERCA.VERT con corrispondenza esatta
Se state effettuando una ricerca con corrispondenza esatta (argomento intervallo impostato su FALSO) e il valore esatto non è stato trovato, anche in questo caso viene restituito l’errore #N/D. Potete vedere maggiori dettagli su come usare correttamente CERCA.VERT con corrispondenza esatta e approssimativa.
La colonna di ricerca non è la colonna più a sinistra di matrice_tabella
Come probabilmente sapete, uno dei limiti più significativi del CERCA.VERT è che non può guardare alla sua sinistra, di conseguenza, la colonna di ricerca deve sempre essere la colonna più a sinistra in matrice_tabella. In pratica, spesso ci dimentichiamo di questo particolare e finiamo per avere un CERCA.VERT non funzionante a causa dell’errore #N/D.
Soluzione: se non è possibile ristrutturare i dati in modo che la colonna di ricerca sia la colonna più a sinistra, è possibile utilizzare una combinazione delle funzioni INDICE e CONFRONTA di Excel, come più versatile alternativa al CERCA.VERT.
I numeri sono formattati come testo
Un’altra fonte di errori #N/D sono i numeri formattati come testo, sia nella tabella principale sia in quella di ricerca.
Questo si verifica in genere quando si importano dati da alcuni database esterni o se avete scritto un apostrofo prima di un numero per mostrare uno zero iniziale.
I segnali più evidenti di numeri che sono formattati come testo sono mostrati nella schermata qui sotto.
I numeri possono anche essere memorizzati nel formato Generale. In questo caso, c’è solo un segnale evidente: i numeri vengono allineati sul lato sinistro della cella, mentre i numeri memorizzati come numeri sono allineati a destra di default.
Soluzione: se si tratta di un singolo numero, è sufficiente fare clic sull’icona di errore e scegliere “Converti in numero” dal menu contestuale.
Se questa anomalia riguarda più numeri, selezionateli tutti, fate clic destro sulla selezione, quindi scegliete Formato celle > scheda Numero > Numero e fate clic su OK.
Spazi iniziali o finali in eccesso
Questa è la causa meno evidente dell’errore #N/D nel CERCA.VERT, in quanto un occhio umano non può certo individuare quegli spazi aggiuntivi, soprattutto quando si lavora con grandi tabelle dove la maggior parte delle voci sono al di sotto della parte visibile.
Soluzione 1: gli spazi aggiuntivi sono nella tabella principale (quella con le formule CERCA.VERT)
Se gli spazi in eccesso si trovano nella tabella principale, è possibile garantire il corretto funzionamento delle formule CERCA.VERT avvolgendo l’argomento valore con la funzione ANNULLA.SPAZI:
=CERCA.VERT(ANNULLA.SPAZI($F2); $A$2:$C$22; 3; FALSO)
Soluzione 2: gli spazi supplementari sono nella tabella di ricerca (colonna di ricerca)
Se gli spazi aggiuntivi si trovano nella colonna di ricerca, non vi è alcun modo semplice per evitare gli errori #N/D. Al posto di CERCA.VERT, è possibile utilizzare una formula matriciale con una combinazione delle funzioni INDICE, CONFRONTA e ANNULLA.SPAZI:
=INDICE($C$2:$C$22; CONFRONTA(VERO; ANNULLA.SPAZI($A$2:$A$22)=ANNULLA.SPAZI($F$2); 0))
Poiché si tratta di una formula matriciale, non dimenticate di premere Ctrl + Maiusc + Invio invece di Invio per completare correttamente l’inserimento:
Gli errori #VALORE! nel CERCA.VERT di Excel
In generale, Microsoft Excel visualizza l’errore #VALORE! se un valore utilizzato nella formula è di un tipo di dati errato. Per quanto riguarda il CERCA.VERT, ci sono due cause abbastanza comuni dell’errore #VALORE!.
Il valore da cercare supera i 255 caratteri
Siate consapevoli del fatto che la funzione CERCA.VERT non riesce a cercare valori che contengano 256 caratteri o più. Se i valori di ricerca superano questo limite, si finirà per ottenere l’errore #VALORE!:
Soluzione: utilizzate l’analoga formula INDICE/CONFRONTA. Nell’esempio precedente, la seguente funzione INDICE/CONFRONTA funziona perfettamente:
=INDICE(C2:C11;CONFRONTA(VERO;INDICE(B2:B11= F$2;0);0))
Non è stato fornito il percorso completo alla cartella di lavoro in cui si trova la tabella di ricerca
Se si stanno cercando dati presenti in un’altra cartella di lavoro, è necessario includere il percorso completo di quel file. Più precisamente, è necessario racchiudere il nome della cartella di lavoro (compresa l’estensione) tra parentesi quadre [], quindi specificare il nome del foglio seguito dal punto esclamativo. Inoltre, dovreste avvolgere questo percorso con degli apostrofi nel caso in cui il nome della cartella di lavoro o del foglio di calcolo contenga spazi.
Ecco la struttura della formula completa per fare un CERCA.VERT da un’altra cartella di lavoro:
=CERCA.VERT(valore; '[nome cartella]nome foglio'!matrice_tabella; indice; FALSO)
Una vera e propria formula potrebbe essere simile a questa:
=CERCA.VERT($A$2; '[Nuovi prezzi.xls]Foglio1'!$B:$D; 3; FALSO)
La formula di cui sopra cercherà il valore della cella A2 nella colonna B di Foglio1 nella cartella di lavoro “Nuovi prezzi”, e restituirà il valore corrispondente dalla colonna D.
Se un qualsiasi elemento del percorso mancasse, la formula CERCA.VERT non funzionerebbe e restituirebbe l’errore #VALORE! (a meno che la cartella di lavoro di ricerca sia attualmente aperta).
Per ulteriori informazioni sulle formule CERCA.VERT che fanno riferimento a un altro file di Excel, vi invito a consultare questo tutorial: Come fare CERCA.VERT da un’altra cartella di lavoro.
L’argomento indice è inferiore a 1
È difficile immaginare una situazione in cui qualcuno potrebbe inserire un numero inferiore a “1” per specificare la colonna dalla quale i valori debbano essere restituiti. Forse, può accadere se questo argomento viene restituito da qualche altra funzione di Excel annidata nella formula CERCA.VERT.
Quindi, anche se dovesse avvenire che l’argomento indice sia inferiore a 1, la formula CERCA.VERT restituirebbe l’errore #VALORE!.
Se l’argomento indice fosse invece maggiore del numero di colonne di matrice_tabella, la formule CERCA.VERT restituirebbe il valore d’errore #RIF!.
Gli errori #NOME? nel CERCA.VERT di Excel
Questo è il caso più semplice: l’errore #NOME? compare se si è accidentalmente scritto male il nome della funzione.
La soluzione è ovvia: controllate l’ortografia 🙂
CERCA.VERT non funzionante (limitazioni, problemi e soluzioni)
Oltre ad avere una sintassi abbastanza complicata, CERCA.VERT ha probabilmente più limitazioni rispetto a qualsiasi altra funzione di Excel. A causa di queste limitazioni, formule CERCA.VERT apparentemente corrette potrebbero produrre risultati diversi da quelli previsti. Qui di seguito troverete le soluzioni per alcuni scenari comuni quando CERCA.VERT non funziona.
CERCA.VERT non fa distinzione tra maiuscole e minuscole
La funzione CERCA.VERT non distingue tra caratteri minuscoli e maiuscoli, li tratta quindi come identici. Quindi, se la tabella include diverse voci simili che differiscono solo nei caratteri maiuscoli o minuscoli, la formula CERCA.VERT restituirà il primo valore trovato a prescindere dal maiuscolo.
Soluzione: utilizzare un’altra funzione di Excel che possa eseguire una ricerca verticale (CERCA, MATR.SOMMA.PRODOTTO, INDICE / CONFRONTA) in combinazione con la funzione IDENTICO che può riconoscere le maiuscole.
CERCA.VERT restituisce solo il primo valore trovato
Come già sapete, CERCA.VERT restituisce il primo valore che trova nella colonna indicata che corrisponda al valore di ricerca. Tuttavia, è possibile costringerlo a restituire il 2°, 3°, 4° o qualsiasi altra occorrenza che desiderate. Se avete bisogno di ottenere tutte le occorrenze del valore di ricerca, dovrete utilizzare una combinazione delle funzioni INDICE, PICCOLO e RIF.RIGA.
Soluzioni: alcune formule di esempio sono state dettagliatamente spiegate in questi articoli:
Una nuova colonna è stata inserita o rimossa dalla tabella
Purtroppo, le formule CERCA.VERT smettono di funzionare ogni volta che una colonna viene eliminata o una nuova viene aggiunta alla tabella di ricerca. Questo accade perché la sintassi della funzione CERCA.VERT richiede che si fornisca l’intera matrice_tabella, così come il numero che indica quale colonna contiene i dati che si desidera restituire. Naturalmente, sia la matrice_tabella sia il numero di colonna cambiano quando si rimuove una colonna esistente o se ne inserisce una nuova.
Soluzione: INDICE / CONFRONTA viene in soccorso ancora una volta 🙂 Nelle formule INDICE & CONFRONTA, si specificano la colonne di ricerca e quella da restituire separatamente, e di conseguenza è possibile eliminare o inserire tante colonne quante se ne desidera, senza preoccuparsi di aggiornare ogni formula CERCA.VERT abbinata.
I riferimenti di cella vengono storpiati quando si copia la formula
L’intestazione di cui sopra dà una spiegazione esauriente della radice del problema, giusto?
Soluzione: utilizzare sempre riferimenti di cella assoluti (con il simbolo $) in matrice_tabella, ad esempio $A$2:$C$100 o $A:$C. Nella barra della formula, è possibile passare rapidamente tra i diversi tipi di riferimento premendo il tasto F4.
CERCA.VERT con SE.ERRORE / VAL.ERRORE
Se non volete intimidire gli utenti con tutti quei messaggi di errore è possibile restituire al loro posto una cella vuota, oppure visualizzare un proprio messaggio. È possibile farlo avvolgendo la formula CERCA.VERT nella funzione SE.ERRORE in Excel 2013, 2010 e 2007 o con SE / VAL.ERRORE nelle versioni precedenti di Excel.
Utilizzare CERCA.VERT con SE.ERRORE
La sintassi della funzione SE.ERRORE è semplice e intuitiva 🙂
SE.ERRORE(valore; se_errore)
Quindi, nel primo argomento si immette il valore per il quale verificare la presenza di un errore, e nel 2° argomento si specifica cosa restituire se si verifica un errore.
Ad esempio, la seguente formula SE.ERRORE / CERCA.VERT restituisce una cella vuota quando il valore di ricerca non viene trovato:
=SE.ERRORE(CERCA.VERT($F$2; $B$2:$C$10; 2; FALSO); "")
Se preferite visualizzare un vostro messaggio, invece dell’errore standard, digitatelo tra le virgolette, come in questo esempio:
=SE.ERRORE(CERCA.VERT($F$2; $B$2:$C$10; 2; FALSO); "Spiacenti, nessuna corrispondenza è stata trovata. Riprova!")
Utilizzare CERCA.VERT con VAL.ERRORE
Poiché la funzione SE.ERRORE è stata introdotta solamente in Excel 2007, nelle versioni precedenti di Excel dovrete utilizzare la combinazione di funzioni SE e VAL.ERRORE in questo modo:
=SE(VAL.ERRORE(formula CERCA.VERT); "Il vostro messaggio"; formula CERCA.VERT)
Ad esempio, ecco la formula SE / VAL.ERRORE / CERCA.VERT analoga alla formula SE.ERRORE / CERCA.VERT più sopra:
=SE(VAL.ERRORE(CERCA.VERT($F$2; $B$2:$C$10; 2; FALSO)); ""; CERCA.VERT($F$2; $B$2:$C$10; 2; FALSO))
È tutto per oggi. Speriamo che questo breve tutorial vi aiuti a far fronte a tutti i possibili errori dei vostri CERCA.VERT e a fare in modo che le vostre formule lavorino nel modo desiderato.
