Questo argomento descrive i motivi più comuni di un risultato errato nella funzione e fornisce suggerimenti per l'uso di INDICE e CONFRONTA .
Suggerimento: Vedere anche i suggerimenti per la risoluzione dei problemi nella Scheda di riferimento rapido: CERCA.VERT che presenta i motivi comuni per problemi #N/D in un pratico file PDF. È possibile condividere il file PDF con altri utenti o stamparlo per riferimento.
Problema: il valore di ricerca non è presente nella prima colonna nell'argomento matrice_tabella
Una delle principali limitazioni di CERCA.VERT è che può cercare solo i valori nella colonna più a sinistra nella matrice di tabella. Quindi se il valore di ricerca non è presente nella prima colonna della matrice, verrà visualizzato l'errore #N/D.
Nella tabella seguente, si vuole recuperare il numero di unità di cavoli vendute.
L'errore #N/D viene restituito perché il valore di ricerca “Cavolo riccio” viene visualizzato nella seconda colonna (Prodotti agricoli) dell'argomento matrice_tabella A2:C10. In questo caso, Excel lo cerca nella colonna A, non nella colonna B.
Soluzione: è possibile provare a risolvere il problema modificando la funzione CERCA.VERT in modo che faccia riferimento alla colonna corretta. Se ciò non fosse possibile, provare a spostare le colonne. Ciò potrebbe essere inattuabile in presenza di fogli di calcolo grandi o complessi, in cui i valori delle celle sono risultati di altri calcoli oppure potrebbero esserci motivi logici per cui non è possibile spostare le colonne. La soluzione consiste nell'usare una combinazione delle funzioni INDICE e CONFRONTA per cercare un valore in una colonna indipendentemente dalla relativa posizione nella tabella di ricerca. Vedere la sezione successiva.
Provare a usare invece la funzione INDICE/CONFRONTA
INDICE e CONFRONTA è una buona opzione per molti casi in cui CERCA.VERT non soddisfa le proprie esigenze. Il vantaggio principale di INDICE/CONFRONTA è che è possibile cercare un valore in una colonna in un punto qualsiasi della tabella di ricerca. INDICE restituisce un valore da una tabella o un intervallo specificato in base alla relativa posizione. CONFRONTA restituisce la posizione relativa di un valore in una tabella/intervallo. L'uso congiunto delle funzioni INDICE e CONFRONTA in una formula consente di cercare un valore in una tabella/matrice specificando la posizione relativa del valore nella tabella/matrice.
L'uso di INDICE/CONFRONTA presenta diversi vantaggi rispetto a CERCA.VERT:
-
Con le funzioni INDICE e CONFRONTA il valore restituito non deve trovarsi nella stessa colonna della colonna di ricerca. Funziona in modo diverso rispetto a CERCA.VERT, in cui il valore restituito deve essere compreso nell'intervallo specificato. Perché è importante? Con CERCA.VERT, occorre conoscere il numero di colonna che contiene il valore restituito. Nonostante ciò non sembri un problema, può essere un'operazione complessa in presenza di una tabella di grandi dimensioni in cui occorre contare il numero di colonne. Se poi si volessero aggiungere/rimuovere valori da una tabella, sarebbe necessario conteggiare nuovamente e aggiornare l'argomento indice. Con le funzioni INDICE e CONFRONTA non è richiesto alcun conteggio perché la colonna di ricerca è differente dalla colonna che contiene il valore restituito.
-
Con le funzioni INDICE e CONFRONTA è possibile specificare una riga o una colonna in una matrice o persino entrambe. Questo significa che è possibile cercare valori in verticale e orizzontale.
-
È possibile usare le funzioni INDICE e CONFRONTA per cercare valori in qualsiasi colonna. A differenza di CERCA.VERT, che permette di cercare un valore solo nella prima colonna di una tabella, le funzioni INDICE e CONFRONTA funzioneranno se il valore di ricerca si trova nella prima colonna, nell'ultima o in qualsiasi colonna intermedia.
-
Le funzioni INDICE e CONFRONTA offrono la flessibilità di effettuare riferimenti dinamici alla colonna contenente il valore restituito.Ciò significa che è possibile aggiungere colonne alla tabella senza interrompere INDICE e CONFRONTA. D'altro canto, la funzione CERCA.VERT restituisce un errore se si è reso necessario aggiungere una colonna alla tabella perché esegue un riferimento statico alla tabella.
-
INDICE e CONFRONTA offre più flessibilità con le corrispondenze. INDICE e CONFRONTA possono trovare una corrispondenza esatta o un valore maggiore o minore del valore di ricerca. La funzione CERCA.VERT cerca solo la corrispondenza più prossima a un valore, per impostazione predefinita, oppure un valore esatto. Inoltre, per impostazione predefinita, CERCA.VERT presuppone che la prima colonna della matrice di tabella sia in ordine alfabetico e, se la tabella non è configurata in questo modo, verrà restituita la prima corrispondenza più vicina nella tabella, che potrebbe non corrispondere ai dati che si stanno cercando.
Sintassi
Per creare una sintassi per le funzioni INDICE/CONFRONTA, è necessario usare l'argomento matrice/riferimento dalla funzione INDICE e annidare la sintassi di CONFRONTA al suo interno, in modo simile al seguente. Il formato è il seguente:
=INDICE(matrice o riferimento, CONFRONTA(valore,matrice,[corrisp])
Per esempio, usare INDICE/CONFRONTA per sostituire CERCA.VERT nell'esempio precedente. La sintassi sarà simile alla seguente:
=INDICE(C2:C10,CONFRONTA(B13,B2:B10,0))
In poche parole, ciò significa che:
=INDICE(restituisce un valore da C2:C10 che CONFRONTA(Cavolo riccio, che si trova da qualche parte nella matrice B2:B10, dove il valore restituito è il primo valore corrispondente a Cavolo riccio))
La formula cerca il primo valore in C2:C10 che corrisponde a Cavolo riccio (in B7) e restituisce il valore in C7 (100), cioè il primo valore che corrisponde a Cavolo riccio.
Problema: non viene trovata la corrispondenza esatta
Quando l'argomento intervallo è FALSE e la funzione CERCA.VERT non trova una corrispondenza esatta nei dati, viene restituito l'errore #N/D.
Soluzione: se si è certi che i dati pertinenti esistano nel foglio di calcolo e la funzione CERCA.VERT non li trova, assicurarsi che le celle di riferimento non contengano spazi nascosti o caratteri non stampabili. Assicurarsi anche che le celle seguano il tipo di dati corretto. Ad esempio, le celle con numeri devono essere formattate come Numero e non come Testo.
Usare la funzione LIBERA o ANNULLA.SPAZI per pulire i dati nelle celle.
Problema: il valore di ricerca è minore del valore più piccolo nella matrice
Se l'argomento intervallo è impostato su TRUE e il valore di ricerca è minore del valore più piccolo nella matrice, verrà visualizzato l'errore #N/D. TRUE cerca una corrispondenza approssimativa nella matrice e restituisce il più vicino valore minore del valore di ricerca.
Nell'esempio seguente, il valore di ricerca è 100, ma nell'intervallo B2:C10 non sono presenti valori inferiori a 100, da cui l'errore.
Soluzione:
-
Correggere il valore di ricerca in base alle esigenze.
-
Se non si può modificare il valore di ricerca ed è necessaria una maggiore flessibilità con i valori corrispondenti, prendere in considerazione l'uso di INDICE e CONFRONTA anziché CERCA.VERT. Vedere la sezione precedente di questo articolo. Con le funzioni INDICE/CONFRONTA è possibile cercare valori maggiori, minori o uguali al valore di ricerca. Per altre informazioni sull'uso di INDICE/CONFRONTA invece di CERCA.VERT, vedere la sezione precedente in questo argomento.
Problema: la colonna di ricerca non è ordinata in ordine crescente
Se l'argomento intervallo è impostato su TRUE e uno dei valori di ricerca non è ordinato in ordine crescente (A-Z), verrà visualizzato l'errore #N/D.
Soluzione:
-
Modificare la funzione CERCA.VERT in modo da cercare una corrispondenza esatta. A tale scopo, impostare l'argomento intervallo su FALSE. Non è necessario alcun ordinamento per FALSE.
-
Usare la funzione INDICE/CONFRONTA per cercare un valore in una tabella non ordinata.
Problema: il valore è un numero a virgola mobile elevato
Se le celle contengono valori temporali o numeri decimali grandi, Excel restituisce l'errore #N/D a causa della precisione della virgola mobile. I numeri a virgola mobile sono quelli che seguono il separatore decimale. Excel archivia i valori temporali come numeri a virgola mobile. Excel non può archiviare numeri a virgola mobile molto grandi, quindi per il corretto funzionamento della funzione, è necessario che i numeri vengano arrotondati a 5 posizioni decimali.
Soluzione: abbreviare i numeri arrotondandoli fino a cinque cifre decimali, con la funzione ARROTONDA.
Servono altre informazioni?
È sempre possibile rivolgersi a un esperto della Tech Community di Excel o ottenere supporto nelle Community.
