Come usare INDEX MATCH con più criteri in Excel

Un paio d’anni fa ho lavorato all’analisi di una campagna marketing in cui dovevo confrontare le performance di vendita tra più regioni. I dati erano sparsi su vari fogli Excel e dovevo recuperare i numeri di vendita di prodotti specifici in un unico report di riepilogo. All’inizio ho provato a cercare e copiare i dati manualmente, ma non era così semplice come pensavo. Basta una riga sbagliata e l’intero report può crollare.

È allora che ho scoperto INDEX MATCH(). Ci sono voluti alcuni tentativi per azzeccare la formula, ma una volta visto con quanta facilità riusciva a individuare e recuperare i numeri esatti di cui avevo bisogno, è entrata nella mia routine. Con sole due funzioni potevo estrarre esattamente i dati necessari, a prescindere da quanto fossero sparsi tra i fogli di calcolo.

In questo articolo ti spiego come fare lo stesso usando le funzioni INDEX() e MATCH(). Con Excel c’è sempre qualcosa di nuovo da imparare. Se sei alle prime armi, ti consiglio vivamente il nostro corso Introduction to Excel. Se hai più esperienza, prova il corso Advanced Excel Functions.

Ripasso su INDEX MATCH

INDEX MATCH è una scorciatoia per riferirsi alla combinazione di due funzioni di Excel che lavorano insieme per eseguire ricerche avanzate. Potremmo anche chiamarla INDEX(MATCH()), ma in questo articolo userò INDEX MATCH. Vediamo ciascuna funzione:

La funzione INDEX() restituisce il valore di una cella in base alla sua posizione all’interno di un intervallo specificato. Ecco la sua sintassi:

=INDEX(array, row_num, [column_num])

Qui:

• array è l’intervallo di celle da cui vuoi recuperare un valore.

• row_num è il numero di riga nell’array da cui restituire un valore.

• column_num (opzionale) è il numero di colonna nell’array da cui restituire un valore.

La funzione MATCH() individua la posizione relativa di un valore all’interno di un intervallo. La sua sintassi è:

=MATCH(lookup_value, lookup_array, [match_type])

Qui:

• lookup_value è il valore che vuoi trovare.

• lookup_array è l’intervallo in cui la funzione cerca il valore.

match_type è opzionale. 1 (predefinito) trova il valore minore o uguale a lookup_value (l’array deve essere ordinato in ordine crescente). 0 trova una corrispondenza esatta (l’array non deve essere ordinato). -1 trova il valore più piccolo maggiore o uguale a lookup_value (l’array deve essere ordinato in ordine decrescente).

Come combinare INDEX() con MATCH()

Annidando MATCH() dentro INDEX(), possiamo creare una ricerca dinamica. Capciamolo con un esempio: supponiamo che tu voglia trovare la posizione di David Wilson nel dataset. Invece di inserire manualmente il numero di riga in INDEX(), usa MATCH() per determinarlo:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

Nella formula sopra, MATCH("David Wilson", A2:A6, 0) restituisce 4, che è la posizione di riga. E INDEX(C2:C6, 4) recupera il valore dalla 4ª riga dell’intervallo C2:C6, che è Seattle.

Combina INDEX() con MATCH(). Immagine dell’autore.

Per renderla ancora più dinamica, puoi sostituire il valore fisso David Wilson con un riferimento di cella. In questo modo, la formula si adatta automaticamente in base al valore in D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Sostituisci il valore fisso in INDEX MATCH. Immagine dell’autore.

INDEX MATCH vs. VLOOKUP()

Ora che sai come funzionano INDEX() e MATCH() singolarmente e come la loro combinazione renda le ricerche più dinamiche, vediamo perché INDEX MATCH è una scelta migliore rispetto a VLOOKUP().

• A differenza di VLOOKUP(), che richiede che la colonna di ricerca sia a sinistra, INDEX MATCH ti permette di recuperare dati da qualsiasi colonna, indipendentemente dalla posizione.

• INDEX MATCH elabora solo l’intervallo di celle necessario, mentre VLOOKUP() scansiona intere tabelle. 

• Le formule che usano VLOOKUP() possono rompersi se si inseriscono o eliminano colonne, poiché si basano su indici di colonna statici. INDEX MATCH, invece, fa riferimento a intervalli dinamici per mantenere intatte le formule nonostante i cambiamenti strutturali dei dati.

• Con INDEX MATCH non dobbiamo contare manualmente i numeri di colonna. Specifica la colonna di ricerca e quella di ritorno, e hai finito. 

INDEX MATCH con più criteri

Mi capita spesso di lavorare con dataset che contengono duplicati, e trovare valori al loro interno è davvero difficile. Ora però uso INDEX MATCH perché gestisce questi scenari con grande facilità, a differenza di altre formule di ricerca standard. Ti mostro passo passo come lo utilizzo.

Prepara i dati per più criteri

Per prima cosa, crea il tuo dataset e assicurati che sia ben organizzato in una tabella con intestazioni chiare per ogni colonna. Ogni riga dovrebbe rappresentare un record univoco e ogni colonna dovrebbe contenere un attributo specifico.

Ad esempio, ecco un dataset di prova:

Dataset per INDEX MATCH con più criteri. Immagine dell’autore.

Scrivi la formula per più criteri

Una volta che i dati sono organizzati correttamente, è il momento di scrivere la formula. La formula INDEX MATCH recupera un valore da un’altra colonna identificando una riga che soddisfa più condizioni. Questo si fa combinando test logici all’interno della funzione MATCH() e incorporandola nella funzione INDEX().

Ecco la sintassi di base: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Qui: 

• Return_range è l’intervallo da cui verrà restituito il valore.

• Criteria1, Criteria2, … sono le condizioni da soddisfare.

• Range1, Range2, … sono gli intervalli corrispondenti ai criteri.

Ora che abbiamo impostato i dati, guardiamo da vicino i due metodi per rispondere alla nostra domanda: come usare INDEX MATCH con più criteri.

Usa colonne di supporto per criteri complessi

Se il tuo dataset ha più condizioni, usa colonne di supporto per semplificare il processo. Combinerà tutte le condizioni in un’unica colonna per ricerche più semplici. Ad esempio, sto usando lo stesso dataset per creare una colonna di supporto combinando le colonne First Name e Salary:

=A2&B2

Crea una colonna di supporto. Immagine dell’autore.

Questa colonna di supporto semplifica la mia formula INDEX MATCH. Invece di scrivere una complessa formula matriciale con più condizioni, faccio riferimento alla colonna di supporto nella formula per un approccio più semplice:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH con colonna di supporto. Immagine dell’autore.

Combina più criteri con una formula matriciale

Se non vuoi usare colonne di supporto, puoi ottenere lo stesso risultato con formule matriciali. Ti permettono di valutare più criteri direttamente nella funzione MATCH(). Ad esempio, ecco come trovo lo stipendio di Alice nel reparto HR:

Passo 1: Scrivi la funzione MATCH() con condizioni logiche:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

In questa formula, 1 fa sì che MATCH() cerchi le righe in cui tutte le condizioni sono vere. (F4=A2:A11) verifica se il valore in F4 coincide con qualche valore nell’intervallo A2:A11. (F5=B2:B11) verifica se il valore in F5 coincide con qualche valore nell’intervallo B2:B11. L’operatore * agisce come un AND logico, assicurando che tutte le condizioni siano soddisfatte.

Passo 2: Inserisci questa funzione MATCH() dentro la funzione INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Passo 3: Finalizza la formula. Se usi una versione meno recente di Excel, premi Ctrl+Shift+Invio per trasformarla in formula matriciale. Nelle versioni più recenti, premi Invio.

INDEX MATCH matriciale con più criteri. Immagine dell’autore.

Usi avanzati di INDEX MATCH con più criteri

Con INDEX MATCH puoi fare molto di più. Vediamo come: 

Usa INDEX MATCH con intervalli denominati e dinamici

Uso gli intervalli denominati in Excel per assegnare nomi significativi come results o totalSales invece dei riferimenti standard come A1:A10. Così è più semplice gestire le formule tra fogli diversi. 

Per assegnare un nome a un intervallo di celle, seleziona le celle e premi Ctrl + F3 (Windows) oppure Cmd + F3 (Mac) per aprire il Gestione Nomi. Poi clicca su Nuovo, inserisci un nome e fai clic su OK.

Assegna un nome all’intervallo. Immagine dell’autore.

L’unica differenza tra un intervallo denominato e uno dinamico è che un intervallo denominato fa riferimento a un gruppo fisso di celle, mentre un intervallo dinamico si adatta automaticamente quando i dati vengono aggiunti o rimossi.

Per impostare un intervallo dinamico, seleziona le celle. Nella scheda Formule, fai clic su Gestione Nomi oppure premi Ctrl + F3 per aprire il Gestione Nomi di Excel e fai clic su Nuovo. Apparirà la finestra di dialogo Nuovo nome. Ora, nel campo Nome inserisci il nome desiderato. Poi, nel campo Riferito a inserisci la formula per l’intervallo dinamico. 

Imposta un intervallo dinamico. Immagine dell’autore. 

Ora vediamo un esempio: ho definito due intervalli dinamici e uno statico:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Ora uso questi intervalli nella formula INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

E come vedi, con nomi chiari la formula diventa molto più facile da capire.

Usa intervalli dinamici e denominati con INDEX MATCH. Immagine dell’autore.

INDEX MATCH annidati per ricerche complesse

Oltre al lavoro di base, puoi usare funzioni INDEX MATCH annidate per gestire anche ricerche complesse. Per esempio, ho un dataset che mostra le vendite per categoria di prodotto in diverse regioni. 

Dataset grezzo. Immagine dell’autore.

Voglio trovare le vendite di mobili nella East. Ma per farlo devo far coincidere sia la categoria di prodotto (riga) sia la regione (colonna), cosa che un semplice INDEX MATCH non può fare. Per questo qui uso la seguente formula INDEX MATCH annidata:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Ecco come funziona: INDEX() estrae un valore dall’intervallo B2:D4, ma ha bisogno di un numero di riga e uno di colonna per sapere esattamente dove cercare. Quindi, il primo MATCH(D6, A2:A4, 0) determina il numero di riga. Se D6 contiene Furniture, cerca nella colonna A2:A4 e lo trova nella seconda riga.

Poi MATCH(D7, B1:D1, 0) determina il numero di colonna. Se D7 contiene East, guarda lungo B1:D1 e lo trova nella seconda colonna.

Una volta che INDEX() conosce riga e colonna, mostra i valori di output. Nel nostro caso, le vendite per Furniture nella East sono 450.

Usa INDEX MATCH annidati. Immagine dell’autore.

Preferisco questa formula alla ricerca manuale tra righe e colonne perché gestisce tutto con precisione. 

Sfide comuni e suggerimenti per la risoluzione

Quando ho iniziato a usare INDEX MATCH, mi sono imbattuto in diverse difficoltà e non voglio che tu provi le stesse frustrazioni. Quindi, ti guido tra le problematiche più comuni e ti mostro come superarle.

Gestire gli errori nelle formule INDEX MATCH

Errori come #N/A e #VALUE! possono sembrare frustranti all’inizio, ma si risolvono piuttosto facilmente. Vediamo come capire cosa li causa e i semplici passaggi per risolverli.

L’errore #N/A si verifica quando la funzione MATCH() non trova un valore. Questo accade perché il valore cercato non esiste nell’array di ricerca o i dati contengono spazi nascosti. Per esempio, una volta ho fatto riferimento alla colonna sbagliata mentre estraevo i nomi dei dipendenti:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

Errore #N/A in INDEX MATCH. Immagine dell’autore.

Per risolvere problemi simili, verifica che il valore cercato esista nell’array e usa la funzione TRIM() per ripulire gli spazi:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

Errore #N/A risolto in INDEX MATCH. Immagine dell’autore.

#VALUE! compare quando la formula non è impostata come formula matriciale. Ad esempio, se uso la funzione MATCH() e includo più di un intervallo, Excel la considera una formula matriciale. Tuttavia, se non è configurata correttamente, Excel restituirà un errore #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

Errore #VALUE in INDEX MATCH. Immagine dell’autore.

Per risolverlo, premi Ctrl + Shift + Invio dopo aver inserito la formula. In questo modo Excel racchiuderà la formula tra parentesi graffe {}, indicando che è ora una formula matriciale. Ma non digitare le parentesi manualmente perché la formula si romperà.

Errore #VALUE risolto in INDEX MATCH. Immagine dell’autore.

Ottimizza le prestazioni con dataset grandi

Con dataset più grandi, a volte le mie formule rallentavano e dovevo aspettare l’aggiornamento dei calcoli. Se anche tu stai affrontando problemi simili, prova questi suggerimenti: 

• Limita l’intervallo di ricerca: Restringi gli intervalli a ciò che è strettamente necessario. Ad esempio, invece di A:A, usa A1:A100 per ridurre i tempi di calcolo.

• Usa colonne di supporto: Precalcola criteri complessi con colonne di supporto. Questo ridurrà il carico computazionale delle formule matriciali.

• Attiva la modalità di calcolo manuale: Passa Excel alla modalità di calcolo manuale per evitare ricalcoli continui. Dopo le modifiche, premi F9 per aggiornare le formule manualmente.

• Evita funzioni volatili: Riduci al minimo l’uso di funzioni volatili come NOW(), RAND() e TODAY() in combinazione con INDEX MATCH. Queste funzioni innescano ricalcoli a ogni aggiornamento della cartella di lavoro.

Considerazioni finali 

INDEX MATCH fa risparmiare tempo e semplifica analisi di dati complesse. Se lavori con dataset molto grandi, vale la pena provarlo. Ma il modo migliore per fissare i concetti è la pratica. Quindi, direi di affrontare qualche dataset ed esercitarti con ciò che hai imparato. È così che ho affinato le mie competenze. 

Per approfondire, dai un’occhiata al nostro corso Advanced Excel Functions per padroneggiare una gamma più ampia di strumenti potenti. Se invece vuoi costruire una competenza completa nell’analisi dei dati in Excel, ti consiglio il corso Data Analysis in Excel. Copre tutto, dalla preparazione dei dati alla visualizzazione.