M365Con 2027 – conferenza online gratuita · 11–22 Jan 2027 · 120 sessions m365con.net

Blog · Mirko Peters

INDEX MATCH in Excel: formula, esempi ed errori comuni

Di · · Aggiornato

INDEX MATCH in Excel spiegato: la formula, il funzionamento di ciascuna funzione, le ricerche a sinistra e bidirezionali, i criteri multipli e gli errori comuni, con un confronto con VLOOKUP.

Tradotto automaticamente con l'IA dal post originale. Leggi l'originale in inglese

INDEX MATCH in Excel è una formula di ricerca composta da due funzioni: =INDEX(intervallo_restituito; MATCH(valore_cercato; intervallo_ricerca; 0)). MATCH trova la posizione del valore in un elenco, e INDEX restituisce l'elemento nella stessa posizione di un altro elenco. Questo articolo è pensato per chi usa formule VLOOKUP e ne incontra i limiti, oppure per chi ha ereditato una cartella di lavoro piena di INDEX MATCH e vuole capirla. Vedrete ciascuna funzione separatamente, come si combinano, come cercare a sinistra, come eseguire una ricerca bidirezionale, come usare due criteri e cosa significano gli errori.

Punti chiave

  • INDEX MATCH è due funzioni in una sola formula: =INDEX(intervallo_restituito; MATCH(valore_cercato; intervallo_ricerca; 0)). MATCH trova la posizione e INDEX restituisce l'elemento in quella posizione.
  • Usate 0 come terzo argomento di MATCH per una corrispondenza esatta. Lasciato al valore predefinito 1, MATCH presuppone che i dati siano ordinati in modo crescente.
  • Poiché l'intervallo di ricerca e quello restituito sono separati, INDEX MATCH può cercare a sinistra della colonna di ricerca, cosa che VLOOKUP non può fare.
  • Per due condizioni, si può unire le colonne in una colonna di supporto oppure usare una formula che moltiplica due confronti all'interno di MATCH.
  • Racchiudete la formula in IFNA per sostituire #N/D con un messaggio, lasciando visibili gli altri errori, così gli errori reali non vengono nascosti.

Troverete INDEX MATCH in molte cartelle di lavoro meno recenti, perché funzionava in ogni versione di Excel ben prima che esistesse XLOOKUP. Funziona ancora, ed è il motivo principale per impararla. I dettagli seguenti provengono dalle pagine Microsoft su MATCH, INDEX e VLOOKUP.

Cosa fa INDEX

INDEX restituisce un valore da una posizione all'interno di un intervallo. La pagina Microsoft riporta la forma ad array come INDEX(matrice; num_riga; [num_colonna]).

  • matrice è l'intervallo da cui prelevare il valore.
  • num_riga è la riga all'interno di quell'intervallo.
  • num_colonna è la colonna all'interno di quell'intervallo. Se l'intervallo ha una sola colonna, si può omettere.

I numeri partono dalla cella in alto a sinistra dell'intervallo, non dal foglio. L'esempio Microsoft usa un blocco con Mele e Banane nella prima colonna e Limoni e Pere nella seconda. =INDEX(A2:B3;2;2) restituisce Pere, e =INDEX(A2:B3;2;1) restituisce Banane.

Dalla stessa pagina vale la pena ricordare due regole. Se num_riga o num_colonna punta al di fuori dell'intervallo, INDEX restituisce un errore #RIF!. Se impostate num_riga o num_colonna su 0, INDEX restituisce l'intera colonna o riga. In Microsoft 365 attuale si preme Invio come al solito. Nelle versioni precedenti la si inserisce come formula matriciale con Ctrl+Maiusc+Invio.

Da sola, INDEX richiede di conoscere già la posizione. Ed è qui che entra in gioco MATCH.

Cosa fa MATCH

MATCH cerca in un intervallo e restituisce una posizione. Microsoft riporta la sintassi come MATCH(valore_ricerca; matrice_ricerca; [tipo_corrispondenza]). Non restituisce l'elemento stesso, solo la sua posizione relativa nell'intervallo.

Il terzo argomento determina il tipo di corrispondenza:

  • 0 trova la prima corrispondenza esatta. L'intervallo può essere in qualsiasi ordine. È quello che userai più spesso.
  • 1, che è anche il valore predefinito se ometti l'argomento, trova il valore più grande minore o uguale al valore di ricerca. L'intervallo deve essere ordinato in ordine crescente.
  • -1 trova il valore più piccolo maggiore o uguale al valore di ricerca. L'intervallo deve essere ordinato in ordine decrescente.

Gli esempi di Microsoft usano i valori 25, 38, 40 e 41 in B2:B5. =MATCH(39,B2:B5,1) restituisce 2, la posizione di 38, cioè il valore più vicino inferiore. =MATCH(41,B2:B5,0) restituisce 4. E =MATCH(40,B2:B5,-1) restituisce #N/D, perché quei valori non sono in ordine decrescente.

Altri comportamenti descritti nella pagina: la ricerca di testo non distingue maiuscole e minuscole, quindi "apple" e "APPLE" sono uguali. Con tipo di corrispondenza 0 e un valore di ricerca testuale, ? corrisponde a qualsiasi singolo carattere e * corrisponde a qualsiasi sequenza di caratteri, mentre una tilde prima di uno dei due cerca il carattere letterale. Se MATCH non trova nulla, restituisce #N/D.

Il valore predefinito 1 trae in inganno molti utenti. Se dimentichi il terzo argomento, MATCH presume silenziosamente dati ordinati e può restituire una posizione sbagliata senza alcun errore. In quasi tutte le ricerche di ID, nomi o codici, digita 0.

Mettiamo tutto insieme

Lo schema è sempre lo stesso: MATCH va dentro INDEX e fornisce il numero di riga.

=INDEX(intervallo_restituito, MATCH(valore_ricerca, intervallo_ricerca, 0))

Immagina un foglio con gli ID dei dipendenti in A2:A100, i nomi in B2:B100 e i reparti in C2:C100. Digiti un ID in F2 e vuoi il reparto.

  1. Fai clic sulla cella del risultato, ad esempio G2.
  2. Digita =INDEX(C2:C100, per indicare da dove proviene la risposta.
  3. Aggiungi MATCH(F2,A2:A100,0)) per indicare dove trovare l'ID.
  4. Premi Invio.

La formula completa è =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH trova F2 in A2:A100 e restituisce, ad esempio, 14. INDEX restituisce quindi il 14° elemento di C2:C100.

Mantieni gli intervalli della stessa lunghezza e con la stessa riga iniziale. Se MATCH cerca in A2:A100 e INDEX legge C3:C101, la posizione punta alla riga sbagliata e il risultato è silenziosamente errato. Usa le stesse righe iniziale e finale per entrambi e bloccali con il simbolo del dollaro, $A$2:$A$100, se prevedi di copiare la formula verso il basso.

Cercare a sinistra e differenze rispetto a VLOOKUP

La pagina di VLOOKUP di Microsoft indica la sintassi come VLOOKUP(valore_ricerca, matrice_tabella, num_indice_col, [intervallo_ricerca]). Specifica che il valore di ricerca deve trovarsi nella prima colonna dell'intervallo e che omettere intervallo_ricerca restituisce una corrispondenza approssimativa, che presuppone dati ordinati. La pagina suggerisce anche di provare XLOOKUP.

Ecco lo stesso compito svolto in entrambi i modi, con gli ID nella colonna A e i nomi nella colonna B:

  • =VLOOKUP(F2,A2:B100,2,FALSE)
  • =INDEX(B2:B100,MATCH(F2,A2:A100,0))

INDEX MATCH offre due vantaggi concreti. Primo, può cercare a sinistra. Se i nomi sono nella colonna B e vuoi recuperare l'ID dalla colonna A, basta scambiare gli intervalli: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP non può farlo, perché il valore di ricerca deve trovarsi nella prima colonna. Secondo, indica direttamente la colonna da restituire, invece di contarla. In VLOOKUP, il 2 nella formula significa "seconda colonna dell'intervallo". Se inserisci una colonna all'interno dell'intervallo, il numero punta a un'altra colonna. Con INDEX MATCH, il riferimento si adegua come qualsiasi altro riferimento.

Si legge spesso anche che INDEX MATCH è più veloce. Le pagine di Microsoft non fanno questa affermazione, e l'eventuale differenza conta solo in cartelle di lavoro molto grandi, quindi non sceglierlo per la velocità. Sceglilo per la flessibilità.

Ricerche bidimensionali

Una ricerca bidimensionale trova il valore in cui si incontrano una riga e una colonna, ad esempio le vendite di un prodotto in un determinato mese. Usa due funzioni MATCH, una per la riga e una per la colonna:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Qui B2:M20 contiene i numeri, A2:A20 i nomi dei prodotti, B1:M1 le intestazioni dei mesi, e P1 e Q1 contengono il prodotto e il mese che ti interessano. Il primo MATCH restituisce la riga, il secondo la colonna, e INDEX restituisce il numero nel punto in cui si incrociano. Nota che l'intervallo di restituzione inizia da B2, non da A1, così le posizioni coincidono con gli intervalli delle etichette: anche gli intervalli di MATCH iniziano dalla stessa riga o colonna dei numeri.

Corrispondenza su due criteri

MATCH accetta un solo valore di ricerca. Se un record è identificato solo da due campi, come categoria e stato, hai due opzioni.

Una colonna di supporto. Aggiungi una colonna che unisce i due campi, per esempio =A2&"|"&B2 in D2, e trascinala verso il basso. Poi cerca il testo unito:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

Il separatore è importante. Senza di esso, "AB" più "C" e "A" più "BC" diventano entrambi "ABC". La colonna di supporto è facile da leggere e da verificare, e funziona in ogni versione.

Una formula matrice. Senza una colonna di supporto, puoi moltiplicare due confronti:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Ogni confronto restituisce VERO o FALSO. La moltiplicazione li trasforma in 1 solo dove entrambi sono veri, e MATCH cerca il primo 1. Questo non compare nelle pagine di Microsoft su INDEX e MATCH, quindi verificalo su un intervallo ridotto. Nelle versioni precedenti di Excel potrebbe essere necessario inserirla con Ctrl+Maiusc+Invio, come Microsoft descrive per i risultati di matrice di INDEX. Nell'attuale Microsoft 365 basta Invio.

Caratteri jolly e valori più vicini

Con tipo di corrispondenza 0 e testo, MATCH accetta caratteri jolly. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) restituisce l'elemento accanto alla prima voce che inizia con "Jan". È utile per codici parziali. Mantieni il modello specifico, perché MATCH si ferma alla prima corrispondenza.

Per fasce come gli scaglioni fiscali o le fasce di provvigione, usa volutamente il tipo di corrispondenza predefinito 1, con le soglie ordinate in modo crescente. Inserisci 0, 1000 e 5000 in A2:A4 e le aliquote in B2:B4; =INDEX(B2:B4,MATCH(E2,A2:A4,1)) restituisce l'aliquota della soglia più alta che non supera E2. È uno dei pochi casi in cui il comportamento predefinito con dati ordinati è la scelta giusta.

Risolvere gli errori

  • #N/D. MATCH non ha trovato nulla. Cerca errori di battitura, spazi finali e numeri memorizzati come testo, dato che "100" e 100 sono diversi. Verifica che il tipo di corrispondenza sia 0, e che con i tipi 1 o -1, che richiedono dati ordinati, i dati siano davvero ordinati.
  • #RIF!. INDEX ha chiesto una riga o una colonna fuori dall'intervallo. Succede anche quando elimini una riga o una colonna usata dalla formula. Verifica che l'intervallo di MATCH e quello di INDEX abbiano la stessa dimensione.
  • Un risultato sbagliato senza alcun errore. Di solito manca lo 0 in MATCH, oppure gli intervalli iniziano su righe diverse.
  • Un risultato sbagliato dopo la copia verso il basso. Gli intervalli si sono spostati perché non erano bloccati. Aggiungi $ agli intervalli.

Per sostituire un errore con un messaggio, la funzione SE.ERRORE.VAL di Microsoft restituisce un valore a scelta quando una formula dà #N/D, e lascia passare invariato ogni altro errore:

=SE.ERRORE.VAL(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Non trovato")

Per le ricerche preferisci SE.ERRORE.VAL a SE.ERRORE. SE.ERRORE nasconde ogni errore, compreso un #RIF! causato da un intervallo danneggiato, così un guasto reale sembra un normale "Non trovato". SE.ERRORE.VAL copre solo il caso che ti aspetti. Microsoft indica SE.ERRORE.VAL per Excel 2016 e versioni successive.

INDEX MATCH o XLOOKUP

Se tutti quelli che aprono il file hanno un Microsoft 365 aggiornato, XLOOKUP svolge lo stesso compito con una sola funzione e un messaggio integrato per il caso non trovato. Ne parliamo nella nostra guida a XLOOKUP in Excel. Ma la pagina di Microsoft su XLOOKUP non è chiara riguardo a Excel 2016 e 2019, e non elenca Excel per il web. INDEX MATCH funziona in tutti questi casi. La scelta è quindi pratica: usa XLOOKUP nei file destinati alle versioni attuali, e INDEX MATCH quando il file viaggia verso persone con versioni più vecchie o quando manuteni una cartella di lavoro che già lo usa.

Errori comuni

  • Dimenticare lo 0 in MATCH e fidarsi del valore predefinito.
  • Usare intervalli di dimensioni diverse o che iniziano da righe diverse in INDEX e MATCH.
  • Dimenticare di bloccare gli intervalli con $ prima di copiare la formula verso il basso.
  • Unire i criteri senza un separatore in una colonna di supporto.
  • Racchiudere tutto in SE.ERRORE, così le formule rotte sembrano dati mancanti.
  • Ti aspetti che INDEX MATCH restituisca tutte le corrispondenze. In realtà ne restituisce solo la prima.

Argomenti correlati da esplorare

  • XLOOKUP. La funzione di ricerca più recente, con direzione della ricerca e argomento per il caso di valore non trovato già integrati.
  • Più risultati insieme. INDEX MATCH restituisce solo la prima corrispondenza. Per restituire tutte le corrispondenze, nelle versioni che la includono, lo strumento giusto è la funzione FILTER.
  • Tabelle e intervalli denominati. I riferimenti strutturati e i nomi rendono le ricerche più leggibili e permettono all'intervallo di crescere insieme ai dati.
  • Prestazioni su fogli di grandi dimensioni. Usa intervalli delimitati invece di intere colonne e mantieni gli intervalli il più possibili stretti.
  • Ricerche con distinzione tra maiuscole e minuscole. MATCH ignora le maiuscole, quindi una ricerca che le distingue richiede un approccio diverso. Verifica qualsiasi soluzione alternativa prima di affidarti a essa.

Prossimo passo

Riscrivi una delle tue formule VLOOKUP come INDEX MATCH e confronta il risultato su alcuni valori noti. Prova poi la stessa formula con una ricerca verso sinistra. Dopo aver fatto entrambe le prove, saprai quando INDEX MATCH in Excel è lo strumento migliore per il lavoro da svolgere.

Fonti

FAQ

Come funziona INDEX MATCH in Excel?

MATCH cerca il valore di ricerca in un intervallo e restituisce la sua posizione, per esempio 5 per il quinto elemento. INDEX usa poi questo numero per restituire l'elemento nella stessa posizione in un altro intervallo. Combinate, =INDEX(C2:C100;MATCH(F2;A2:A100;0)) trova F2 nella colonna A e restituisce il valore della colonna C.

INDEX MATCH è meglio di VLOOKUP?

È più flessibile. VLOOKUP richiede che il valore di ricerca si trovi nella prima colonna del suo intervallo e conta le colonne tramite un numero, mentre INDEX MATCH usa due intervalli separati, quindi può cercare anche a sinistra e non risente dell'inserimento di colonne. Per una ricerca semplice verso destra vanno bene entrambe. XLOOKUP è una terza opzione nelle versioni più recenti.

Perché INDEX MATCH restituisce #N/D?

MATCH restituisce #N/D quando non trova corrispondenze. Controllate errori di battitura, spazi in più, numeri memorizzati come testo e il tipo di corrispondenza. Usate 0 per una corrispondenza esatta. Con il valore predefinito 1, l'intervallo di ricerca deve essere ordinato in modo crescente, mentre con -1 deve essere ordinato in modo decrescente.

Entità in questo articolo

Temi di questo articolo:

Microsoft Excel – Strumento di analisi e BI di Microsoft

Articoli correlati

Altri articoli su Microsoft Excel:

RSS