Combinare INDEX e MATCH

Ora conosce le due componenti di una ricerca. MATCH individua dove si trova un valore, mentre INDEX restituisce il valore nella posizione indicata.

Combinandole si ottiene una ricerca completa: MATCH individua la riga, quindi INDEX recupera i dati da quella riga nella colonna desiderata.

Lo schema è semplice una volta compreso: inserire MATCH all'interno di INDEX, nel punto in cui normalmente si inserisce il numero di riga.

Ecco la struttura che utilizzerà più e più volte:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Legga l'espressione dall'interno verso l'esterno. MATCH viene eseguita per prima e restituisce un numero di posizione. Quel numero diventa quindi il row_num di INDEX, che restituisce il valore dall'intervallo dei risultati.

L'intervallo dei risultati e quello di ricerca hanno solitamente lo stesso numero di righe, quindi una posizione nell'uno corrisponde a quella nell'altro.

Si immagini una tabella in cui la colonna A contiene i nomi dei prodotti e la colonna C contiene i prezzi. Si desidera il prezzo di "Cherry".

Per prima cosa MATCH trova Cherry: =MATCH("Cherry", A2:A20, 0) restituisce, ad esempio, 3.

Poi INDEX utilizza quel 3: =INDEX(C2:C20, 3) restituisce il prezzo nella terza riga della colonna C.

Annidando le due funzioni, si ottiene il risultato in un solo passaggio: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

Per imparare va bene inserire direttamente "Cherry", ma nelle formule reali si fa invece riferimento a una cella. Inserisca il termine da cercare in E1 e faccia riferimento a quella cella.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Ora, qualunque prodotto venga digitato in E1, il relativo prezzo viene restituito immediatamente. Si digiti Banana per ottenere il prezzo di Banana; si digiti Date per aggiornare il risultato.

Una sola formula diventa uno strumento di ricerca riutilizzabile, controllato interamente dalla cella di input.

È possibile fornire a INDEX anche un numero di colonna, ricavato da un secondo MATCH. In questo modo si individua con precisione un valore all'intersezione di una riga e di una colonna.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

Il primo MATCH trova la riga in base alle etichette nella colonna A, mentre il secondo trova la colonna in base alle intestazioni della riga 1. INDEX restituisce la cella in cui si intersecano. Questo schema avanzato viene illustrato in dettaglio più avanti.

L'intervallo del risultato decide che cosa si ottiene. Cercando con la stessa chiave, può recuperare qualsiasi colonna desideri semplicemente cambiando l'intervallo di INDEX.

Per trovare l'indirizzo email di un cliente: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Per trovare invece la città dello stesso cliente: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

La parte MATCH rimane identica; cambia solo l'intervallo di INDEX, così da scegliere un risultato diverso.

È possibile fornire a INDEX anche un numero di colonna, ottenuto tramite una seconda MATCH. In questo modo si individua con precisione un valore all'intersezione di una riga e di una colonna.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

La prima MATCH individua la riga a partire dalle etichette nella colonna A, mentre la seconda individua la colonna a partire dalle intestazioni nella riga 1. INDEX restituisce la cella in cui si intersecano. Questo schema avanzato viene approfondito più avanti.

Affinché la posizione corrisponda, l'intervallo di ricerca e l'intervallo del risultato devono iniziare dalla stessa riga e avere la stessa altezza.

Se MATCH cerca in A2:A20 (19 righe), ma INDEX restituisce un valore da C2:C19 (18 righe), le posizioni si disallineano e si ottiene un risultato errato.

Una buona abitudine consiste nell'usare esattamente lo stesso intervallo di righe per entrambi, ad esempio A2:A20 e C2:C20. Anche i riferimenti a colonne intere, come A:A e C:C, rimangono automaticamente allineati.

Se MATCH non trova il valore cercato, restituisce #N/A e l'intera formula INDEX-MATCH mostra questo errore. La racchiuda in IFNA per ottenere un risultato alternativo più ordinato.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Ora, se manca un prodotto, viene mostrato il testo «Non trovato» invece di un errore preoccupante. Anche IFERROR funziona, ma IFNA gestisce solo il caso di mancata corrispondenza e lascia emergere gli altri errori.

Metta insieme tutti gli elementi. Ha una tabella dei dipendenti: gli ID nella colonna A, i nomi nella colonna B, i reparti nella colonna C e gli stipendi nella colonna D. Un utente inserisce un ID in G1.

Per restituire il reparto di quel dipendente: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

Per restituire invece il suo stipendio, sostituisca l'intervallo di INDEX con D2:D200. La logica di ricerca non cambia mai: cambia solo la colonna da cui leggere. Questo è lo strumento di uso quotidiano per le ricerche dinamiche.

Quando una formula sembra intimidatoria, la valuti come fa il foglio di calcolo, partendo dalla funzione più interna e procedendo verso l'esterno.

Per =INDEX(C2:C20, MATCH(E1, A2:A20, 0)): legga prima MATCH(E1, A2:A20, 0), immagini che restituisca un numero come 5, quindi lo sostituisca mentalmente per ottenere =INDEX(C2:C20, 5).

All'improvviso, la formula significa semplicemente «restituire il quinto prezzo». Questa abitudine rende facile eseguire il debug di qualsiasi ricerca nidificata.

Verifichi di aver capito come si combinano le due funzioni.

Ha combinato le due funzioni in una ricerca flessibile:

• Schema: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH trova la posizione della riga; INDEX restituisce il valore in quella posizione
• Le colonne di ricerca e del risultato sono indipendenti, quindi può cercare a sinistra con la stessa facilità che a destra
• Mantenga la stessa altezza per entrambi gli intervalli e usi IFNA per una gestione pulita degli errori

Ora vedrà esattamente perché questo approccio spesso supera VLOOKUP.