Quando un foglio Excel contiene mesi nelle colonne e prodotti nelle righe, trovare il dato giusto significa incrociare due coordinate, non limitarsi a una semplice ricerca verticale. Qui mostro come cercare un valore in una matrice bidimensionale con CERCA.X, con la combinazione classica INDICE e CONFRONTA e con formule adatte anche ai casi in cui il dato non esiste.
Il metodo giusto dipende da come sono organizzati riga e colonna
- CERCA.X annidato è la soluzione più leggibile nelle versioni moderne di Excel.
- INDICE e CONFRONTA funziona bene anche nei file creati con versioni meno recenti.
- La corrispondenza esatta si imposta con il parametro 0.
- SE.ERRORE evita la comparsa di #N/D quando il valore non viene trovato.
- Con CONTA.SE si può verificare rapidamente se un dato è presente nell’intera matrice.
Che cosa significa cercare in una matrice bidimensionale
Immaginiamo una tabella con i prodotti nella colonna A, i mesi nella riga 1 e gli importi nelle celle B2:E5. Se voglio conoscere le vendite delle “Cuffie” a “Marzo”, devo individuare la riga del prodotto e la colonna del mese. Il risultato è il valore che si trova all’incrocio tra le due coordinate.
| Prodotto | Gennaio | Febbraio | Marzo | Aprile |
|---|---|---|---|---|
| Monitor | 120 | 135 | 142 | 150 |
| Tastiere | 95 | 110 | 108 | 116 |
| Cuffie | 80 | 92 | 101 | 99 |
In questo esempio il risultato corretto è 101. La difficoltà nasce dal fatto che funzioni come CERCA.VERT cercano normalmente in una sola direzione. Per un’intersezione riga-colonna servono due ricerche coordinate.
La soluzione più semplice con CERCA.X
Nelle versioni moderne di Excel, il metodo che preferisco è annidare due funzioni CERCA.X. La prima individua la riga del prodotto, mentre la seconda cerca il mese all’interno dell’intervallo restituito.
Supponiamo che il prodotto da cercare sia scritto in G2 e il mese in G3. La formula completa è:
=CERCA.X(G3;$B$1:$E$1;CERCA.X(G2;$A$2:$A$4;$B$2:$E$4);"Non trovato")
La funzione interna cerca il prodotto nell’intervallo A2:A4 e restituisce la riga corrispondente tra B2:E4. La funzione esterna cerca poi il mese nella riga delle intestazioni e restituisce il dato corretto. L’argomento “Non trovato” sostituisce l’errore quando una delle due coordinate manca.
Perché CERCA.X è pratica
CERCA.X non richiede il conteggio manuale della colonna di risultato e può cercare anche quando la colonna di ritorno si trova a sinistra dell’intervallo di ricerca. Nelle installazioni più recenti di Microsoft 365 ed Excel 2021 o successive è spesso la scelta più pulita.
Non la userei però senza verificare la compatibilità del file. Excel 2016 e Excel 2019 non includono normalmente CERCA.X come funzione nativa, quindi una cartella condivisa con utenti che lavorano su versioni precedenti può mostrare un errore di funzione non riconosciuta.
Il metodo compatibile con INDICE e CONFRONTA
La combinazione tra INDICE e due funzioni CONFRONTA è il metodo classico per una ricerca a due vie. È meno immediata da leggere, ma rimane molto solida e non dipende da CERCA.X.
Con lo stesso esempio, la formula è:
=INDICE($B$2:$E$4;CONFRONTA(G2;$A$2:$A$4;0);CONFRONTA(G3;$B$1:$E$1;0))
Il primo CONFRONTA restituisce il numero della riga del prodotto. Il secondo restituisce il numero della colonna del mese. INDICE usa entrambe le posizioni per recuperare il valore dalla matrice B2:E4.
Il significato dello zero finale
Il parametro 0 impone una corrispondenza esatta. È importante perché, senza questa impostazione, Excel può applicare una ricerca approssimata e restituire un dato apparentemente plausibile ma sbagliato, soprattutto quando l’elenco non è ordinato.
Per gestire i valori assenti si può aggiungere SE.ERRORE:
=SE.ERRORE(INDICE($B$2:$E$4;CONFRONTA(G2;$A$2:$A$4;0);CONFRONTA(G3;$B$1:$E$1;0));"Prodotto o mese non trovato")
Questa versione è più adatta a un report destinato ad altre persone, perché evita di lasciare in vista il codice #N/D e restituisce un messaggio comprensibile.
Quale formula scegliere nel lavoro quotidiano
Le due soluzioni portano allo stesso risultato, ma hanno caratteristiche diverse. La scelta non dovrebbe dipendere solo dal numero di caratteri della formula: contano anche la versione di Excel, la manutenzione futura e la possibilità che la tabella cambi struttura.| Metodo | Punto di forza | Limite | Quando usarlo |
|---|---|---|---|
| CERCA.X annidato | Formula leggibile e flessibile | Non disponibile nativamente nelle versioni meno recenti | Microsoft 365, Excel 2021 e file moderni |
| INDICE + CONFRONTA | Compatibilità e controllo preciso | Sintassi più lunga | File condivisi o versioni precedenti |
| CERCA.VERT | Semplice per una ricerca verticale | Non gestisce bene l’incrocio di due coordinate | Tabelle con un solo criterio di ricerca |
La mia regola pratica è questa: scelgo CERCA.X quando controllo l’ambiente di lavoro e voglio una formula facile da aggiornare. Preferisco INDICE e CONFRONTA quando il file deve circolare tra reparti diversi o quando la compatibilità è più importante della brevità.
Come verificare un valore in tutta la matrice
Non sempre serve sapere l’incrocio tra una riga e una colonna. A volte la domanda è più semplice: “Questo codice compare da qualche parte nella tabella?”. In questo caso una ricerca bidimensionale completa è superflua.
Per controllare la presenza del valore scritto in G2 nell’intervallo B2:E4, usa:
=SE(CONTA.SE($B$2:$E$4;G2)>0;"Presente";"Assente")
La formula conta quante volte il valore compare nell’intera matrice. Se il risultato è superiore a zero, il dato esiste. È una soluzione veloce per controlli di qualità, importazioni e verifiche preliminari prima di costruire una formula più complessa.
Se lo stesso valore può comparire più volte, CONTA.SE è anche più informativa di una ricerca che restituisce soltanto la prima corrispondenza. In una matrice con duplicati, infatti, CERCA.X e CONFRONTA restituiscono normalmente la prima occorrenza, non tutte le posizioni.
Gli errori che fanno restituire il risultato sbagliato
Intestazioni non identiche
Un mese scritto come “Marzo ” con uno spazio finale non è sempre gestito come “Marzo”. Lo stesso problema si presenta con codici importati da CSV, celle contenenti spazi invisibili e numeri memorizzati come testo.Quando una formula restituisce #N/D anche se il dato sembra presente, controllo prima spazi, formati e tipo di dato. ANNULLA.SPAZI può rimuovere gli spazi superflui dal testo, mentre la conversione in numero risolve molti problemi legati ai codici importati.
Intervalli non allineati
La matrice B2:E4 deve avere lo stesso numero di righe dell’elenco A2:A4 e lo stesso numero di colonne delle intestazioni B1:E1. Se si modifica solo un intervallo, la formula può restituire un errore oppure collegare il valore alla coordinata sbagliata.
Per questo conviene bloccare i riferimenti con il simbolo $ e, quando possibile, trasformare l’intervallo in una Tabella di Excel. Le Tabelle si espandono con i nuovi dati e riducono il rischio di lasciare fuori l’ultima riga.Ricerca approssimata usata per errore
Con CONFRONTA bisogna usare 0 per la corrispondenza esatta. Con CERCA.X è inoltre possibile impostare esplicitamente la modalità di confronto, ma nella maggior parte dei report aziendali la ricerca esatta è la scelta più sicura.
Leggi anche: Grafico percentuale chiaro - come sceglierlo e crearlo
Celle vuote e valori realmente mancanti
Una cella vuota può significare “dato non ancora disponibile”, “zero” oppure “nessuna vendita”. Non sono la stessa cosa. Prima di sostituire automaticamente gli errori con 0, stabilisco il significato del vuoto, altrimenti il report può trasformare un’assenza di informazione in un risultato numerico falso.
Una struttura più affidabile per i report Excel
La formula funziona meglio quando la tabella è progettata per separare chiaramente anagrafiche, intestazioni e misure. Evito celle unite, righe vuote nel mezzo e intestazioni duplicate: rendono la ricerca più fragile e complicano eventuali analisi con Tabelle Pivot o Power Query.
Per un modello ricorrente, metto le coordinate di ricerca in celle dedicate, ad esempio G2 per il prodotto e G3 per il mese. In questo modo l’utente cambia i criteri senza toccare la formula, mentre il risultato si aggiorna automaticamente.
Se la matrice supera qualche centinaio di righe o viene alimentata ogni giorno, prenderei in considerazione una struttura tabellare “lunga”, con una riga per ogni combinazione prodotto-mese. È meno intuitiva da leggere a colpo d’occhio, ma spesso è più efficiente per filtri, Power Query e modelli di business intelligence.
La scelta finale per una ricerca senza sorprese
Per incrociare un’intestazione di riga e una di colonna, partirei da CERCA.X annidato se il file usa una versione moderna di Excel. Quando la compatibilità è incerta, INDICE con due CONFRONTA resta una scelta affidabile e facilmente verificabile.
Prima di consegnare il foglio, testerei almeno tre casi: una combinazione presente, una combinazione assente e un valore con formato anomalo. Sono controlli semplici, ma fanno emergere quasi tutti i problemi che nella pratica trasformano una ricerca apparentemente corretta in un dato poco affidabile.
