Ricerche bidirezionali con INDEX-MATCH-MATCH

Immagini una griglia di vendite mensili in cui le regioni sono disposte lungo il lato sinistro e i mesi lungo la riga superiore. Si desidera il numero corrispondente all'intersezione tra una regione e un mese scelti.

Una ricerca normale trova un valore eseguendo la ricerca in una sola direzione. Una ricerca a due vie cerca contemporaneamente in entrambe le direzioni: individua la riga e la colonna corrette, quindi restituisce il valore che si trova alla loro intersezione.

Lo strumento classico per farlo è INDEX combinata con due chiamate a MATCH, spesso indicata come INDEX-MATCH-MATCH.

INDEX restituisce un valore da un intervallo in base alla sua posizione. La forma completa è INDEX(array, row_num, column_num).

Fornendo un blocco di celle, un numero di riga e un numero di colonna, restituisce il valore che si trova in quella posizione. Ad esempio, in una griglia che inizia da B2, richiedendo la riga 3 e la colonna 2 si ottiene il valore situato 3 righe sotto e 2 colonne a destra all'interno di quel blocco.

L'idea fondamentale è che INDEX richiede posizioni, non etichette. Ed è proprio ciò che fornisce MATCH.

MATCH trova la posizione di un valore all'interno di una singola riga o colonna. La sua forma è MATCH(lookup_value, lookup_array, match_type).

Usare 0 come tipo di corrispondenza per una corrispondenza esatta. Il risultato è un numero che indica la posizione del valore, contando da 1.

Se "East" è il secondo elemento dell'intervallo A2:A5, MATCH restituisce 2. Quel 2 può diventare il numero di riga per INDEX.

Per una ricerca a due vie si eseguono due MATCH:

• Una MATCH individua la riga in cui si trova la regione.
• Una MATCH individua la colonna in cui si trova il mese.

Quindi si passano entrambi i numeri a INDEX. MATCH per la riga cerca in un intervallo verticale di etichette; MATCH per la colonna cerca in un intervallo orizzontale di intestazioni.

Il risultato è la singola cella in cui si intersecano quella riga e quella colonna.

Immagini questo layout. Le etichette delle regioni si trovano in A2:A5 (East, West, North, South). Le intestazioni dei mesi si trovano in B1:D1 (Jan, Feb, Mar). I valori effettivi delle vendite riempiono B2:D5.

Due celle di input controllano la ricerca: G1 contiene la regione desiderata e G2 contiene il mese desiderato.

L'obiettivo è una singola formula che legga G1 e G2 e restituisca il valore delle vendite corrispondente da B2:D5.

Per prima cosa, individuare la regione. MATCH cerca nell'elenco verticale delle etichette A2:A5 il valore inserito in G1.

Se G1 contiene "North" e North è la terza etichetta, questa MATCH restituisce 3.

Questo numero indica a INDEX quale riga del blocco di dati leggere. Si noti che la ricerca viene eseguita in A2:A5, contenente solo le etichette, non nei dati, in modo che la posizione 3 corrisponda alla terza riga dei dati.

Successivamente, individuare il mese. Questa MATCH cerca nella riga orizzontale delle intestazioni B1:D1 il valore presente in G2.

Se G2 contiene "Feb" e Feb è la seconda intestazione, MATCH restituisce 2.

Quel numero diventa la posizione della colonna per INDEX. Come per le righe, la ricerca viene eseguita solo nelle intestazioni B1:D1, così la posizione corrisponde alle colonne dei dati in B2:D5.

Ora racchiudere entrambe le chiamate a MATCH in INDEX. Il blocco di dati B2:D5 è l'array, MATCH per la riga fornisce il numero di riga e MATCH per la colonna fornisce il numero di colonna.

Quando G1 è "North" e G2 è "Feb", MATCH per la riga restituisce 3 e MATCH per la colonna restituisce 2, quindi INDEX restituisce il valore alla riga 3 e alla colonna 2 di B2:D5.

Questa singola formula costituisce la ricerca a due vie completa.

Supponiamo che B2:D5 contenga quanto segue: la riga di North è Jan 50, Feb 80, Mar 65.

• MATCH("North", A2:A5, 0) restituisce 3.
• MATCH("Feb", B1:D1, 0) restituisce 2.
• INDEX(B2:D5, 3, 2) legge la riga 3 e la colonna 2, restituendo 80.

Modificando G1 in "East" o G2 in "Mar", l'intera formula viene ricalcolata immediatamente. Questo è il vantaggio di usare due ricerche MATCH per determinare INDEX.

VLOOKUP cerca solo nella prima colonna e restituisce un valore a un numero fisso di colonne a destra. Per passare da un mese all'altro, dovrebbe inserire manualmente l'indice della colonna oppure calcolarlo.

INDEX-MATCH-MATCH consente di scegliere dinamicamente sia la riga sia la colonna in base all'etichetta. Può riordinare le colonne o inserire nuovi mesi: la formula continuerà a funzionare perché cerca il testo dell'intestazione, non un numero fisso di colonne.

L'errore più comune consiste nell'usare intervalli non corrispondenti. L'intervallo di MATCH per la riga deve avere la stessa altezza del blocco di dati di INDEX, mentre l'intervallo di MATCH per la colonna deve avere la stessa larghezza.

Qui A2:A5 è alto 4 righe e anche B2:D5 è alto 4 righe, quindi un risultato di MATCH pari a 3 indica davvero la terza riga di dati. Se cerca accidentalmente in A1:A5, che include un'intestazione, le posizioni si spostano di una riga e si ottiene la cella sbagliata.

Verifichi la Sua comprensione dello schema di ricerca a due vie.

Ha imparato lo schema di ricerca a due vie:

• INDEX restituisce un valore in una determinata posizione di riga e colonna all'interno di un blocco.
• Un MATCH trova la riga cercando tra le etichette verticali.
• Un secondo MATCH trova la colonna cercando tra le intestazioni orizzontali.

La formula combinata =INDEX(data, MATCH(row), MATCH(col)) legge due input e restituisce il valore alla loro intersezione. Mantenga gli intervalli di MATCH delle stesse dimensioni del blocco di dati per evitare disallineamenti.