Capitolo 17 · 17.4
Cercare un valore in base a due condizioni contemporaneamente
CERCA.VERT e CERCA.X cercano in base a un solo valore (vedere CERCA.VERT restituisce #N/D: ecco come risolverlo e CERCA.X: ricerca verso sinistra e con un messaggio personalizzato). In pratica capita spesso di dover cercare in base a due elementi contemporaneamente: il prezzo di un bouquet in taglia L, la tariffa di un cliente in un determinato anno, la giacenza di un prodotto in un determinato colore. Esistono tre buoni metodi per farlo, che funzionano tutti con un semplice Invio. Un quarto metodo, molto diffuso su internet, in Euro-Office non funziona senza accorgimenti.
Come esempio prendiamo il listino prezzi di Bloemisterij Het Zonnetje. Lo stesso prodotto compare più volte, in taglie diverse:
| A | B | C | |
|---|---|---|---|
| 1 | Prodotto | Taglia | Prezzo |
| 2 | Bouquet | S | 17,50 |
| 3 | Bouquet | M | 24,50 |
| 4 | Bouquet | L | 34,50 |
| 5 | Composizione funebre | M | 65,00 |
| 6 | Composizione funebre | L | 89,00 |
| 7 | Bouquet da sposa | M | 79,50 |
Il prodotto cercato si trova in F2 (Bouquet), la taglia in G2 (L). Cerchiamo 34,50.
Metodo 1: CERCA.X con valori concatenati
=CERCA.X(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"non trovato")
Il carattere & unisce più testi. F2&"|"&G2 diventa "Bouquet|L". A2:A7&"|"&B2:B7 crea la stessa combinazione per ogni riga dell'elenco ("Bouquet|S", "Bouquet|M" e così via) e CERCA.X effettua la ricerca al suo interno. La barra verticale | inserita in mezzo evita che due combinazioni diverse producano per caso lo stesso testo. Se la combinazione non esiste, ad esempio una composizione funebre in taglia S, si ottiene "non trovato".

Se dopo aver digitato la formula guarda nella barra della formula, noterà una @ davanti ad A2:A7 e B2:B7. La inserisce automaticamente Euro-Office; il risultato è comunque corretto.
Metodo 2: FILTRO per tutte le corrispondenze
=FILTRO(C2:C7;(A2:A7=F2)*(B2:B7=G2);"nessuno")
FILTRO non restituisce solo la prima riga, ma tutte le righe che soddisfano entrambe le condizioni. Se una combinazione compare due volte, vedrà entrambi i prezzi uno sotto l'altro. Si assicuri quindi che sotto la formula ci sia spazio libero (vedere Elenchi che si aggiornano da soli con FILTRO, UNICI e DATI.ORDINA). Per una condizione "o" anziché "e" utilizzi il segno più: (A2:A7="Bouquet")+(A2:A7="Composizione funebre").
Metodo 3: SOMMA.PIÙ.SE se la combinazione è univoca
=SOMMA.PIÙ.SE(C2:C7;A2:A7;F2;B2:B7;G2)
Può sembrare strano, dato che non si vuole sommare nulla. Ma se ogni combinazione compare una sola volta, la somma corrisponde semplicemente a quell'unico prezzo. Il vantaggio: SOMMA.PIÙ.SE è semplice, veloce e funziona in qualsiasi versione di Excel. Lo svantaggio: se la combinazione non esiste, si ottiene 0 (0,00 €) invece di un messaggio. E se la combinazione è presente per errore due volte, i due prezzi vengono sommati.
Cosa si trova su internet: INDICE e CONFRONTA con una moltiplicazione
Molti siti dedicati a Excel propongono =INDICE(C2:C7;CONFRONTA(1;(A2:A7=F2)*(B2:B7=G2);0)). In Euro-Office questa formula, confermata con un semplice Invio, restituisce #N/D, anche quando la combinazione esiste. Se la conferma con Ctrl+Shift+Invio, la formula viene racchiusa tra parentesi graffe {…} e funziona. È facile dimenticarsene, quindi è preferibile usare CERCA.X o FILTRO descritti sopra.
Ricerca per riga e colonna
Se il listino prezzi è organizzato come una tabella con i prodotti uno sotto l'altro e le taglie una accanto all'altra (una matrice), utilizzi INDICE con due CONFRONTA. Con i prodotti in A11:A13, le taglie S, M e L in B10:D10 e i prezzi in B11:D13:
=INDICE(B11:D13;CONFRONTA(F2;A11:A13;0);CONFRONTA(G2;B10:D10;0))
Il primo CONFRONTA cerca la riga del prodotto, il secondo la colonna della taglia. INDICE restituisce la cella che si trova all'incrocio.
Quale scegliere?
| Situazione | Scelta migliore |
|---|---|
| Un solo risultato, con un messaggio chiaro se non esiste | CERCA.X con valori concatenati |
| Il file deve funzionare anche in una versione datata di Excel | SOMMA.PIÙ.SE |
| Possono esserci più corrispondenze | FILTRO |
| Tabella dei prezzi con le taglie come intestazioni di colonna | INDICE con due CONFRONTA |
Errori frequenti
- #N/D anche se la combinazione è effettivamente presente: spesso è dovuto a uno spazio dopo un valore ("Bouquet ") o a un numero memorizzato come testo. Vedere CERCA.VERT restituisce #N/D: ecco come risolverlo.
- Intervalli di lunghezza diversa: =CERCA.X(F2;A2:A7;C2:C50) restituisce #VALORE!. Faccia iniziare e terminare tutti gli intervalli nella stessa riga.
- Colonne intere (A:A) in queste formule rallentano un file di grandi dimensioni, perché Euro-Office deve concatenare un milione di righe. Utilizzi un intervallo fisso come A2:A500. Vedere Rendere più veloce un foglio di calcolo lento.
- Un collega che usa Excel 2019 o una versione precedente vedrà #NOME? con CERCA.X e FILTRO.
