Come trovare un valore in Excel utilizzando due o più criteri?

Immagina di lavorare con una tabella delle vendite di frutta come quella mostrata di seguito. Potresti aver bisogno di cercare l’importo delle vendite in base a più informazioni, ad esempio il tipo di frutto, la data di vendita e il peso. Grazie alle formule matriciali in Excel, puoi recuperare questi valori in modo rapido ed efficiente, anche quando devi soddisfare contemporaneamente diverse condizioni. Questo approccio è estremamente flessibile e si adatta perfettamente a set di dati in cui occorre individuare un singolo valore corrispondente a più criteri specifici.

Formula matriciale 1: Trova un valore con due o più criteri in Excel

La struttura generale di questa formula matriciale è la seguente:

{=INDICE(array;CONFRONTA(1;(criterio1=matrice_cerca1)*(criterio2=matrice_cerca2)…*(criterio_n=matrice_cerca_n);0))}

Ad esempio, se si desidera trovare l’importo delle vendite di mangovenduto il 9/3/2019, inserire la seguente formula in una cella vuota e premere Ctrl+Shift+Invioper confermarla come formula matriciale:

=INDICE(F3:F22;CONFRONTA(1;(J3=B3:B22)*(J4=C3:C22);0))

Nota: In questo esempio,

• F3:F22 è la colonna «Amount» (Importo) da cui si desidera recuperare il valore.
• B3:B22 è la colonna «Date» (Data); C3:C22 è la colonna «Fruit» (Frutto).
• J3 è la data scelta come primo criterio; J4 è il nome del frutto utilizzato come secondo criterio.

Aggiungere ulteriori criteri è semplice. Ad esempio, per cercare l’importo delle vendite di mango il 9/3/2019 con un peso di 211, aggiungi la terza condizione sia nella funzione CONFRONTA che nelle matrici di ricerca, come mostrato:

=INDICE(F3:F22;CONFRONTA(1;(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22);0))

Dopo aver inserito la formula, premere nuovamente Ctrl+Shift+Invio per confermare. Il risultato sarà l’importo delle vendite che soddisfa tutti i criteri specificati.

Formula matriciale 2: Trova un valore con due o più criteri in Excel mediante concatenazione

In alternativa, puoi usare la concatenazione nella formula per un approccio diverso, particolarmente utile se desideri una struttura più compatta. La formula di base è:

=INDICE(array;CONFRONTA(criterio1&criterio2…&criterioN matrice_cerca1&matrice_cerca2…&matrice_cercaN;0);0)

Ad esempio, per recuperare l’importo delle vendite di un frutto con peso di 242il 9/1/2019:

=INDICE(F3:F22;CONFRONTA(J3&J4B3:B22&C3:C22;0);0)

Nota: In questo caso,

• F3:F22 è la colonna Amount (Importo); B3:B22 è la Data; E3:E22 è la colonna Weight (Peso).
• J3 è la data; J5 è il valore di peso per i criteri specificati.

Per più di due criteri, estendere sia i criteri che le matrici di ricerca nello stesso ordine:

=INDICE(F3:F22;CONFRONTA(J3&J4&J5B3:B22&C3:C22&E3:E22;0);0)

Come in precedenza, premere Ctrl + Shift + Invio per ottenere il risultato corretto.

Entrambi i metodi basati su formule matriciali individuano il primo valore che soddisfa tutti i criteri. Tuttavia, richiedono che gli intervalli di celle abbiano la stessa dimensione e non restituiscono più corrispondenze: viene recuperata soltanto la prima. Se non ci sono corrispondenze, la formula restituisce un errore #N/D. Per visualizzare tutte le corrispondenze, ti consigliamo di provare la funzione FILTER (vedi dettagli qui sotto).

Alcuni suggerimenti pratici e note:

• Se utilizzi versioni più recenti di Excel (Microsoft 365, Excel 2021), puoi sfruttare le formule matriciali dinamiche e la funzione FILTER per semplificare questa procedura.
• Per evitare errori #N/D quando non ci sono corrispondenze, racchiudi la formula nella funzione IFERROR, ad esempio:=IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),«Not found»).
• Verifica attentamente che le celle dei criteri di ricerca non contengano spazi superflui o tipi di dati non corrispondenti.
• Se si riceve un errore dopo aver premuto semplicemente Invio, assicurarsi di confermare la formula come formula matriciale utilizzando Ctrl+Shift+Invio (per Excel 2019 e versioni precedenti).

Oltre alle formule, Excel offre la potente funzionalità Filtro avanzato, che ti permette di filtrare ed estrarre tutte le righe corrispondenti a due o più criteri, mostrando i risultati in un’altra posizione del foglio. È la soluzione ideale quando devi visualizzare tutti i record che soddisfano determinate condizioni, anziché recuperare un singolo valore. Ecco come usarla:

1. Passa alla scheda Dati e seleziona Avanzate nel gruppo Ordina e filtra per aprire la finestra di dialogo Filtro avanzato.

2. Nella finestra di dialogo Filtro avanzato, completa le seguenti impostazioni:
(1) Seleziona Copia in un’altra posizione nella sezione Azione.
(2) Per Intervallo elenco, evidenzia l’intervallo contenente i dati da filtrare ()A1:E21 in questo esempio).
(3) Per Intervallo criteri, seleziona l’intervallo contenente le tue condizioni di filtro ()H1:J2 in questo caso). Assicurati che le intestazioni nell’intervallo criteri corrispondano esattamente a quelle nella tabella dati.
(4) In Copia in, seleziona la prima cella in cui desideri incollare i risultati filtrati ()H9 in questo caso).

3. Fare clic su OK per eseguire il filtro.

Le righe che soddisfano tutte le condizioni definite nell’intervallo criteri verranno copiate nell’area di destinazione specificata. Questa funzionalità è ideale per analizzare o generare report su tutti i record che rispondono contemporaneamente a più condizioni di filtro.

Alcuni suggerimenti e precauzioni:

• Assicurati che le intestazioni dell’intervallo criteri siano identiche a quelle della tabella dati principale; in caso contrario, il filtro potrebbe non funzionare correttamente.
• Il filtro avanzato supporta sia le condizioni AND che OR: inserendo i criteri sulla stessa riga si applica la logica AND (tutti devono essere veri), mentre utilizzando righe separate si applica la logica OR (ne basta uno vero).
• Il filtro avanzato non si aggiorna automaticamente al cambiamento dei dati; è necessario riapplicarlo dopo aver aggiornato i dati o i criteri.
• Attenzione: le celle vuote nell’intervallo criteri possono essere interpretate come «corrispondenza con qualsiasi valore» per quel campo.

Rispetto alle soluzioni basate su formule, il Filtro avanzato è particolarmente adatto per estrarre interi set di dati corrispondenti ai criteri, anziché un’unica cella. Tuttavia, non è indicato per ricerche in tempo reale o frequentemente aggiornate, poiché è necessario rieseguire il filtro dopo ogni modifica dei dati.

Se utilizzi una versione recente di Excel (Microsoft 365 o Excel 2021 e successive), la funzione FILTER offre un metodo dinamico e intuitivo per estrarre tutti i valori che soddisfano più criteri. Questa soluzione è altamente raccomandata se desideri che i risultati si aggiornino automaticamente al variare dei dati o dei criteri, senza dover ricorrere a complesse formule matriciali.

1. In una cella vuota, inserisci una formula simile alla seguente:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

In questa formula:

• F3:F22 è la colonna Amount (Importo).
• B3:B22 è la colonna delle date (Data), confrontata con la data presente in J3.
• C3:C22 è la colonna Fruit (Frutto), confrontata con il frutto presente in J4.

Per aggiungere una terza condizione, ad esempio confrontare la colonna Peso (E3:E22)con un valore presente in J5, estendere la formula nel modo seguente:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Dopo aver premuto Invio, Excel visualizzerà tutti gli importi che soddisfano i criteri specificati. Se non viene trovata alcuna corrispondenza, la formula restituirà l’errore #CALC!, che puoi gestire facilmente con la funzione SE.ERRORE:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Vantaggi:

• I risultati si aggiornano automaticamente non appena cambiano i dati o i criteri.
• Le formule sono più semplici da gestire ed espandere rispetto alle vecchie formule matriciali.
• Restituisce tutte le corrispondenze, non solo la prima trovata.

Limitazione: Disponibile solo in Microsoft 365, Excel 2021 o versioni successive. Non supportato nelle versioni precedenti.