Ricerche con più criteri usando INDEX-MATCH

A volte una singola colonna non identifica una riga in modo univoco. Potrebbe aver bisogno del prezzo di un prodotto in una taglia specifica oppure dello stipendio di un dipendente in un determinato reparto.

In questi casi serve una ricerca con più criteri: è necessario confrontare contemporaneamente due o più colonne per individuare esattamente una riga.

INDEX-MATCH gestisce questa situazione in modo elegante combinando le condizioni in un unico test di corrispondenza, senza richiedere colonne di supporto aggiuntive.

Il modello mentale più semplice consiste nell'unire le colonne che contengono le chiavi. Aggiunga una colonna di supporto che concateni prodotto e taglia, quindi esegua una normale ricerca al suo interno.

Ad esempio, una cella di supporto potrebbe contenere =A2&"|"&B2, producendo "Shirt|Large". Potrà quindi usare MATCH per cercare "Shirt|Large" nella colonna combinata.

Questo metodo funziona, ma rende il foglio più disordinato. Nelle sezioni successive vedrà come fare a meno della colonna di supporto.

Il principio fondamentale consiste nel moltiplicare i due test delle condizioni all'interno di MATCH.

(A2:A10=G1) restituisce un array di TRUE/FALSE per il primo criterio. (B2:B10=G2) fa lo stesso per il secondo. Moltiplicandoli, (A2:A10=G1)*(B2:B10=G2) si ottiene 1 solo dove entrambe le condizioni sono TRUE e 0 in tutti gli altri casi.

MATCH cerca quindi il valore 1 per trovare la riga che soddisfa entrambe le condizioni.

Nei fogli di calcolo TRUE si comporta come 1 e FALSE come 0. Moltiplicare due di questi valori equivale a un AND logico:

• 1 per 1 = 1 (entrambe le condizioni sono soddisfatte)
• 1 per 0 = 0
• 0 per 1 = 0
• 0 per 0 = 0

Di conseguenza, solo le righe in cui entrambi i criteri sono soddisfatti producono 1. Tutte le altre diventano 0. Quell'unico 1 identifica la riga desiderata.

Ora racchiuda l'array ottenuto dalla moltiplicazione in MATCH e cerchi il valore esatto 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) restituisce la posizione della prima riga in cui entrambe le condizioni sono TRUE.

Se la combinazione corrispondente si trova nella quarta riga di dati, MATCH restituisce 4. È questa la posizione di cui INDEX ha bisogno per recuperare il risultato.

Passi il risultato di MATCH a INDEX, applicato alla colonna desiderata, ad esempio ai prezzi in C2:C10.

La formula completa significa: nell'intervallo C2:C10, restituisci il valore della riga in cui il prodotto è uguale a G1 e la taglia è uguale a G2.

Questa è una vera ricerca con più criteri, senza colonne di supporto e senza dover riorganizzare i dati.

Questa formula valuta array di condizioni. In Excel moderno e Google Sheets è sufficiente premere Invio perché funzioni.

Nelle versioni precedenti di Excel (prima degli array dinamici), deve confermarla come formula matriciale con Ctrl+Maiusc+Invio, aggiungendo le parentesi graffe. Se in una versione precedente di Excel il risultato è errato o viene visualizzato un errore, spesso manca proprio questo passaggio di conferma.

Servono tre criteri? Moltiplichi semplicemente anche un altro test. Supponiamo che voglia confrontare anche un colore nella colonna D con il valore inserito in G3.

Ogni fattore aggiuntivo (range=criterion) restringe ulteriormente il risultato. Solo le righe in cui tutte le condizioni sono TRUE mantengono un prodotto pari a 1; un qualsiasi valore FALSE trasforma l'intero prodotto in 0.

Questo schema può essere esteso a tutte le colonne necessarie.

Dati: A = prodotto, B = taglia, C = prezzo. Desidera il prezzo di una "Shirt" in taglia "Large".

• G1 = "Shirt", G2 = "Large".
• Gli array delle condizioni producono 1 solo nella riga Shirt+Large, ad esempio la riga 4.
• MATCH(1, ..., 0) restituisce 4.
• INDEX(C2:C10, 4) restituisce il prezzo di quella riga.

Modificando uno dei due input, la formula individua immediatamente la riga corretta.

Tenga presenti questi aspetti:

• Intervalli uguali: ogni intervallo delle condizioni e la colonna di INDEX devono avere la stessa altezza.
• Nessuna corrispondenza: se nessuna riga soddisfa tutti i criteri, MATCH restituisce #N/A. Racchiuda l'intera formula in IFERROR.
• Duplicati: se più righe corrispondono, MATCH restituisce solo la prima. Renda i criteri abbastanza specifici da ottenere una corrispondenza univoca.

Se più righe possono corrispondere e preferisce sommarne i valori invece di recuperarne uno solo, SUMPRODUCT è un'alternativa semplice a INDEX-MATCH inserito come formula matriciale.

Moltiplica gli array delle condizioni per la colonna dei valori e somma i risultati, così contribuiscono solo le righe che soddisfano entrambi i criteri. Non è necessario premere Ctrl+Maiusc+Invio, perché SUMPRODUCT gestisce nativamente gli array.

Utilizzi INDEX-MATCH per recuperare un singolo valore corrispondente; utilizzi SUMPRODUCT per aggregare tutti i valori corrispondenti.

Verifichi le Sue conoscenze sulle ricerche con più criteri.

Per le ricerche con più criteri usando INDEX-MATCH:

• Moltiplicate tra loro gli array delle condizioni: (A=G1)*(B=G2) restituisce 1 solo nelle righe in cui tutte le condizioni sono vere (un AND logico).
• MATCH(1, ..., 0) trova la posizione della riga.
• INDEX(returnCol, position) restituisce il valore.

Aggiungete altri fattori *(range=criterion) per inserire ulteriori condizioni, mantenete gli intervalli della stessa altezza, confermate con Ctrl+Maiusc+Invio nelle versioni precedenti di Excel e gestite gli errori con IFERROR.