Prima dell'arrivo di CERCA.X, chi aveva bisogno di ricerche più robuste di CERCA.VERT usava la coppia INDICE e CONFRONTA (INDEX e MATCH in inglese). Ancora oggi è la soluzione più portabile: funziona in tutte le versioni di Excel, in LibreOffice Calc e in Google Sheets, e un file costruito così si apre ovunque senza sorprese. Conviene capirla bene, perché ogni funzione presa da sola è semplice e la loro combinazione è molto potente.
Le due funzioni separate
INDICE: dammi l'elemento in quella posizione
=INDICE(intervallo; riga; [colonna])
INDICE restituisce il contenuto di una cella indicata per posizione. =INDICE(B2:B100; 5) restituisce il quinto valore dell'intervallo, cioè B6. Con un intervallo a due dimensioni si indicano riga e colonna: =INDICE(B2:M100; 5; 3) restituisce la cella alla quinta riga e terza colonna dell'intervallo.
Da sola sembra poco utile, perché di solito non sappiamo in che posizione si trova il dato. È qui che entra CONFRONTA.
CONFRONTA: dimmi in che posizione si trova
=CONFRONTA(valore; intervallo; [tipo])
CONFRONTA cerca un valore in un intervallo di una sola riga o colonna e restituisce la sua posizione, non il contenuto. Se in A2:A100 il codice «X-204» si trova nella cella A13, =CONFRONTA("X-204"; A2:A100; 0) restituisce 12.
Il terzo argomento ha lo stesso ruolo del quarto argomento di CERCA.VERT:
| Tipo | Comportamento | Requisito |
|---|---|---|
| 0 | corrispondenza esatta | nessuno |
| 1 | valore più grande minore o uguale | ordine crescente |
| -1 | valore più piccolo maggiore o uguale | ordine decrescente |
Se lo ometti vale 1, quindi per codici e nomi va sempre scritto 0.
La combinazione
Mettendo CONFRONTA dentro INDICE ottieni una ricerca completa: CONFRONTA trova la riga, INDICE restituisce il valore di quella riga nella colonna che ti serve.
=INDICE($D$2:$D$500; CONFRONTA(A2; $A$2:$A$500; 0))
Si legge così: «nella colonna dei prezzi, restituisci il valore alla stessa posizione in cui il codice di A2 compare nella colonna dei codici». Le due colonne sono indipendenti, quindi:
- puoi cercare a sinistra, per esempio trovare un codice partendo da una descrizione;
- puoi inserire o eliminare colonne nel mezzo senza rompere nulla;
- l'intervallo di ricerca e quello di restituzione possono stare perfino in fogli diversi, purché abbiano la stessa altezza e partano dalla stessa riga.
Quest'ultimo punto è il vincolo da ricordare: se un intervallo parte dalla riga 2 e l'altro dalla riga 3, il risultato è sfasato di una riga e nessuno se ne accorge.
Ricerca a due dimensioni
INDICE accetta sia la riga sia la colonna, quindi due CONFRONTA permettono di cercare all'incrocio di una tabella. Supponi di avere i prodotti in A3:A40, i mesi in B2:M2 e le vendite in B3:M40:
=INDICE($B$3:$M$40; CONFRONTA(P2; $A$3:$A$40; 0); CONFRONTA(P3; $B$2:$M$2; 0))
Con il prodotto in P2 e il mese in P3 ottieni il valore corrispondente. Cambiando P2 o P3, per esempio tramite un elenco a discesa, costruisci in pochi minuti un piccolo pannello di consultazione.
Ricerca con più criteri
Un caso frequente: trovare il prezzo di un articolo per una certa taglia, dove nessuna delle due colonne da sola è univoca. Una soluzione pulita è creare una colonna di appoggio che unisce i due campi, per esempio in E2 =A2&"|"&B2, e poi cercare la chiave combinata:
=INDICE($D$2:$D$500; CONFRONTA(H2&"|"&H3; $E$2:$E$500; 0))
Il separatore | evita ambiguità: senza di esso «AB» + «C» e «A» + «BC» produrrebbero la stessa chiave. Nelle versioni recenti esistono soluzioni senza colonna di appoggio, basate su formule a matrice, ma la colonna esplicita ha un vantaggio: chiunque apra il foglio capisce come funziona la ricerca.
Usare CONFRONTA da solo
CONFRONTA è utile anche senza INDICE:
- per verificare se un valore esiste in un elenco, con
=VAL.NUMERO(CONFRONTA(A2; Elenco; 0)), che restituisce VERO o FALSO; - per rendere dinamico l'indice di colonna di un CERCA.VERT esistente, sostituendo il numero fisso con un CONFRONTA sulla riga delle intestazioni;
- per trovare la posizione di un valore massimo, combinandolo con MAX:
=CONFRONTA(MAX(B2:B50); B2:B50; 0).
Errori tipici
- Dimenticare lo 0 in CONFRONTA, con risultati approssimati su dati non ordinati.
- Intervalli di altezza diversa tra ricerca e restituzione.
- Intervalli non bloccati, che scorrono quando copi la formula.
- Dati non omogenei: numeri salvati come testo, spazi finali, caratteri invisibili.
Nel mio lavoro, quando devo consegnare un file a persone che usano software diversi, scelgo INDICE e CONFRONTA proprio per questo: è la soluzione con meno probabilità di dare sorprese.
In sintesi
- CONFRONTA restituisce una posizione, INDICE il valore che si trova in quella posizione.
- Insieme cercano in qualsiasi direzione e resistono all'inserimento di colonne.
- Con due CONFRONTA si cerca all'incrocio di riga e colonna.
- Per più criteri una colonna chiave con separatore è semplice e trasparente.
- Funziona in ogni versione di Excel, in LibreOffice Calc e in Google Sheets.
