INDICE e CONFRONTA in Excel: l’alternativa flessibile a CERCA.VERT

CERCA.VERT è la prima funzione di ricerca che quasi tutti imparano, e anche quella che dà più grattacapi: cerca solo verso destra, si rompe quando qualcuno inserisce una colonna e, se dimentichi un argomento, restituisce risultati approssimativi che sembrano corretti ma non lo sono.

La combinazione di INDICE e CONFRONTA risolve questi problemi e funziona in qualsiasi versione di Excel. Vedrai cosa fa ciascuna funzione da sola, come unirle, come cercare per riga e colonna allo stesso tempo e a quali errori fare attenzione.

Perché cercare un’alternativa a CERCA.VERT

Una formula tipica di CERCA.VERT ha questo aspetto:

=CERCA.VERT(H2;A2:E6;4;FALSO)

Funziona, ma ha limiti importanti:

• Guarda solo verso destra. Il valore cercato deve trovarsi nella prima colonna dell’intervallo. Non puoi cercare un nome e restituire il codice che si trova alla sua sinistra.
• Usa un numero di colonna fisso. Il 4 significa “quarta colonna”. Se qualcuno inserisce una colonna in mezzo, la formula restituisce un altro dato senza avvisare.
• La corrispondenza approssimativa è l’opzione predefinita. Se ometti FALSO, Excel può restituire un valore vicino invece di un errore.

I dati di esempio

Useremo questo catalogo di prodotti nelle colonne da A a E, con le intestazioni nella riga 1:

Nella cella H2 scriveremo il codice da cercare, ad esempio P-104.

I due mattoncini: INDICE e CONFRONTA

INDICE: restituisce il valore di una posizione

INDICE riceve un intervallo e un numero di riga (e, se vuoi, di colonna) e restituisce ciò che si trova in quella posizione:

=INDICE(B2:B6;3)

Restituisce “Monitor”, il terzo elemento dell’intervallo. Con un intervallo di più colonne puoi indicare anche la colonna:

=INDICE(A2:E6;3;4)

Restituisce 180: riga 3 e colonna 4 dell’intervallo, cioè il prezzo del monitor.

CONFRONTA: trova la posizione di un valore

CONFRONTA fa l’opposto: cerca un valore in una riga o in una colonna e ti dice in che posizione si trova.

=CONFRONTA("P-103";A2:A6;0)

Restituisce 3. L’ultimo argomento è il tipo di corrispondenza ed è fondamentale:

• 0: corrispondenza esatta. È quello che userai quasi sempre.
• 1 (predefinito): il valore più grande minore o uguale a quello cercato. Richiede dati in ordine crescente.
• -1: il valore più piccolo maggiore o uguale a quello cercato. Richiede dati in ordine decrescente.

Combinare INDICE e CONFRONTA

L’idea è semplice: CONFRONTA calcola la riga e INDICE restituisce il dato di quella riga. Per ottenere il prezzo del codice scritto in H2:

=INDICE(D2:D6;CONFRONTA(H2;A2:A6;0))

Con P-104 in H2, il risultato è 650. Per costruire la formula senza sbagliare:

1. Scegli la colonna che contiene la risposta (D2:D6, i prezzi).
2. Scegli la colonna in cui si trova il valore che conosci (A2:A6, i codici).
3. Controlla che i due intervalli inizino e finiscano sulla stessa riga.
4. Scrivi CONFRONTA con lo 0 finale e inseriscila come secondo argomento di INDICE.

Poiché la colonna del risultato è un intervallo indipendente, inserire colonne nella tabella non influisce sulla formula: Excel aggiorna i riferimenti da solo.

Cercare verso sinistra

Ecco il grande vantaggio rispetto a CERCA.VERT. Se conosci il nome del prodotto (in H3) e vuoi il suo codice, che si trova a sinistra:

=INDICE(A2:A6;CONFRONTA(H3;B2:B6;0))

Con “Monitor” in H3, restituisce P-103.

Recuperare dati da un altro foglio

Gli intervalli possono trovarsi in fogli diversi. Se hai un elenco di ordini con il codice nella colonna A e il catalogo in un foglio chiamato Prezzi, questa formula recupera il prezzo di ogni ordine:

=INDICE(Prezzi!D:D;CONFRONTA(A2;Prezzi!A:A;0))

Se i dati ti arrivano divisi in più file, ad esempio un catalogo per fornitore, conviene prima riunirli. Il nostro strumento gratuito per unire file Excel combina più cartelle di lavoro in una sola, e poi puoi cercare con un’unica formula.

Ricerca con riga e colonna variabili

Usando due CONFRONTA, uno per la riga e uno per la colonna, puoi scegliere anche quale dato vedere. Scrivi in H4 il nome di una colonna, ad esempio Scorte:

=INDICE(A2:E6;CONFRONTA(H2;A2:A6;0);CONFRONTA(H4;A1:E1;0))

Con P-104 in H2 e Scorte in H4, il risultato è 8. Se cambi H4 in Prezzo, ottieni 650 senza toccare la formula. È un modo molto comodo per creare una piccola scheda di consultazione: una cella per il codice, un elenco a discesa con le intestazioni e una formula.

Errori comuni e come evitarli

• #N/D perché il valore non esiste. Di solito dipende da spazi finali, codici scritti in modo diverso o numeri memorizzati come testo (101 non è uguale a “101”). Per mostrare un messaggio al posto dell’errore, racchiudi la formula in SE.ERRORE.
• Intervalli disallineati. Se INDICE usa D1:D6 e CONFRONTA usa A2:A6, la posizione calcolata corrisponde a un’altra riga e il risultato risulta spostato di una posizione, senza alcun avviso.
• Dimenticare lo 0 in CONFRONTA. Senza di esso, Excel usa la corrispondenza approssimativa e, con dati non ordinati, può restituire una riga qualsiasi.
• Riferimenti che si spostano durante la copia. Se devi copiare la formula verso il basso, blocca gli intervalli con $, ad esempio $A$2:$A$6.

Questa è la versione con un messaggio personalizzato:

=SE.ERRORE(INDICE(D2:D6;CONFRONTA(H2;A2:A6;0));"Non trovato")

E CERCA.X?

In Excel 2021 e Microsoft 365 esiste CERCA.X, che fa la stessa cosa con una sola funzione e cerca in qualsiasi direzione:

=CERCA.X(H2;A2:A6;D2:D6;"Non trovato")

Se condividi file con persone che usano Excel 2016 o 2019, INDICE e CONFRONTA restano la scelta più sicura: in quelle versioni, CERCA.X mostra l’errore #NOME?.

Conclusione

INDICE e CONFRONTA separano due compiti: trovare la posizione e restituire il dato. È proprio questa separazione a renderle così flessibili. Cercano in qualsiasi direzione, tollerano i cambiamenti nella struttura della tabella e permettono ricerche per riga e colonna con una sola formula.

Prendi l’abitudine di scrivere sempre lo 0 di CONFRONTA, di controllare che gli intervalli siano allineati e di ripulire spazi e formati prima di cercare. Con queste tre abitudini eviterai la maggior parte degli errori #N/D.

Domande frequenti