INDEX MATCH in Excel: un modo migliore per cercare dati

INDEX MATCH, una combinazione di due funzioni di Excel, INDEX() e MATCH(), è un'alternativa alla funzione VLOOKUP() comunemente usata.

In questa guida ti mostrerò come funzionano singolarmente e insieme INDEX() e MATCH(), come eseguire ricerche verticali e orizzontali e le best practice per usare INDEX MATCH con esempi pratici.

Che cos'è INDEX MATCH in Excel?

INDEX MATCH è un modo abbreviato per riferirsi all'annidamento delle due funzioni. È equivalente all'idea di INDEX(MATCH()). Vediamo come funzionano sia singolarmente che insieme.

Funzione INDEX() in Excel

La funzione INDEX() in Excel restituisce un valore o un riferimento a un valore all'interno di un intervallo o di una tabella. Puoi usarla in due modi: forma matrice e forma riferimento.

Forma matrice

Puoi usare la forma matrice quando lavori con una tabella o matrice fissa. In questo formato, INDEX() estrae un valore in base al numero di riga e di colonna che specifichi. La sua sintassi è:

INDEX(array, row_num, [column_num])Offerto da

Vediamo questi argomenti:

• array (obbligatorio) è l'intervallo o la matrice di valori con cui stai lavorando.

• row_num (obbligatorio) è il numero di riga da cui restituire un valore.

• column_num (facoltativo) è il numero di colonna da cui restituire un valore.

Dobbiamo specificare almeno uno tra row_num o column_num affinché Excel possa individuare il valore.

Tieni però a mente alcune note rapide quando lavori con questa sintassi:

• Se l'array ha una sola riga o una sola colonna, puoi omettere row_num o column_num.

• Se sono inclusi sia row_num che column_num, INDEX restituisce il valore all'intersezione.

• Se imposti row_num o column_num a 0, Excel restituisce l'intera colonna o riga (dovrai inserirla come formula matrice per vedere il risultato).

Forma riferimento

Puoi usare la forma riferimento quando lavori con più intervalli e devi specificare quale considerare.

La sua sintassi è:

INDEX(reference, row_num, [column_num], [area_num])Offerto da

Vediamo questi argomenti:

• reference (obbligatorio) serve per fare riferimento a uno o più intervalli di celle.

• row_num (obbligatorio) è il numero di riga nell'area selezionata.

• column_num (facoltativo) è il numero di colonna nell'area selezionata.

• area_num (facoltativo) indica a Excel quale intervallo usare se ce n'è più di uno.

Ecco alcuni punti aggiuntivi da ricordare:

• Se area_num viene omesso, Excel usa per impostazione predefinita il primo intervallo.

• Non puoi mescolare intervalli da fogli diversi in questa forma; genererà un errore.

• Se sia riga che colonna sono omesse, restituisce l'intera area specificata.

Funzione MATCH() in Excel

La MATCH() function ti aiuta a trovare la posizione di un valore in una riga o colonna. Invece di restituire il valore stesso, ti dice dove si trova nell'elenco.

La sua sintassi è:

MATCH(lookup_value, lookup_array, [match_type])Offerto da

Vediamo questi argomenti:

• lookup_value (obbligatorio) è il valore che vuoi trovare. Può essere un numero, testo, valore logico (TRUE/FALSE) o un riferimento a una cella.

• lookup_array (obbligatorio) è l'intervallo di celle che Excel analizzerà.

• match_type (facoltativo) definisce come Excel cerca la corrispondenza. Il valore predefinito è 1. Qui 1 trova il valore più grande minore o uguale a lookup_value (richiede ordine crescente). 0 trova la corrispondenza esatta (qualsiasi ordine). -1 trova il valore più piccolo maggiore o uguale a lookup_value (richiede ordine decrescente).

La funzione MATCH non fa distinzione tra maiuscole e minuscole, quindi tratta allo stesso modo lettere maiuscole e minuscole. Se lavori con testo e vuoi usare caratteri jolly come * (qualsiasi numero di caratteri) o ? (un singolo carattere), assicurati di impostare match_type su 0. Perché è l'unica modalità che supporta la ricerca con wildcard.

Come funzionano insieme INDEX() e MATCH() in Excel?

Uno degli usi più comuni di MATCH() è abbinarla alla funzione INDEX(). Mentre MATCH() trova la posizione di un valore, INDEX() recupera il valore in quella posizione. 

Annidare MATCH() dentro INDEX() ti dà una formula dinamica e resistente quando colonne o righe cambiano nel tempo. 

Per esempio, ho un dataset di esempio. E se voglio scoprire cosa c'è sulla stessa riga in colonna B rispetto a Banana in colonna A, posso usare INDEX MATCH insieme così:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))Offerto da

Qui, MATCH("Banana", A2:A5, 0) trova la posizione di Banana nella colonna A e INDEX(B2:B5, ...) restituisce il valore dalla stessa riga nella colonna B.

INDEX MATCH vs. VLOOKUP(): quale dovresti usare?

INDEX MATCH può fare cose che VLOOKUP() non può. Tuttavia, la scelta tra le due dipende dal livello di flessibilità e prestazioni di cui hai bisogno nei tuoi fogli Excel. Confrontiamole.

Flessibilità delle colonne e integrità dei dati

Uno dei maggiori svantaggi di VLOOKUP() è la sua dipendenza da numeri di colonna fissi. Se inserisci o sposti colonne, la formula può rompersi.

Prendi questo esempio:

Se viene aggiunta una nuova colonna tra Colonna A e Colonna B, la terza colonna si sposta e la formula restituisce risultati errati, come puoi vedere nell'immagine qui sotto.

=VLOOKUP("Apple", A2:C5, 2, FALSE)Offerto da

INDEX MATCH evita questo problema lavorando con intervalli definiti invece di posizioni di colonna hardcoded:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))Offerto da

Anche se le colonne vengono riordinate, questa formula resta efficace, risultando più affidabile e facile da mantenere. Qui, una colonna viene inserita prima della colonna B; la formula si aggiorna automaticamente e i risultati restano corretti. Questa è la magia di INDEX MATCH.

Direzione di ricerca e versatilità

VLOOKUP() può cercare solo da sinistra a destra. Significa che la tua colonna di ricerca deve essere sempre la prima nell'intervallo. Se il valore che ti serve è a sinistra della colonna di ricerca, VLOOKUP() non può trovarlo.

Tuttavia, se cerchiamo per nome del frutto per trovare il suo colore a destra, questa formula funzionerà. Per esempio, se vogliamo trovare il colore di una banana dal nostro elenco, dà il risultato corretto: 

Con INDEX MATCH questa limitazione non esiste. Puoi cercare in qualsiasi direzione: sinistra, destra, in alto o in basso. Supporta anche ricerche orizzontali, dandoti più controllo su come organizzare i tuoi dati. 

Applichiamo INDEX MATCH a questo esempio e vediamo come permette sia ricerche a sinistra che a destra.

Per la ricerca a sinistra, puoi usare la formula seguente, che darà l'output corretto: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))Offerto da

Per la ricerca a destra, puoi usare la formula seguente, che darà l'output corretto: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))Offerto da

Prestazioni ed efficienza in Excel

VLOOKUP() analizza l'intera tabella per trovare una corrispondenza e recuperare un valore in un'unica operazione. Ma INDEX MATCH divide il compito: MATCH() trova la riga o la colonna e INDEX() recupera il valore. 

Questo lo rende più veloce e meno dispendioso in memoria, soprattutto in cartelle di lavoro complesse o di grandi dimensioni.

Ecco una tabella per darti un'idea più chiara di quali funzionalità supporta ciascuna funzione:

Quando usare INDEX MATCH

Puoi usare INDEX MATCH quando:

• Lavori con dataset di grandi dimensioni in cui la velocità conta
• Il tuo modello è dinamico, con colonne aggiunte o riordinate
• Devi cercare valori a sinistra o in intervalli orizzontali

Se i tuoi file Excel sono piccoli e semplici, VLOOKUP() può ancora andare bene. Ma per modellazione e reportistica avanzate, INDEX MATCH è una scelta più sicura.

Come usare INDEX MATCH passo dopo passo

Mettiamo che tu abbia due colonne: Frutti e Vendite. Vuoi trovare il valore delle vendite per "Apple".

Per farlo, digita la formula combinata INDEX MATCH e premi Invio:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Offerto da

Qui, MATCH("Apple", A2:A6, 0) trova la posizione di "Apple" nell'elenco Frutti e INDEX(B2:B6, ...) usa quella posizione per restituire il valore di vendita corrispondente dalla colonna Vendite.

Vedi che la formula ha restituito 12000, che è il valore delle vendite per Apple. Quindi, questo è il tuo risultato.

Evitare gli errori di INDEX MATCH

Sebbene INDEX MATCH sia un'ottima combinazione, può comunque generare errori se non impostata correttamente. Ecco come risolvere i problemi più comuni ed evitare errori che potrebbero compromettere le tue formule.

Errori #N/A e come risolverli

L'errore #N/A significa che Excel non ha trovato il valore che stai cercando. Alcune cause comuni possono essere:

• Errori di digitazione, spazi extra o testo non corrispondente (es., "apple" vs. "Apple")

• Nessun valore nell'intervallo di ricerca

• match_type sbagliato, ad esempio 1 invece di 0 per una corrispondenza esatta

• Caratteri nascosti (come spazi non separabili) nei dati

Ci sono quindi due modi per risolvere:

Usa TRIM() per ripulire spazi iniziali o finali:

=MATCH(TRIM("Apple"), A2:A6, 0)Offerto da

Aggiungi IFERROR() per mostrare un valore di fallback invece di un errore:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")Offerto da

Errori #REF! nelle formule INDEX MATCH

L'errore #REF! di solito indica un riferimento interrotto—spesso a causa di:

• Una mancata corrispondenza tra il numero di righe nei tuoi intervalli di INDEX() e MATCH()

• Righe o colonne eliminate usate nella tua formula

• Copia e incolla delle formule senza aggiornare i riferimenti di cella

Ma ci sono alcuni modi semplici per risolvere: 

• Assicurati che gli intervalli di INDEX() e MATCH() abbiano la stessa dimensione:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Offerto da

• Blocca gli intervalli con riferimenti assoluti (come $A$2:$A$6) per mantenere stabili le formule durante copia-incolla.

• Evita di eliminare celle da cui dipendono le formule.

Prestazioni lente e dataset di grandi dimensioni

Se lavori con migliaia di righe, formule INDEX MATCH non ottimizzate possono rallentare Excel.

Ecco come puoi ottimizzare:

• Limita gli intervalli di ricerca invece di fare riferimento a intere colonne:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))Offerto da

• Evita funzioni volatili come OFFSET(), INDIRECT() e NOW() vicino alle ricerche.

• Usa Tabelle di Excel o intervalli con nome per mantenere tutto organizzato e migliorare le prestazioni.

Gestire gli errori di spill in Excel 365

Excel 365 introduce le matrici dinamiche, che possono "spandere" i risultati nelle celle adiacenti. Se c'è un problema in questo processo, otterrai un errore #SPILL!.

Alcune cause comuni possono essere:

• Le celle adiacenti non sono vuote.
• La formula restituisce più valori in un intervallo di celle bloccato o unito.
• Stai usando una matrice dinamica dove è necessario un solo valore.

Ma questi problemi sono risolvibili. Ecco come superarli:

• Svuota le celle sotto o accanto alla formula.
• Separa eventuali celle unite nell'area di spill.
• Se vuoi un solo valore, forza un risultato singolo con @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Offerto da

Considerazioni finali

Se hai fatto affidamento su VLOOKUP(), ora è il momento di passare a qualcosa di più stabile. Puoi iniziare con alcune semplici ricerche usando INDEX MATCH in una copia del tuo file di lavoro. Prova a sostituire la nuova formula dove i cambi di colonna causano errori o dove la struttura dei dati richiede flessibilità. Una volta visto con quanta pulizia gestisce questi casi, sarà difficile tornare indietro.