INDEX e XMATCH in Excel: ricerche a una e due dimensioni

Collegamenti rapidi

• How Excel’s INDEX and XMATCH Functions Work

• Using INDEX With XMATCH for One-Way Lookups

• Using INDEX With XMATCH for Two-Way Lookups

Questo articolo spiega come usare INDEX e XMATCH in Excel per ricerche monodimensionali e bidimensionali. Procede dal semplice al complesso e include suggerimenti pratici, controllo degli errori e checklist operative.

Come funzionano INDEX e XMATCH in Excel

Prima di usare le due funzioni insieme, vediamo brevemente come lavorano singolarmente.

La funzione INDEX

INDEX restituisce il valore in una matrice o in un intervallo, in base al numero di riga e di colonna che specifichi.

Sintassi:

=INDEX(*a*,*b*,*c*)

dove

• a è l’intervallo o la tabella,
• b è il numero di riga, e
• c è il numero di colonna.

Esempio:

=INDEX(T_Profit,3,6)

inserito nella cella H2 restituisce il valore nella terza riga e nella sesta colonna della tabella T_Profit.

Invece di inserire numeri fissi per riga e colonna, puoi riferirti a celle che li contengono:

=INDEX(T_Profit,I2,I3)

in I5 prende il numero di riga da I2 e il numero di colonna da I3.

La funzione XMATCH

XMATCH cerca un elemento in un intervallo e restituisce la sua posizione relativa. È disponibile in Excel 2021, Excel per Microsoft 365, Excel per il web e nelle app tablet/mobile.

Esempio di sintassi:

=XMATCH(*a*,*b*,*c*,*d*)

dove

• a è il valore da cercare,
• b è l’intervallo in cui cercare,
• c è il tipo di corrispondenza (0 = esatta (predefinito); -1 = esatta o prossimo minore; 1 = esatta o prossimo maggiore; 2 = con jolly),
• d è la modalità di ricerca (1 = prima->ultima (predefinito), -1 = ultima->prima, 2 = ricerca binaria su intervallo ordinato ascendente, -2 = ricerca binaria su intervallo ordinato discendente).

XMATCH offre opzioni più intuitive rispetto a MATCH: per impostazione predefinita cerca una corrispondenza esatta e permette di cercare in entrambe le direzioni.

Esempio pratico:

=XMATCH(1927,T_Profit[Employee])

in H2 restituisce 3 se l’ID 1927 è il terzo valore nella colonna Employee della tabella T_Profit.

Puoi anche passare a XMATCH un riferimento a cella per rendere la ricerca dinamica:

=XMATCH(I2,T_Profit[Employee])

Usare INDEX con XMATCH per ricerche monodimensionali

Combinando le due funzioni ottieni ricerche più flessibili e robuste rispetto a formule tradizionali.

Scenario: vuoi vedere il profitto totale generato da un dipendente inserendo il suo ID in I2.

Formula in I2:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)

• T_Profit è la tabella dove cercare;
• XMATCH(I2,T_Profit[Employee]) individua la riga corrispondente all’ID in I2;
• 6 indica la sesta colonna della tabella (profitto totale).

Suggerimento: per comodità, clicca l’intestazione di colonna nella tabella per ottenere il riferimento strutturato senza digitare manualmente.

Questa configurazione non richiede di impostare i parametri di tipo di ricerca o modalità per XMATCH, perché gli argomenti predefiniti restituiscono una corrispondenza esatta dall’alto verso il basso.

Usare INDEX con XMATCH per ricerche bidimensionali

La forza della combinazione emerge quando vuoi trovare il valore all’intersezione di una riga e di una colonna variabili. XMATCH può restituire sia il numero di riga che quello di colonna, e INDEX usa quei due numeri per estrarre il valore.

Esempio: quanto ha generato di profitto il dipendente 1191 nel 2021?

Formula in I4:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

• il primo XMATCH trova la riga in base all’ID in I2;
• il secondo XMATCH trova la colonna in base al valore dell’intestazione in I3.

Importante: se le intestazioni di colonna contengono numeri (per esempio anni), Excel potrebbe memorizzarle come testo. Questo provoca ERRORI #N/A nella ricerca.

Per risolvere: imposta il formato della cella del valore di ricerca su Testo (scheda Home > Formato numero > Testo), poi premi F2 nella cella e Invio per forzare la conversione. In alternativa, usa la funzione TESTO o VALORE per armonizzare i tipi.

Dopo l’allineamento dei tipi, la formula restituirà il valore previsto.

Prova a cambiare i parametri in I2 e I3 per vedere i risultati aggiornarsi automaticamente.

Ora che sai come impostare le ricerche bidimensionali, puoi velocizzare l’inserimento dei parametri con la convalida dati (Data Validation). Nota: non puoi usare direttamente le intestazioni di colonna di una tabella come sorgente di elenco; usa riferimenti a celle o nomi intervallo.

Suggerimenti pratici e varianti avanzate

Di seguito trovi approcci, trucchi e alternative che aumentano robustezza, leggibilità e manutenzione delle formule.

1) Gestire errori e valori mancanti

Usa IFERROR per evitare messaggi di errore poco utili:

=IFERROR(INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers])) ,"Valore non trovato")

Meglio ancora, con LET rendi la formula più leggibile e reusi i calcoli:

=LET(
  id, I2,
  col, I3,
  riga, XMATCH(id, T_Profit[Employee]),
  colnum, XMATCH(col, T_Profit[#Headers]),
  IFERROR(INDEX(T_Profit, riga, colnum), "Valore non trovato")
)

LET aiuta anche a ottimizzare le prestazioni quando le ricerche sono complesse.

2) Quando la ricerca fallisce: cause comuni

• tipi dati disallineati (testo vs numero);
• spazi invisibili o caratteri non stampabili nella sorgente o nel valore di ricerca;
• intestazioni duplicate (XMATCH restituisce la prima corrispondenza per impostazione predefinita);
• riferimenti strutturati non aggiornati dopo modifiche alla tabella.

Soluzioni rapide: TRIM, CLEAN, VALUE/TEXT, e verificare la presenza di duplicati.

3) Alternative a INDEX+XMATCH

• XLOOKUP (se vuoi una singola funzione che cerca in orizzontale o verticale e restituisce un valore): più semplice ma meno flessibile per ricerche bidimensionali senza nidificazioni;
• INDEX+MATCH (MATCH è la versione più vecchia) se XMATCH non è disponibile;
• DATABASE functions (DSUM, DGET) per scenari con criteri multipli.

4) Modello mentale rapido

• XMATCH trova la posizione;
• INDEX legge la cella a quella posizione;
• pensa a INDEX come a “prendi la riga X e la colonna Y” e a XMATCH come a “trova X”.

5) Esempio di playbook rapido

1. Assicurati che la tabella abbia intestazioni chiare e univoche.
2. Prepara le celle di input (ID, intestazione colonna) con formato coerente.
3. Usa XMATCH per riga e per colonna.
4. Incapsula in LET e IFERROR per leggibilità e gestione errori.
5. Aggiungi convalida dati per limitare gli input.

6) Checklist per ruoli (manager, analista, sviluppatore)

Manager:

• Verifica che le colonne critiche abbiano intestazioni coerenti.
• Richiedi nomi intervallo se la tabella è condivisa.

Analista:

• Usa LET per rendere le formule ripetibili.
• Aggiungi commenti di cella con spiegazioni delle formule.

Sviluppatore/IT:

• Valuta l’uso di tabelle strutturate e nomi intervallo centralizzati.
• Automatizza i controlli di qualità dei dati (script che rimuovono spazi/normalizzano tipi).

7) Casi di test e criteri di accettazione

• Inserire ID valido e anno valido → restituisce valore corretto.
• Inserire ID inesistente → restituisce messaggio amichevole (es. “Valore non trovato”).
• Intestazione numerica formattata come numero vs testo → la formula gestisce il formato dopo normalizzazione.
• Duplicati nelle intestazioni → controllare che venga restituita la prima corrispondenza prevista.

8) Decision tree per scegliere la soluzione giusta

flowchart TD
  A[Hai Excel con XMATCH disponibile?] -->|Sì| B{È una ricerca a 1 o 2 dimensioni?}
  A -->|No| C[Usa INDEX + MATCH o XLOOKUP se disponibile]
  B -->|1D| D[Usa XMATCH + INDEX o XLOOKUP]
  B -->|2D| E[Usa XMATCH per riga e per colonna + INDEX]
  D --> F[Incapsula in LET e IFERROR]
  E --> F
  C --> F
  F --> G[Valida input con Convalida Dati e normalizza tipi]

9) Prestazioni e scala

• XMATCH e INDEX su tabelle moderate sono veloci. Su milioni di righe, valuta tabelle pivot, Power Query o modelli dati (Power Pivot) per evitare calcoli riga-per-riga.
• Se usi ricerche binarie (modi 2 o -2), assicurati che l’intervallo sia ordinato; altrimenti risultati errati.

10) Sicurezza e privacy

Se la tabella contiene dati personali (ID impiegati, profitti collegati a persone), applica le regole aziendali su accesso e conservazione. In contesti GDPR, limita l’accesso e usa pseudonimizzazione se esponi file esternamente.

11) Compatibilità e migrazione

• XMATCH non esiste in versioni datate di Excel (prima di 2021). Per compatibilità, mantieni una versione alternativa basata su MATCH oppure usa XLOOKUP quando disponibile.
• Quando esporti file in formati non Excel, verifica che le formule vengano convertite o sostituite con valori per evitare errori.

Risoluzione dei problemi comuni (check rapido)

• Errore #N/A: verifica che tipi e spazi combacino; prova TRIM e VALUE.
• Errore #REF!: indica riferimento tabella non valido, controlla che la tabella non sia stata rinominata o cancellata.
• Valori sbagliati: controlla duplicati delle intestazioni o ambiguità nelle chiavi di ricerca.

Esempi di funzioni utili per debug:

• =ISNUMBER(cell) per verificare tipo numero;
• =ISTEXT(cell) per testo;
• =TRIM(cell) per rimuovere spazi superflui;
• =CLEAN(cell) per rimuovere caratteri non stampabili.

Piccola metodologia: creare una ricerca bidimensionale robusta (5 passaggi)

1. Nominare la tabella (es. T_Profit) e verificare intestazioni univoche.
2. Normalizzare tipi: assicurati che gli anni/intestazioni numeriche siano testuali se così sono le intestazioni della tabella.
3. Creare celle di input per ID e Intestazione e applicare Convalida Dati.
4. Scrivere la formula con LET e IFERROR.
5. Testare con casi normali, borderline e errori attesi.

Galleria di casi limite

• Intestazioni duplicate: XMATCH restituisce la prima. Se vuoi la seconda, usa modalità di ricerca opposta o limita la tabella.
• Riferimento a colonne inserite o rimosse: i riferimenti strutturati si aggiornano automaticamente, ma i numeri di colonna usati come valore fisso (es. 6) non si aggiornano.
• Valori calcolati come intestazioni (es. formule) possono cambiare tipo: valuta di convertire le intestazioni in valori statici se necessario.

Suggerimenti per versione italiana di Excel

• L’etichetta “Text” nella versione inglese corrisponde a “Testo” in italiano (scheda Home > Formato numero > Testo).
• La funzione XMATCH mantiene il nome XMATCH anche in Excel italiano; tuttavia le funzioni locali (in alcune versioni più vecchie) potrebbero usare nomi tradotti. Verifica la localizzazione della tua installazione.

Riepilogo finale

IMPORTANTE: INDEX + XMATCH è una combinazione potente e flessibile per ricerche sia orizzontali sia verticali. Normalizza i tipi, proteggi gli input con la convalida dati, e incapsula le formule in LET e IFERROR per chiarezza e robustezza.

Punti chiave:

• XMATCH trova posizioni; INDEX legge celle su riga/colonna;
• usa LET per migliori prestazioni e leggibilità;
• gestisci i formati (Testo vs Numero) per evitare #N/A;
• se XMATCH non è disponibile, usa INDEX+MATCH o XLOOKUP a seconda della disponibilità.

Se vuoi, posso trasformare la tua tabella esistente in un modello con nomi intervallo, formule LET pronte e convalida dati passo-passo per automatizzare la distribuzione.