Excel: Le Funzioni CONFRONTA e INDICE

Applicare le Funzioni INDICE e CONFRONTA di Excel (Ottima alternativa alla Funzione CERCA.VERT)

Le Funzioni CONFRONTA  e  INDICE appartengono entrambe alla classe delle Funzioni di Ricerca e Riferimento, a cui appartiene anche la più famosa  Funzione Cerca Verticale.
Cerca Verticale non è certo l'unica Funzione di Ricerca di Excel e i limiti che la caratterizzano possono diventare un problema in determinate impostazioni di ricerca dati.
L'applicazione combinata delle Funzioni CONFRONTA e INDICE diventa una validissima alternativa, e permette di impostare delle ricerche anche su matrici che non prevedono di avere nella prima colonna il valore da ricercare.
In questo tutorial viene presentata la sintassi delle funzioni INDICE e CONFRONTA e vengono proposti degli esempi di ricerca combinata delle due Funzioni.

Contenuti del tutorial Indice e Confronta

[1] - Elementi introduttivi sulle Funzioni Confronta e Indice
[2] - La Funzione CONFRONTA di Excel e Sintassi della Funzione
[3] - Esempio di applicazione della Funzione CONFRONTA
[4] - Codici di errore della Funzione CONFRONTA
[5] - La Funzione INDICE di Excel e Sintassi della Funzione
[6] - Esempi di applicazione combinata di INDICE e CONFRONTA per cercare valori a sinistra (e non solo)
[7] - Cinque vantaggi nell'applicare le Funzioni INDICE e CONFRONTA al posto del CERCA.VERT
[8] - Download esempio ricerca dati a sinistra con le Funzioni INDICE e CONFRONTA

Elementi introduttivi sulle Funzioni Confronta e Indice

La Funzione CONFRONTA permette di restituire la posizione di un determinato valore nell'ambito di un definito intervallo (riga o colonna).
Per un approfondimento della Funzione CONFRONTA e per prendere visione della nuova Funzione CONFRONTA.X si rimanda all'articolo: Applicare le funzioni CONFRONTA e CONFRONTA.X

La Funzione INDICE restituisce il valore di una cella posizionata nell'intersezione di una determinata riga e colonna inclusa in uno specifico intervallo.
L'utilizzo delle due Funzioni combinate assieme offre una valida è potente alternativa alla più nota Funzione Cerca Verticale riuscendo a sopperire ad alcuni limiti che questa presenta.

Cerca verticale è sicuramente una delle Funzioni più importanti di Excel e consente di cercare un valore contenuto nella prima colonna di una tabella matrice e restituire un valore nella stessa riga di una specificata colonna a destra. Ma se in una matrice il dato da cercare si trovasse interposto fra altre colonne e si dovesse cercare un valore a sinistra della colonna di ricerca? In questo caso esiste un metodo alternativo  per poter effettuare le ricerche dati e consiste nell'utilizzo combinato delle Funzioni CONFRONTA e INDICE.

:: ATTENZIONE. con la versione Microsoft 365 è stata creata una nuova Funzione CERCA.X  che riassume e integra le funzionalità combinate delle Funzioni INDICE E CONFRONTA. Per info si rimanda al tutorial: La Funzione CERCA.X destinata a sostituire la Funzione Cerca Verticale

La Funzione CONFRONTA di Excel e Sintassi della Funzione

La Funzione CONFRONTA permette di cercare un determinato elemento in un intervallo di celle e restituirne la posizione.
La sintassi è espressa da
=CONFRONTA(Valore;Matrice;[Corrisp])
Nella quale
Valore rappresenta il valore che si vuole cercare, può essere indicato con un riferimento di cella, una data, un numero, un testo.
Matrice indica l'area del foglio all’interno della quale si vuole effettuare la ricerca (una riga o una colonna)
Corrispondenza questo argomento accetta tre diversi valori. Va indicato 0 per trovare una corrispondenza esatta oppure 1 o -1 per trovare una corrispondenza approssimativa rispettivamente più piccola o più grande del valore cercato.
: Bisogna porre attenzione alla corretta impostazione dell'argomento Corrispondenza!
Omettere l'argomento o inserire il valore 1, viene restituita la posizione del valore più grande (che più si avvicina)  minore o uguale al valore ricercato. Per questo tipo di ricerca è fondamentale che i valori nella matrice di ricerca siano ordinati in ordine crescente.
Inserendo il valore 0 (zero) viene restituita la posizione del primo valore che è esattamente uguale al valore ricercato in questo caso non è necessario che i valori nella matrice siano ordinati in modo crescente o decrescente.
Inserendo il valore -1 viene restituita la posizione del valore più piccolo (che più si avvicina) che è maggiore o uguale al valore ricercato. Per questo tipo di ricerca è fondamentale che i valori nella matrice di ricerca siano ordinati in ordine decrescente.

Esempio di applicazione della Funzione CONFRONTA

Nell'esempio raffigurato nell'immagine sottostante, nella cella H6 è stato definito un codice da cercare.
La Funzione è stata imposta nella cella H7 con la seguente sintassi =CONFRONTA(H9;C4:C16;0)
H9 rappresenta il valore da ricercare, C4:C16 l'area del foglio nella quale cercare il valore e infine 0 (zero) definisce che la ricerca deve essere esatta e non per approssimazione.
Il risultato restituito dalla Funzione è 5 in quanto il codice a117 si trova nella quinta posizione dell'elenco delle celle C4:C16

:: OSSERVAZIONE. La posizione 5 che viene restituita dalla formula CONFRONTA, potrà essere utilizzata per ricercare il valore del prezzo presente nella stessa posizione di riga ma contenuto nella colonna a sinistra. Per effettuare questa ricerca è necessario nidificare CONFRONTA all'interno della Funzione INDICE.

Codici di errore della Funzione CONFRONTA

Se il dato ricercato con la Funzione CONFRONTA non fosse disponibile viene restituisce un messaggio di errore.
Nell'esempio sotto raffigurato il valore restituito è #N/D (dato non disponibile) poichè il codice non è presente all'interno della tabella matrice. Questo messaggio di errore è stato restituito perchè nell'argomento Corrisp. della Funzione è stato inserito lo 0 (zero) che indica che la Corrispondenza deve essere esatta.
Nel caso fosse stato inserito l'argomento 1 oppure -1 e qualora il dato ricercato non fosse disponibile, verrebbe restituita la posizione del numero più vicino a quello cercato rispettivamente minore o maggiore.

:: Per la corretta gestione e correzione dei codici di errore che le Funzioni di Ricerca restituiscono si rimanda al tutorial: Gestire il messaggio di errore #N/D

La Funzione INDICE di Excel e sintassi della Funzione

La Funzione INDICE restituisce il valore di una cella all'intersezione di una determinata riga e colonna incluse in un certo intervallo.
Questa funzione trova una importante applicazione se abbinata alla Funzione CONFRONTA.
La Funzione CONFRONTA precedentemente illustrata fornisce quale risultato il numero della riga nella quale si trova l'oggetto della ricerca (riga 5). Tramite la Funzione INDICE sarà possibile indicare che riga 5 deve essere incrociata con la colonna 1.
Sintassi:
=INDICE(Matrice;Riga;[Col])
nella quale:
Matrice rappresenta l'area entro la quale ricercare il valore;
Riga indica la Riga della Matrice da cui restituire il valore
Col indica la colonna della Matrice da cui restituire il valore.
Se entrambi gli argomenti Riga e Col vengono individuati, la Funzione INDICE permette di restituire il valore contenuto nella cella dell'intersezione di Riga e Colonna.
Esempio illustrato nella sottostante immagine. La funzione =INDICE(B4:C16;5;1) restituisce il valore 187, ovvero il valore presente nella quinta riga e prima colonna della matrice B4:C16

 

Ricercare valori combinando le Funzioni INDICE e CONFRONTA

Come anticipato, è possibile nidificare la Funzione CONFRONTA all'interno di INDICE, al fine di ottenere una Formula di ricerca che permette di restituire dei valori sia a sinistra sia a destra della colonna nella quale ricercare il valore.
Di seguito vengono proposte due diverse impostazioni della sintassi delle Funzioni INDICE e CONFRONTA da applicare in alternativa alle ricerche con la Funzione CERCA.VERT

Esempi di applicazione di INDICE e CONFRONTA per cercare valori a sinistra

Nella tabella illustrata nella sottostante immagine si vuole ricercare la categoria del prodotto (presente nella prima colonna della tabella) attraverso la ricerca di un Codice che è contenuto nella seconda colonna.  
Nella cella F2 della tabella è stato inserito il valore che si vuole ricercare nella matrice $A$2:$D$14

Nel caso dell'esempio la Funzione CONFRONTA, restituisce l'informazione che la posizione del codice a10 è nella riga 8.
Questa informazione può essere utilizzata per ricercare il valore del prezzo presente nella stessa posizione di riga ma contenuto nella colonna a sinistra.

Per effettuare questa ricerca è necessario nidificare CONFRONTA all'interno della Funzione INDICE.
Nella funzione INDICE  verrà pertanto indicato in primo luogo la Matrice di ricerca A2:D14, al posto dell'operatore Riga verrà inserita la Funzione CONFRONTA (il risultato restituito dalla Funzione Confronta è appunto la posizione di riga numero 8) infine come argomento colonna verrà inserito il valore 1 (poichè è il valore presente nella prima colonna che si vuole venga restituito).

=INDICE(A2:D14;CONFRONTA(F2;B2:B14;0);1)

La colonna 1, nell'intersezione con la riga 8, restituisce il risultato delle ricerca "Pantaloni Snowboard"

 

Semplificazione della Funzione INDICE  

Nella Funzione INDICE il terzo argomento è facoltativo.
Per evitare di impostare questo terzo argomento. Il primo argomento Matrice (ovvero l'area) anzichè indicare l'intera matrice A2:D14 è possibile indicare la sola prima colonna A2:14, in questo modo non è necessario indicare il terzo argomento che nell'impostazione precedente era 1 ovvero la prima colonna. (Vedi esempio nella successiva immagine).
Questa seconda impostazione ha l'ulteriori vantaggio di rendere più veloce la Formula dovendo caricare una sola colonna di ricerca (matrice monodimensionale) e non l'intera matrice della tabella.

 

Cinque vantaggi nell'applicare le Funzioni INDICE e CONFRONTA al posto del CERCA.VERT

Ovviamente la combinazione delle Funzioni CONFRONTA e INDICE può ricercare valori presenti anche a destra.
Basta applicare il primo esempio proposto e anziché indicare come terzo argomento della Funzione INDICE la colonna 1 impostare un riferimento ad una successiva colonna della matrice (2, 3, 4 etc)
Molti esperti di Excel tendono a preferire e consigliare (anche se più complesso) l'utilizzo combinato di queste due Funzioni al posto della Funzione CERCA.VERT le motivazioni sono rappresentate nel successivo elenco di vantaggi.

I sintesi sono ben cinque i vantaggi dell'applicazione combinata delle Funzioni INDICE e CONFRONTA:
::  Possibilità di ricercare sia a desta sia a sinistra (argomento discusso nel tutorial)
:: Nessun limite nella dimensione del valore da ricercare (la Funzione CERCA.VERT ha un limite di lunghezza della stringa di ricerca di 255 caratteri)
:: Ricerche per approssimazione: per quanto riguarda le ricerche per approssimazione,  l'argomento corrispondenza della Funzione CONFRONTA si rileva più flessibile della Funzione CERCA.VERT per la quale è possibile impostare solo il valore VERO (1) oppure FALSO (0).

Per quanto concerne l'applicazione della seconda impostazione delle Funzioni INDICE e CONFRONTA (vedi esempio di cui sopra), ovvero quella in cui si individua come matrice della Funzione INDICE la colonna che si vuole restituita e non si definisce il terzo argomento, vi sono altri due interessanti vantaggi nel preferirla alla ricerca CERCA.VERT
:: Maggiore velocità di elaborazione nella ricerca dati. Questa opzione è ovviamente apprezzabile nella ricerche su grandi intervalli di dati. L'abbinamento delle Funzioni INDICE e CONFRONTA risulta più veloce poichè nell'impostazione della ricerca si individua una matrice monodimensionale (la sola colonna) e non l'intera matrice dati.
:: Inserimento od eliminazioni di colonne. Con il CERCA.VERT c'è il rischio che con l'eliminazione o inserimento di nuove colonne non vi sia più corrispondenza con l'indice dal quale si vuole la restituzione del risultato. Viceversa con CONFRONTA e INDICE la matrice monodimensionale di riferimento è la stessa dalla quale si vuole la restituzione dei dati e di conseguenza non c'è la necessità di modificare la sintassi della Funzione.

Download esempio ricerca dati a sinistra con le Funzioni INDICE e CONFRONTA  

Scarica il file contenente l'esempio di ricerca INDICE-CONFRONTA presentato nel tutorial

Indice Generale Argomenti: EXCEL

[Valter Borsato: ottobre - 2014 | Ultimo aggiornamento 06/02/2021]