Excel INDEX MATCH: Ricerche di base e avanzate

In Excel, recuperare con precisione dati specifici è spesso una necessità frequente. Sebbene le funzioni INDEX e MATCH abbiano ciascuna i propri punti di forza, la loro combinazione sblocca un potente set di strumenti per la Ricerca. Insieme, permettono una vasta gamma di ricerche, dalle più semplici ricerche orizzontali e verticali fino a funzionalità avanzate come ricerche bidirezionali, sensibili alle maiuscole/minuscole e su più criteri. Rispetto a CERCA.VERT, l'accoppiata INDEX e MATCH offre possibilità di ricerca molto più ampie. In questo tutorial, esploriamo in profondità tutte le potenzialità che possono raggiungere insieme.

• Funzione INDEX
• Funzione MATCH
• Combinare INDEX e MATCH

• Ricerca bidirezionale
• Ricerca a sinistra
• Ricerca sensibile alle maiuscole/minuscole
• Ricerca della corrispondenza più vicina
• Ricerca con criteri multipli
• Ricerca su più colonne
• Ricerca del primo valore non vuoto
• Ricerca del primo valore numerico
• Ricerca di associazioni MAX o MIN
• Suggerimento: Personalizza i tuoi messaggi di errore #N/A

Come usare INDEX e MATCH in Excel

Prima di utilizzare le funzioni INDEX e MATCH, assicuriamoci di capire in che modo possono aiutarci a cercare valori.

Come usare la funzione INDEX in Excel

La funzione INDEX in Excel restituisce il valore in una posizione specifica di un determinato intervallo. La sintassi della funzione INDEX è la seguente:

• array (obbligatorio) si riferisce all'intervallo da cui si desidera restituire il valore.
• row_num (obbligatorio, a meno che column_num sia presente) indica il numero di riga dell'intervallo.
• column_num (opzionale, ma richiesto se row_num è omesso) indica il numero di colonna dell'intervallo.

Ad esempio, per conoscere il punteggio di Jeff, il6° studente nell'elenco, puoi utilizzare la funzione INDEX in questo modo:

=INDEX(C2:C11,6)

√ Nota: L'intervallo C2:C11 è dove sono elencati i punteggi, mentre il numero 6 individua il punteggio d'esame del 6° studente.

Facciamo ora una piccola prova. Per la formula =INDEX(A1:C1,2), quale valore restituirà? --- Esatto, restituirà Birth date, il secondo valore della riga indicata.

Ora sappiamo che la funzione INDEX può lavorare perfettamente sia con intervalli orizzontali che verticali. Ma se abbiamo bisogno di restituire un valore in un intervallo più ampio con più righe e colonne? In questo caso, dobbiamo fornire sia il numero di riga che quello di colonna. Ad esempio, per trovare il punteggio di Jeff all'interno di una tabella invece che in una sola colonna, possiamo individuarlo con un numero di riga pari a6 e un numero di colonna pari a3 nelle celle da A2 a C11, come segue:

=INDEX(A2:C11,6,3)

Tuttavia, per un database molto grande con molte righe e colonne, non è certo comodo applicare la formula indicando manualmente il numero esatto di riga e colonna. È proprio in questi casi che conviene combinare l'uso della funzione MATCH.

Come usare la funzione MATCH in Excel

La funzione MATCH in Excel restituisce un valore numerico che rappresenta la posizione di un elemento specifico nell'intervallo fornito. La sintassi della funzione MATCH è la seguente:

• lookup_value (obbligatorio) si riferisce al valore da cercare nel lookup_array.
• lookup_array (obbligatorio) indica l'intervallo di celle in cui desideri che MATCH effettui la ricerca.
• match_type (opzionale): 1, 0 oppure -1.
  • 1(predefinito), MATCH troverà il valore più grande che è minore o uguale al lookup_value. I valori nel lookup_array devono essere in ordine crescente.
  • 0, MATCH troverà il primo valore che è esattamente uguale al lookup_value. I valori nel lookup_array possono essere in qualsiasi ordine. (Se il tipo di corrispondenza è impostato su0, puoi utilizzare i caratteri jolly.)
  • -1, MATCH troverà il valore più piccolo che è maggiore o uguale al lookup_value. I valori nel lookup_array devono essere in ordine decrescente.

Ad esempio, per conoscere la posizione di Vera nell'elenco dei nomi, puoi usare la formula MATCH così:

=MATCH("Vera",A2:A11,0)

√ Nota: Il risultato “4” indica che il nome “Vera” si trova nella quarta posizione dell'elenco.

Ora che conosciamo le basi delle funzioni INDEX e MATCH in Excel, rimbocchiamoci le maniche e prepariamoci a combinarle.

Come combinare INDEX e MATCH in Excel

Guarda l'esempio qui sotto per capire come possiamo combinare le funzioni INDEX e MATCH:

Per trovare il punteggio di Evelyn, sapendo che i punteggi degli esami sono nella terza colonna, possiamo utilizzare la funzione MATCH per determinare automaticamente la posizione della riga senza doverla contare manualmente. Successivamente, possiamo usare la funzione INDEX per recuperare il valore all'intersezione della riga individuata e della terza colonna:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Poiché la formula potrebbe sembrare un po' complessa, analizziamola passo dopo passo.

La formula INDEX contiene tre argomenti:

• row_num: MATCH("Evelyn",A2:A11,0)fornisce a INDEX la posizione di riga del valore "Evelyn" nell'intervallo A2:A11, che è5.
• column_num:3 indica la 3za colonna che INDEX deve utilizzare per trovare il punteggio nell'array.
• array: A2:C11 indica a INDEX di restituire il valore corrispondente all'intersezione della riga e colonna specificate, nell'intervallo che va da A2 a C11. Infine, otteniamo il risultato90.

Nella formula sopra, abbiamo utilizzato un valore fisso, "Evelyn". Tuttavia, nella pratica, i valori fissi non sono pratici, perché dovresti modificarli ogni volta che vuoi cercare dati diversi, come il punteggio di un altro studente. In questi casi, puoi utilizzare riferimenti di cella per creare formule dinamiche. Ad esempio, qui sostituirò "Evelyn" con F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Esempi di formule INDEX e MATCH

In questa sezione, vedremo diversi casi in cui usare le funzioni INDEX e MATCH per soddisfare varie esigenze.

INDEX e MATCH per effettuare una ricerca bidirezionale

Nell'esempio precedente, conoscevamo il numero di colonna e usavamo una formula MATCH per trovare il numero di riga. Ma se non conosciamo nemmeno il numero di colonna?

In questi casi, possiamo eseguire una ricerca bidirezionale, nota anche come ricerca matriciale, utilizzando due funzioni MATCH: una per trovare il numero di riga e l'altra per determinare il numero di colonna. Ad esempio, per conoscere il punteggio di Evelyn, dovremmo usare la formula:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX e MATCH per effettuare una ricerca a sinistra

Consideriamo ora uno scenario in cui devi determinare la classe di Evelyn. Potresti aver notato che la colonna delle classi si trova a sinistra rispetto a quella dei nomi, una situazione che va oltre le capacità di un'altra potente funzione di ricerca di Excel, CERCA.VERT.

In realtà, la possibilità di effettuare ricerche a sinistra è uno degli aspetti in cui la combinazione di INDEX e MATCH supera CERCA.VERT.

Per trovare la classe di Evelyn, utilizza la seguente formula per cercare Evelyn in B2:B11 e recuperare il valore corrispondente da A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX e MATCH per effettuare una ricerca sensibile alle maiuscole/minuscole

La funzione MATCH, di default, non distingue tra maiuscole e minuscole. Tuttavia, se hai bisogno che la formula distingua tra lettere maiuscole e minuscole, puoi migliorarla integrando la EXACT. Combinando MATCH con EXACT all'interno di una formula INDEX, puoi effettuare una ricerca sensibile alle maiuscole/minuscole, come mostrato di seguito:

• array indica l'intervallo da cui si desidera restituire il valore.
• lookup_value indica il valore da cercare, considerando le maiuscole/minuscole, nel lookup_array.
• lookup_array indica l'intervallo di celle in cui MATCH deve confrontare con lookup_value.

Ad esempio, per conoscere il punteggio d'esame di JIMMY, usa la seguente formula:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Nota: Questa è una formula matriciale che richiede l'inserimento con Ctrl + Shift + Enter, tranne che in Excel365, Excel2021 e versioni successive.

INDEX e MATCH per trovare la corrispondenza più vicina

In Excel, potresti trovarti nella situazione di dover trovare la corrispondenza più vicina a un valore specifico all'interno di un set di dati. In questi casi, la combinazione delle funzioni INDEX e MATCH, insieme a ABS e MIN, può essere estremamente utile.

• array indica l'intervallo da cui si desidera restituire il valore.
• lookup_array indica l'intervallo di valori in cui cercare la corrispondenza più vicina a lookup_value.
• lookup_value indica il valore di cui trovare la corrispondenza più vicina.

Ad esempio, per scoprire chi ha il punteggio più vicino a85, usa la seguente formula per cercare il punteggio più vicino a85 in C2:C11 e recuperare il valore corrispondente da A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Nota: Questa è una formula matriciale che richiede l'inserimento con Ctrl + Shift + Enter, tranne che in Excel365, Excel2021 e versioni successive.

INDEX e MATCH per effettuare una ricerca con criteri multipli

Per trovare un valore che soddisfa più condizioni, e che richiede quindi la ricerca su due o più colonne, utilizza la seguente formula. Questa formula consente di effettuare una ricerca multi-condizione specificando diversi criteri su colonne differenti, aiutandoti a trovare il valore desiderato che soddisfa tutti i criteri indicati.

√ Nota: Questa è una formula matriciale che richiede l'inserimento con Ctrl + Shift + Enter. Una coppia di parentesi graffe apparirà nella barra della formula.

• array indica l'intervallo da cui si desidera restituire il valore.
• (lookup_value=lookup_array) rappresenta una singola condizione. Questa condizione verifica se un determinato lookup_value corrisponde ai valori nel lookup_array.

Ad esempio, per trovare il punteggio di Coco della Classe A, nata il7/2/2008, puoi usare la seguente formula:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX e MATCH per effettuare una ricerca su più colonne

Immagina uno scenario in cui hai a che fare con più colonne di dati. La prima colonna funge da chiave per classificare i dati nelle altre colonne. Per determinare la categoria o la classificazione di una voce specifica, dovrai effettuare una ricerca tra le colonne dati e associarla alla chiave corrispondente nella colonna di riferimento.

Ad esempio, nella tabella qui sotto, come possiamo abbinare lo studente Shawn alla sua classe utilizzando INDEX e MATCH? Puoi farlo con una formula, ma la formula è piuttosto lunga e difficile sia da comprendere che da ricordare e digitare.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ecco perché la funzione Indice e confronto di colonne multiple di Kutools per Excel è così utile. Semplifica il processo, rendendo rapido e facile abbinare voci specifiche alle relative categorie. Per sfruttare questo potente strumento e abbinare facilmente Shawn alla sua classe, basta scaricare e installare il componente aggiuntivo Kutools per Excel e procedere come segue:

1. Seleziona la cella di destinazione in cui vuoi visualizzare la classe corrispondente.
2. Nella scheda Kutools, clicca su Assistente formula > Ricerca & Riferimento > Indice e confronto di colonne multiple.
3. 
3. Nella finestra di dialogo che si apre, procedi così:
   1. Clicca sul primo pulsante accanto a Colonna di ricerca per selezionare la colonna che contiene le informazioni chiave che vuoi restituire, ad esempio i nomi delle classi. (Qui puoi selezionare solo una colonna.)
   2. Clicca sul secondo pulsante accanto a Intervallo tabella per selezionare le celle da confrontare con i valori nella Colonna di ricerca, cioè i nomi degli studenti.
   3. Clicca sul terzo pulsante accanto a Valore di ricerca per selezionare la cella che contiene il nome dello studente da abbinare alla classe, in questo caso Shawn.
   4. Clicca su OK.
   5. 

Risultato

Kutools ha generato automaticamente la formula e vedrai subito il nome della classe di Shawn visualizzato nella cella di destinazione.

INDEX e MATCH per cercare il primo valore non vuoto

Per recuperare il primo valore non vuoto, ignorando gli errori, da una colonna o una riga, puoi utilizzare una formula basata sulle funzioni INDEX e MATCH. Tuttavia, se non vuoi ignorare gli errori dal tuo intervallo, aggiungi la funzione ISBLANK.

• Ottieni il primo valore non vuoto in una colonna o riga ignorando gli errori:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Ottieni il primo valore non vuoto in una colonna o riga includendo gli errori:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX e MATCH per cercare il primo valore numerico

Per recuperare il primo valore numerico da una colonna o una riga, utilizza la formula basata sulle funzioni INDEX, MATCH e ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX e MATCH per cercare associazioni MAX o MIN

Se hai bisogno di recuperare un valore associato al valore massimo o minimo all'interno di un intervallo, puoi utilizzare le funzioni MAX o MIN insieme a INDEX e MATCH.

• INDEX e MATCH per recuperare un valore associato al valore massimo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX e MATCH per recuperare un valore associato al valore minimo:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• Nelle formule sopra ci sono due argomenti:
• • array indica l'intervallo da cui si desidera restituire le informazioni correlate.
  • lookup_array rappresenta l'insieme di valori da esaminare o cercare in base a criteri specifici, ad esempio valori massimi o minimi.

Ad esempio, se vuoi sapere chi ha ottenuto il punteggio più alto, utilizza la seguente formula:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Suggerimento: Personalizza i tuoi messaggi di errore #N/A

Quando lavori con le funzioni INDEX e MATCH di Excel, potresti incontrare l'errore #N/A quando non c'è alcun risultato corrispondente. Ad esempio, nella tabella qui sotto, cercando di trovare il punteggio di una studentessa di nome Samantha, compare un errore #N/A perché non è presente nel set di dati.

Per rendere i tuoi fogli di calcolo più intuitivi, puoi personalizzare questo messaggio di errore racchiudendo la formula INDEX MATCH nella funzione IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Sopra trovi tutti i contenuti relativi alle funzioni INDEX e MATCH in Excel. Spero che il tutorial ti sia stato utile. Se vuoi scoprire altri suggerimenti e trucchi per Excel, clicca qui per accedere alla nostra vasta raccolta di migliaia di tutorial.