INDEX e CONFRONTA in Excel: ricerche base e avanzate

In Excel, recuperare dati specifici in modo accurato è spesso un’esigenza ricorrente. Sebbene le funzioni INDICE e CONFRONTA abbiano ciascuna i propri punti di forza, combinarle sblocca un potente set di strumenti per la ricerca dei dati. Insieme, offrono un’ampia gamma di capacità: dalle ricerche orizzontali e verticali di base fino a funzionalità avanzate come ricerche bidirezionali, con distinzione tra maiuscole e minuscole e basate su criteri multipli. Superando le limitazioni di CERCA.VERT, l’accoppiata INDICE e CONFRONTA garantisce maggiore flessibilità e un ventaglio più ampio di opzioni per il recupero dati. In questo tutorial, esploreremo in profondità tutto ciò che queste due funzioni possono fare insieme.

• Funzione INDICE
• Funzione CONFRONTA
• Combinare INDICE e CONFRONTA

• Ricerca bidirezionale
• Ricerca a sinistra
• Ricerca con distinzione tra maiuscole e minuscole
• Corrispondenza più vicina
• Ricerca con criteri multipli
• Ricerca su più colonne
• Ricerca del primo valore non vuoto
• Ricerca del primo valore numerico
• Ricerca associazioni di valori MAX o MIN
• Suggerimento: personalizza i tuoi messaggi di errore #N/D

Come usare INDICE e CONFRONTA in Excel

Prima di utilizzare le funzioni INDICE e CONFRONTA, assicuriamoci innanzitutto di comprendere come possano aiutarci a trovare i valori desiderati.

Come usare la funzione INDICE in Excel

La funzione INDICE in Excel restituisce il valore presente in una determinata posizione all’interno di un intervallo specifico. La sintassi della funzione INDICE è la seguente:

• matrice (obbligatorio) indica l'intervallo da cui si desidera recuperare il valore.
• num_riga(obbligatorio, a meno che)num_colonna non sia presente) indica il numero di riga della matrice.
• num_colonna(facoltativo, ma obbligatorio se)num_riga è omesso) indica il numero di colonna della matrice.

Ad esempio, per conoscere il punteggio di Jeff, lo 6° studente dell’elenco, è possibile utilizzare la funzione INDICE in questo modo:

=INDEX(C2:C11,6)

√ Nota: L’intervallo C2:C11contiene i punteggi, mentre il numero 6individua il punteggio d’esame dello 6° studente.

Facciamo ora un piccolo test. Per la formula =INDICE(A1:C1;2), quale valore restituirà? — Sì, restituirà Data di nascita, il 2° valore nella riga indicata.

Ora sappiamo che la funzione INDICE funziona perfettamente con intervalli orizzontali o verticali. Ma cosa succede se dobbiamo restituire un valore in un intervallo più ampio, con diverse righe e colonne? In tal caso, dobbiamo specificare sia un numero di riga che un numero di colonna. Ad esempio, per trovare il punteggio di Jeff all’interno dell’intervallo tabellare anziché in una singola colonna, possiamo individuare il suo punteggio con un numero di riga pari a 6 e un numero di colonna pari a 3 nell’intervallo di celle da A2 a C11 in questo modo:

=INDEX(A2:C11,6,3)

Tuttavia, in un database molto grande con numerose righe e colonne, utilizzare una formula con riferimenti esatti a righe e colonne non è certo pratico. Ed è proprio in questi casi che conviene abbinare la funzione CONFRONTA.

Come usare la funzione CONFRONTA in Excel

La funzione CONFRONTA in Excel restituisce un valore numerico che indica la posizione di un elemento specifico all’interno di un intervallo assegnato. La sintassi della funzione CONFRONTA è la seguente:

• valore_ricercato (obbligatorio) indica il valore da cercare nella matrice_ricerca.
• matrice_ricerca (obbligatorio) indica l'intervallo di celle in cui CONFRONTA deve effettuare la ricerca.
• tipo_confronto(facoltativo):1,0oppure -1.
  • 1 (predefinito): CONFRONTA troverà il valore più grande minore o uguale al valore_ricercato. I valori nell’intervallo_ricerca devono essere disposti in ordine crescente.
  • 0, CONFRONTA troverà il primo valore che corrisponde esattamente al valore_ricercato. I valori nell’intervallo_ricerca possono essere in qualsiasi ordine. (Nei casi in cui il tipo di confronto è impostato su 0, puoi usare caratteri jolly.)
  • -1, CONFRONTA troverà il valore più piccolo che è maggiore o uguale al valore_ricercato. I valori nell’intervallo_ricerca devono essere disposti in ordine decrescente.

Ad esempio, per conoscere la posizione di Vera nella colonna Elenco nomi, è possibile utilizzare la funzione Distingui formule in questo modo:

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

√ Nota: Il risultato «4» indica che il nome «Vera» occupa la quarta posizione nell’elenco.

Ora che abbiamo visto gli utilizzi di base delle funzioni INDICE e CONFRONTA in Excel, prepariamoci a combinarle per ottenere risultati ancora più potenti.

Come combinare INDICE e CONFRONTA in Excel

Si consideri l’esempio seguente per capire come combinare le funzioni INDICE e CONFRONTA:

Per trovare il punteggio di Evelyn, sapendo che i punteggi d’esame si trovano nella 3ª colonna, possiamo utilizzare la funzione CONFRONTA per determinare automaticamente la posizione della riga senza doverla contare manualmente. Successivamente, possiamo impiegare la funzione INDICE per recuperare il valore all’intersezione della riga identificata e della 3ª colonna:

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

Poiché la formula potrebbe sembrare un po’ complessa, esaminiamone ogni singola parte.

La formula INDICEcontiene tre argomenti:

• num_riga:CONFRONTA(«Evelyn»,A2:A11,0)fornisce a INDICE la posizione della riga del valore "Evelyn" nell'intervallo A2:A11, che corrisponde a5.
• num_colonna: 3 specifica la 3ª colonna in cui INDICE deve individuare il punteggio all'interno della matrice.
• matrice: A2:C11 indica a INDICE di restituire il valore corrispondente all'intersezione della riga e della colonna specificate, all'interno dell'intervallo compreso tra A2 e C11. Infine, otteniamo il risultato 90.

Nella formula precedente abbiamo utilizzato un valore fisso: «Evelyn». Tuttavia, nella pratica, i valori fissi non sono pratici, perché richiederebbero di modificare la formula ogni volta che si vuole cercare dati diversi, ad esempio il punteggio di un altro studente. In questi casi, è possibile usare riferimenti di cella per creare formule dinamiche. Ad esempio, qui sostituirò «Evelyn» con F2:

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

Esempi con INDICE e Distingui formule

In questa sezione esploreremo diversi contesti in cui sfruttare al meglio le funzioni INDICE e CONFRONTA per rispondere a esigenze specifiche.

INDICE e CONFRONTA per una ricerca bidirezionale

Nell’esempio precedente conoscevamo il numero di colonna e abbiamo usato una funzione Distingui formule per individuare il numero di riga. Ma cosa possiamo fare se non conosciamo neppure il numero di colonna?

In questi casi, puoi eseguire una ricerca bidirezionale—nota anche come ricerca matriciale—usando due funzioni CONFRONTA: una per individuare il numero di riga e l’altra per determinare il numero di colonna. Ad esempio, per scoprire il punteggio di Evelyn, utilizza la seguente formula:

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

INDICE e CONFRONTA per una ricerca a sinistra

Consideriamo ora uno scenario in cui è necessario determinare la classe di Evelyn. Si noterà che la colonna della classe si trova a sinistra di quella dei nomi, una situazione che va oltre le capacità di un’altra potente funzione di ricerca di Excel: CERCA.VERT.

In effetti, la capacità di eseguire ricerche verso sinistra è uno degli aspetti in cui la combinazione di INDICE e CONFRONTA si rivela superiore a 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))

INDICE e CONFRONTA per una ricerca con distinzione tra maiuscole e minuscole

La funzione CONFRONTA non distingue per impostazione predefinita tra maiuscole e minuscole. Tuttavia, quando è necessario che la formula riconosca la differenza tra caratteri maiuscoli e minuscoli, puoi potenziarla integrando la funzione IDENTICO. Combinando CONFRONTA con IDENTICO all’interno di una formula INDICE, è possibile eseguire in modo efficace una ricerca sensibile alle maiuscole/minuscole, come illustrato di seguito:

• matrice indica l'intervallo da cui si desidera recuperare il valore.
• valore_ricercato indica il valore da abbinare, rispettando maiuscole e minuscole, nella matrice_ricerca.
• matrice_ricerca indica l'intervallo di celle in cui CONFRONTA deve confrontare il valore_ricercato.

Ad esempio, per conoscere il punteggio d’esame di JIMMY, utilizzare la seguente formula:

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

√ Nota: Questa è una formula di matrice che richiede di essere inserita con Ctrl+Maiusc+Invio, tranne in Excel 365, Excel 2021 e versioni successive.

INDICE e CONFRONTA per trovare la corrispondenza più vicina

In Excel, possono verificarsi situazioni in cui è necessario individuare il valore più vicino a un dato specifico all’interno di un set di dati. In questi casi, la combinazione delle funzioni INDICE e CONFRONTA con le funzioni ASS e MIN si rivela estremamente efficace.

• matrice indica l'intervallo da cui si desidera restituire il valore.
• matrice_ricerca indica l'intervallo di valori in cui si desidera trovare la corrispondenza più vicina al valore_ricercato.
• valore_ricercato indica il valore per cui si desidera trovare la corrispondenza più vicina.

Ad esempio, per scoprire di chi è il punteggio più vicino a 85, utilizza la seguente formula per cercare il punteggio più vicino a 85 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 di matrice che richiede di essere inserita con Ctrl+Maiusc+Invio, tranne in Excel 365, Excel 2021 e versioni successive.

INDICE e CONFRONTA per una ricerca con criteri multipli

Per trovare un valore che soddisfi più condizioni, effettuando una ricerca su due o più colonne, utilizza la seguente formula. Questa ti permette di eseguire ricerche con criteri multipli, specificando diverse condizioni su colonne differenti e individuando facilmente il valore che soddisfa tutti i requisiti indicati.

√ Nota: questa è una formula di matrice che deve essere inserita con Ctrl+Maiusc+Invio. Apparirà quindi una coppia di parentesi graffe nella barra delle formule.

• matrice indica l'intervallo da cui si desidera restituire il valore.
• (valore_ricercato=matrice_ricerca) rappresenta una singola condizione. Questa condizione verifica se un determinato valore_ricercato corrisponde ai valori presenti nella matrice_ricerca.

Ad esempio, per trovare il punteggio di Coco della Classe A, la cui data di nascita è 7/2/2008, puoi utilizzare la seguente formula:

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

INDEX e CONFRONTA per eseguire una ricerca su più colonne

Immagina uno scenario in cui devi gestire 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, devi effettuare una ricerca tra le colonne dei dati e associarla alla chiave corrispondente nella colonna di riferimento.

Ad esempio, nella tabella seguente, come possiamo abbinare lo studente Shawn alla sua classe corrispondente utilizzando INDICE e CONFRONTA? È possibile farlo con una singola formula, ma risulta piuttosto lunga, poco intuitiva e difficile da ricordare o 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)), "")

È qui che entra in gioco la funzionalità Kutools per Excel's Indice e confronto di colonne multiple. Questo strumento semplifica il processo, permettendoti di abbinare rapidamente e facilmente voci specifiche alle rispettive categorie. Per sbloccare questa potente funzionalità e abbinare senza sforzo Shawn alla sua classe, ti basta scaricare e installare il componente aggiuntivo Kutools per Excel, quindi procedere come segue:

1. Seleziona la cella di destinazione in cui vuoi visualizzare la classe corrispondente.
2. Nella scheda Kutools, fai clic su Assistente formula > Ricerca e riferimento > Indice e confronto di colonne multiple.
3. 
3. Nella finestra di dialogo visualizzata, procedi come segue:
   1. Fai clic sul primo pulsante accanto a Lookup_col per selezionare la colonna contenente le informazioni chiave da restituire, ovvero i nomi delle classi. (Puoi selezionare solo una singola colonna.)
   2. Fai clic sul secondo pulsante accanto a Table_rng per selezionare le celle da confrontare con i valori della colonna selezionata Lookup_col, ovvero i nomi degli studenti.
   3. Fai clic sul terzo pulsante accanto a Lookup_value per selezionare la cella contenente il nome dello studente da abbinare alla sua classe—in questo caso, Shawn.
   4. Fai clic su OK.
   5. 

Risultato

Kutools ha generato automaticamente la formula e il nome della classe di Shawn apparirà immediatamente nella cella di destinazione.

INDEX e CONFRONTA per cercare il primo valore non vuoto

Per recuperare il primo valore non vuoto da una colonna o una riga, ignorando eventuali errori, puoi utilizzare una formula basata sulle funzioni INDEX e CONFRONTA. Tuttavia, se preferisci non ignorare gli errori presenti nell’intervallo, aggiungi la funzione VAL.VUOTO.

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

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

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

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

  

INDEX e CONFRONTA per cercare il primo valore numerico

Per recuperare il primo valore numerico da una colonna o una riga, utilizza la formula basata sulle funzioni INDICE, CONFRONTA e VAL.NUMERO.

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

INDEX e CONFRONTA per associazioni con il MASSIMO o il MINIMO

Per recuperare un valore associato al massimo o al minimo all’interno di un intervallo, puoi combinare le funzioni MAX o MIN con INDEX e CONFRONTA.

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

Ad esempio, se vuoi determinare 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/D

Quando utilizzi le funzioni INDICE e CONFRONTA di Excel, potresti imbatterti nell’errore #N/D se non esiste un risultato corrispondente. Ad esempio, nella tabella seguente, cercando il punteggio di una studentessa di nome Samantha, viene restituito l’errore #N/D perché il suo nome non è presente nel set di dati.

Per rendere i tuoi fogli di lavoro più intuitivi, puoi personalizzare questo messaggio di errore racchiudendo la tua funzione INDEX Distingui formule nella funzione SE.NON.DISP:

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

Quanto sopra rappresenta tutto il contenuto rilevante relativo alle funzioni INDEX e CONFRONTAin Excel. Speriamo che questo 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.