La combinazione di formule INDICE e CONFRONTA in Excel

In questo articolo esploriamo una tecnica avanzata per trovare dati correlati presenti in altri fogli di lavoro utilizzando la combinazione di due formule classiche, disponibili in tutte le versioni di Excel: INDICE e CONFRONTA.

Se hai a disposizione la versione 365 di Excel, quanto descritto in questo articolo può essere sviluppato attraverso una delle funzioni moderne: il CERCA.X.

Questa combinazione ci permette di creare un "CERCA.VERT DINAMICO" (vedi articolo precedente), che funziona indipendentemente dall'ordine delle colonne: il valore da cercare non deve essere necessariamente nella prima colonna della matrice e se cambi ordine alle colonne di origine, il risultato rimane invariato.

Andiamo quindi a ripetere lo stesso esempio pratico visto nell'articolo relativo al CERCA.VERT utilizzando anche lo stesso file di partenza.

Vedremo come ottenere gli stessi risultati in modo dinamico con la combinazione delle formule INDICE e CONFRONTA in Excel.

Se preferisci la versione video di questo tutorial, qui sotto ti lascio il video del mio canale YouTube.

La formula CONFRONTA

Iniziamo dalla formula CONFRONTA: questa funzione restituisce la posizione relativa di un valore in un intervallo dati in cui cercarlo. Ecco come funziona:

=CONFRONTA(valore da cercare, intervallo in cui cercare, tipo di corrispondenza)

Ad esempio, se nel foglio Tracciato Marcatempo volessimo trovare la posizione della matricola di un dipendente nell'elenco dei dipendenti, useremo questa formula da inserire nella cella F2:

=CONFRONTA(D2;Dipendenti!A:A;0)

Trascinando la formula il risultato diventa il seguente:

Il terzo parametro della formula (tipo di corrispondenza) è da mettere sempre a 0 per gli scopi di questo tutorial e anche per la maggior parte degli utilizzi comuni in cui si ricerca la corrispondenza precisa di un valore univoco.

Il trucco davvero interessante è usare la formula CONFRONTA anche per i nomi di colonna. Per esempio, se nella cella F2 cambiamo la formula e inseriamo la seguente, andiamo a ricercare il nome di colonna nella riga di intestazione del foglio Dipendenti:

=CONFRONTA(F1;Dipendenti!$1:$1;0)

Otteniamo il seguente risultato:

A questo punto, abbiamo a disposizione due formule che ci restituiscono le coordinate di riga e colonna di un determinato attributo (nome colonna) di un certo dipendente (matricola) in riferimento al foglio Dipendenti:

• =CONFRONTA(D2;Dipendenti!A:A;0) -> numero di riga
• =CONFRONTA(F1;Dipendenti!$1:$1;0) -> numero di colonna

La Combo Micidiale: INDICE e CONFRONTA

La vera magia avviene quando combiniamo le formula CONFRONTA che abbiamo appena costruito con la formula INDICE.

La formula INDICE restituisce un valore da una matrice specificata, utilizzando un numero di riga e un numero di colonna.

=INDICE(matrice, num di riga, num di colonna)

Capisci vero dove stiamo andando a parare?

È un po' come la battaglia navale. Diciamo ad Excel: "Hey, restituisci quello che trovi nel foglio Dipendenti alla riga X e colonna Y".

Nella cella F2 andiamo quindi ad inserire la seguente formula:

=INDICE(Dipendenti!$1:$1048576;CONFRONTA($D2;Dipendenti!$A:$A;0);CONFRONTA(F$1;Dipendenti!$1:$1;0))

Trascinala per tutte le righe e colonne che ti interessano e otterai il seguente risultato:

Utilizzare i riferimenti strutturati delle tabelle

Per rendere questa tecnica ancora più potente, possiamo utilizzare le tabelle di Excel e in particolare, nelle formule, i riferimenti strutturati.

Dopo aver trasformato gli intervalli Dipendenti e Marcatempo in due tabelle (con nome rispettivamente Dipendenti e Marcatempo), la nostra combinazione magica di formula diventa:

=INDICE(Dipendenti;CONFRONTA($D2;Dipendenti[[MATRICOLA]:[MATRICOLA]];0);CONFRONTA(F$1;Dipendenti[#Intestazioni];0))

Conclusioni

La combinazione di formule INDICE e CONFRONTA è uno strumento molto potente per estrarre dati da fogli e tabelle in Excel e un'ottima alternativa, più flessibile e dinamica, al nostro amato CERCA.VERT.

Utilizzando i riferimenti strutturati delle tabelle, rendiamo poi le formule ancora più flessibili e facili da gestire.

Buon lavoro con Excel e le sue potenti funzionalità!

Ci si vede nel gruppo Facebook.

#ExcelHaSempreRagione