=CERCA.X(1;(A2:A7=E2)*(B2:B7=F2);C2:C7) restituisce il prezzo della riga in cui il prodotto è E2 e il formato è F2. Ogni confronto controlla tutte le righe, moltiplicarli dà 1 solo dove entrambi sono veri, e CERCA.X cerca quell'1. Richiede Excel 2021 o Microsoft 365; la versione con INDICE e CONFRONTA più sotto funziona in ogni versione. La tabella mostra la formula in inglese, con XLOOKUP al posto di CERCA.X: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). Puoi scrivere le formule anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=CERCA.X(1;(A2:A7=E2)*(B2:B7=F2);C2:C7)Tea e Large si incontrano nella riga 5, quindi G2 restituisce $3.00. Scegli Juice e Small: di nuovo $3.00, da un'altra riga. Aggiungi un quarto argomento per il caso in cui nessuna riga corrisponda a entrambi: =CERCA.X(1;(A2:A7=E2)*(B2:B7=F2);C2:C7;"No such item").
Come funzionano le condizioni moltiplicate
A2:A7=E2 confronta ogni prodotto con E2 e restituisce sei valori VERO o FALSO. Moltiplicare due elenchi così trasforma VERO in 1 e FALSO in 0, e una riga vale 1 solo se vale 1 in entrambi. La colonna D mostra quell'elenco, espanso da una sola formula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
=(A2:A7=F2)*(B2:B7=G2)Solo D5 vale 1. Cambia F2 o G2 e l'1 si sposta. Ogni criterio in più è un altro *(intervallo=valore), e le condizioni non devono essere per forza uguaglianze: *(C2:C7<3) aggiunge "prezzo sotto 3". Ogni intervallo deve coprire le stesse righe (A2:A7, B2:B7, C2:C7): se l'intervallo da restituire ha una dimensione diversa dalle condizioni, CERCA.X restituisce #VALORE! (in inglese #VALUE!).
INDICE e CONFRONTA con più criteri
Per Excel 2019 e versioni precedenti, CONFRONTA può cercare l'1 nella stessa matrice e INDICE restituisce il prezzo da quella posizione.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=INDICE(C2:C7;CONFRONTA(1;(A2:A7=E2)*(B2:B7=F2);0))Coffee e Large sono la posizione 2 della matrice, e INDICE restituisce $3.50. In Excel italiano la formula è =INDICE(C2:C7;CONFRONTA(1;(A2:A7=E2)*(B2:B7=F2);0)). In Excel 2019 e versioni precedenti questa è una formula di matrice: premi Ctrl+Maiusc+Invio (Cmd+Maiusc+Invio su Mac) invece di Invio, ed Excel la mostra tra parentesi graffe. Premere solo Invio lì di solito restituisce #N/D o #VALORE!. In Excel 365 basta Invio. La forma con un solo criterio è nella pagina INDICE e CONFRONTA.
Unire i criteri in una sola chiave
L'altro modo è trasformare due criteri in uno unendoli. CERCA.VERT ha bisogno dei valori uniti in una colonna di appoggio all'inizio della tabella (la pagina su CERCA.VERT mostra quella versione). CERCA.X può unire gli intervalli dentro la formula, quindi non serve nessuna colonna di appoggio.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=CERCA.X(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7)A2:A7&"|"&B2:B7 costruisce sei chiavi come Juice|Large, e CERCA.X trova Juice|Large tra di esse: $4.00. Metti un separatore tra le parti. Senza, "AB" e "C" si uniscono nello stesso "ABC" di "A" e "BC", e la ricerca può restituire la riga sbagliata.
Se il valore che vuoi è un numero e ogni combinazione compare una volta sola, SOMMA.PIÙ.SE (in inglese SUMIFS) dà la stessa risposta senza nessuna matrice: =SOMMA.PIÙ.SE(C2:C7;A2:A7;E2;B2:B7;F2). Restituisce 0 invece di un errore quando nulla corrisponde, e questo può nascondere un errore di battitura.
Restituire tutte le corrispondenze con FILTRO
CERCA.X e INDICE CONFRONTA restituiscono la prima riga che corrisponde. Quando corrispondono più righe e le vuoi tutte, usa FILTRO (in inglese FILTER) con le stesse condizioni.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
=FILTRO(C2:D8;(A2:A8="North")*(B2:B8="Phone"))Tre righe sono North e Phone, quindi F2 espande i loro trimestri e le vendite in F2:G4. In Excel italiano la formula è =FILTRO(C2:D8;(A2:A8="North")*(B2:B8="Phone")). Cambia A3 in South e l'elenco si riduce a due righe. Se nessuna riga corrisponde, FILTRO restituisce #CALC!; un terzo argomento come "None" mostra invece un testo. Altre opzioni sono nella pagina su FILTRO.
Esercizio: tre criteri
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Tocca a te: Restituisci in G4 le vendite per la regione in G1, il prodotto in G2 e il trimestre in G3.
Domande frequenti
Come si usa CERCA.X con più criteri?
Moltiplica un confronto per ogni criterio e cerca 1: =CERCA.X(1;(A2:A7=E2)*(B2:B7=F2);C2:C7). Ogni confronto dà VERO o FALSO per ogni riga, il prodotto vale 1 solo dove sono tutti VERI, e CERCA.X restituisce la prima riga così.
Come si fa INDICE e CONFRONTA con due criteri?
Usa le stesse condizioni moltiplicate dentro CONFRONTA: =INDICE(C2:C7;CONFRONTA(1;(A2:A7=E2)*(B2:B7=F2);0)). In Excel 2019 e versioni precedenti, confermala con Ctrl+Maiusc+Invio (Cmd+Maiusc+Invio su Mac).
CERCA.VERT può usare due criteri?
Non direttamente. Aggiungi all'inizio della tabella una colonna di appoggio che unisca i due valori, come =A2&"|"&B2, poi cerca il valore unito: =CERCA.VERT(E2&"|"&F2;tabella_appoggio;col;FALSO).
SOMMA.PIÙ.SE può sostituire una ricerca con due criteri?
Sì, quando il valore è un numero e ogni combinazione compare una volta sola: =SOMMA.PIÙ.SE(C2:C7;A2:A7;E2;B2:B7;F2). Restituisce 0 invece di #N/D quando nessuna riga corrisponde, e somma i valori se una combinazione compare due volte.
Come si cerca con criteri in O?
Somma le condizioni invece di moltiplicarle: (A2:A7="Tea")+(A2:A7="Juice") vale 1 o più dove una delle due è vera. Cerca un valore maggiore di 0, per esempio con =CERCA.X(VERO;((A2:A7="Tea")+(A2:A7="Juice"))>0;C2:C7).
