Un CERCA.VERT con più condizioni (usando INDICE e CONFRONTA)
Quante volte avete la necessità di cercare un dato con CERCA.VERT con più condizioni? Scommetto che vi accade spesso, lo dico perché è una delle cose che mi vengono chieste più spesso.
Vi mostrerò in questo articolo come ottenere questo risultato usando INDICE e CONFRONTA perché, come sappiamo, CERCA.VERT non permette di definire più parametri per le ricerche.
La base dati di esempio
Prima di tutto definiamo la nostra base dati che sarà la seguente:
A | B | C | D | |
---|---|---|---|---|
1 | PRODOTTO | COLORE / MODELLO | TAGLIA | CODICE |
2 | Maglietta | Blu | S | MBS |
3 | Maglietta | Rosso | S | MRS |
4 | Maglietta | Verde | S | MVS |
5 | Maglietta | Blu | M | MBM |
6 | Maglietta | Rosso | M | MRM |
7 | Maglietta | Verde | M | MVM |
8 | Maglietta | Blu | L | MBL |
9 | Maglietta | Rosso | L | MRL |
10 | Maglietta | Verde | L | MVL |
11 | Scarpa | Mocassino | 38 | SM38 |
12 | Scarpa | Mocassino | 39 | SM39 |
13 | Scarpa | Mocassino | 40 | SM40 |
Come potete vedere abbiamo una lista di prodotti con 2 varianti per ognuno di essi, il colore (o modello), la taglia ed infine il codice. Poniamo adesso di voler ricercare il codice di un prodotto inserendo le caratteristiche usando una maschera simile a questa.
F | G | |
---|---|---|
1 | Prodotto | Maglietta |
2 | Colore | Verde |
3 | Taglia | L |
4 | ||
5 | Codice | [qui andrà la formula] |
Con il classico CERCA.VERT questo non sarebbe possibile, infatti usando questa funzione possiamo passare un solo parametro relativo ad una sola colonna, la prima dell’intervallo selezionato.
L’alternativa a CERCA.VERT
Possiamo a questo punto pensare ad un approccio diverso, in Excel esiste una funzione chiamata INDICE (qui un approfondimento) che permette di selezionare una cella all’interno di un intervallo passando il numero di riga ed il numero di colonna.
Posizionatevi nella cella G5, corrispondente al valore del “Codice” ed inserite la sequente formula
=INDICE(A1:D13;2;4)
Usando questa formula otterrete il valore della seconda riga e della quarta colonna del nostro intervallo iniziale, quindi il codice MBS.
Ora dobbiamo modificare la nostra formula perché il numero di riga 2 diventi dinamico e rispecchi la riga del prodotto che cerchiamo. Per questo possiamo usare la funzione CONFRONTA (qui c’è un articolo dedicato).
Cerchiamo quindi il primo parametro, il prodotto, usando questa formula
=INDICE(A1:D13;CONFRONTA(G1;A1:A13;0);4)
Il risultato non è cambiato, infatti la nostra formula trova alla prima riga dell’elenco il prodotto cercato e si ferma. Dobbiamo quindi modificarla ulteriormente.
La soluzione finale
ATTENZIONE: da ora in poi questa formula diventa ora MATRICIALE (leggi qui per la definizione) e quindi va confermata con CTRL+MAIUSC+INVIO e non solo con INVIO.
Vediamo ora come aggiungere un secondo parametro modificando questa volta la parte relativa alla funziona CONFRONTA che diventerà ora il cuore della nostra ricerca.
Prima però una piccola premessa: come ho già detto in passato più volte i valori VERO e FALSO sono interpretati da Excel anche come 1 e 0. Perché vi dico questo? Perché ora inizieremo a confrontare i valori di ogni riga del nostro elenco con il valore che stiamo cercando, e otterremo dei VERO se il valore corrisponde e dei FALSO se il valore NON corrisponde, poi li moltiplicheremo tra di loro. Andando avanti vedrete che tutto diventerà più chiaro.
Aggiungiamo quindi il secondo parametro, cerchiamo il colore o modello basandoci sul valore contenuto in G2.
=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2);0);4)
Una volta confermata con CTRL+MAIUSC+INVIO vedrete che la formula verrà rappresentata tra parentesi graffe
{=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2);0);4)}
Ed anche il risultato sarà cambiato, infatti il codice restituito sarà ora MVS, corrispondente al primo prodotto “Maglietta” di colore “Verde”. Ma come funziona questa formula? Per ogni riga vengono confrontati i valori delle colonne con i nostri parametri di ricerca, il risultato di ogni confronto verrà moltiplicato per il risultato del confronto successivo.
Quindi nella seconda riga avremo
(A2="Maglietta")*(B2="Verde")
che darà come risultato
VERO*FALSO
che corrisponde a
1*0
che da come risultato 0, questo si ripete per ogni riga ottenendo una matrice così composta (partendo dalla riga 1 che contiene i titoli:
0
0
0
1
0
0
1
0
0
1
0
0
0
CONFRONTA cercherà all’interno di essa il primo 1 che corrisponde alla 4 riga e passerà questa informazione a INDICE che diventerà, quindi
=INDICE(A1;D13;4;4)
restituendoci quindi il valore della 4 riga e della 4 colonna del nostro intervallo iniziale, ovvero MVS.
Seguendo questa logica possiamo ora aggiungere il terzo (e quarto, quinto… centesimo) parametro alla formula semplicemente modificando i parametri di CONFRONTA
=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2)*(C1:C13=G3);0);4)
confermare con CTRL+MAIUSC+INVIO ed ecco che avremo ottenuto il risultato sperato.
Conclusioni
Superato lo scoglio iniziale per capirne il funzionamento diventa estremamente semplice utilizzare questa formula.
Scarica qui il file di esempio
Spero di essere stato abbastanza chiaro ed esaustivo nella spiegazione (forse anche troppo), ma se avete dei dubbi non esitate a scriverlo nei commenti, sono sempre a vostra disposizione.
Ben fatto! Io avrei fatto lo stesso! Forse però usando l’Indice e Confronta e non il Cerca Verticale, anche se immagino che molta più gente si trovi a suo agio con il cerca verticale
Ciao, potresti spiegare esattamente la parte della formula (A1:A13=G1)?cosa fa di preciso?
Non riesco a risolvere questo caso:
ho una tabella cosi composta:
giorno 15 giorno 16 giorno 17 giorno 18…………………
1 2 1 1
in base al giorno la formula deve puntare sul giorno della tabella e sommare da quella data fino alla fine del mese.
Esempio oggi giorno 16, la formula deve sommare da giorno 16 al giorno 30 (2+1+1…….)
Ciao Rino,
innanzitutto grazie per aver visitato il mio sito 🙂
Ecco la tua soluzione, poniamo che tu abbia i nomi dei giorni (giorno 1, giorno 2, etc) nella prima riga, quindi da A1 fino ad AE1 ed i numeri in A2:AE2 come nell’immagine sottostante e che tu inserisca il nome del giorno nella cella A3, in A4
dovresti mettere la seguente formula nella cella che vuoi:
=SOMMA(SCARTO(A2;0;CONFRONTA(A3;A1:AE1;0)-1;1;31-CONFRONTA(A3;A1:AE1;0)+1))
Per semplicità ho dato per assodato che i giorni siano sempre 31, basterà lasciare vuoti quelli che non vuoi usare (es. per febbraio. aprile, giugno, etc)
Se hai problemi contattami pure 🙂
A presto
Ciao, io ho un problema simile, ho provato acercare una soluzione on line ma non ho trovato molto o forse non sono stato capace di interpretarlo…. ho 2 tabelle, devo trovare una corrispondenza utilizzando contemporaneamente 2 criteri, faccio un esempio:
tabella a
2 2 0 15
2 3 0 11
2 4 0 12
tabella b
2 2 0 ( quarto valore della prima riga tabella a )
2 3 0 ( quarto valore della prima riga tabella a )
2 4 0 ( quarto valore della prima riga tabella a )
so usare il cerca vert con un valore ma creare una formula tipo cerca valore 1a e 1b e restituiscimi il valor di 1 d mi viene complicato
Mi aiutate per favore?
Grazie mille a tutti
Bell’articolo!
Ho un problema che credo sia risolvibile usando indice e confronta ma non riesco a capire esattamente come
Ho una tabella con due colonne (Gruppi e Valori). La prima Colonna è strutturata come segue.
Gruppo1
Sub1
Sub2
Sub3
Gruppo 2
Sub1
Sub2
Sub3
I nomi dei sottogruppi sono fissi , quelli dei gruppi cambiano.
Presumiamo di avere la stessa Colonna 8stessa struttura ma non ordine) in un altro foglio, e voglio combinare I valori. Come faccio a farlo assicurandomi che excel insewrisca il valore corrispondente a due valori (Gruppo e Sub).
Ho trovato la soluzione creando una seconda Colonna con il gruppo corrispondente per riga, ma volevo sapere se ci fosse una soluzione alternative, senza dover creare/modificare dati.
Bgiorno,
c’è qualcosa che non mi torna nell’esempio…
se cerco Scarpa-Mocassino-38 restituisce errore e con 39 restituisce SM38.
RIPARTIZIONE CAP 00188
Z. TOPONIMO CIVICO TIPO
34 ADRIANOPOLI VIA
28 ARA DELLE ROSE VIA
30 ARCISATE PIAZZA
27 ARCORE VIA
31 ARETUSI GIOVANNI VIA
57 ARTA TERME VIA
57 BACCANO VIA
25 BAGNATICA VIA
31 BAGOLINO 6 VIA
31 BAGOLINO 14 VIA
31 BAGOLINO 32 VIA
34 BAGOLINO VIA
INDICE(A3:A13;CONFRONTA(B18;B3:B13;0))
TOPONIMO CIVICO TIPO ZONA
arcisate 30
Buongiorno ho creato questo file che contiene le vide di un cap e la loro zona di appartenenza e vorrei creare una scheda di ricerca dove scrivendo il toponimo il civio e il tipo mi dia la zona di appartenenza, come già vedi ho impostato la formula dell’indice confronto che mi va a dare la zona in base al toponimo, ma per più correttezza vorrei mettere gli altri due confronti (civico e tipo) perché ci sono casi in cui essendo civico e tipo può cambiare zona.
Come posso fare per inserire gli altri due confronti?ho provato con la formula presente nell’articolo ma mi da errore, in più mi servirebbe che la formula funzioni anche se la scio la casella del civico e del tipo in bianco in quanto solo in alcuni casi mi serve confrontarli tutti e tre insieme.
Ultima cosa io per ora avrò 7 schede diverse (ossia 7 cap diversi) devo unirli in un’unica scheda oppure posso tenerle separata e dalla scheda di ricerca si può impostare la formula che cerca in tutte le schede?
Se mi lasci il tuo indirizzo email ti invio il file.
Ti ringrazio per l’aiuto.
Buongiorno,
Innanzitutto complimenti per la spiegazione. L’idea è utilissima ed è spiegata benissimo.
Volevo chiederti se fosse possibile tradurre questa formula excel in codice vba in modo da inserirla in una logica più ampia?
Ancora complimenti,
saluti.
Buongiorno,
innanzitutto grazie perché questa formula mi sta risolvendo moltri di problemi,
ma, per il mio caso sarebbe perfetta se riuscisse a tirare fuori più risultati che corrispondono agli stessi criteri.
Ad esempio se ci fossero 2 o 3 codici diversi che corrispondono a: maglietta – verde – S – come posso fare a visualizzare anche il secondo e il terzo codice?
Grazie
Buongiorno,
è possibile con questa funzione ottenere più risultati che corrispondono agli stessi criteri.
Ad esempio se ci fossero 2 o 3 codici diversi che corrispondono a: maglietta – verde – S – come posso fare a visualizzare anche il secondo e il terzo codice?
Grazie!!!
Ciao, hai trovato poi la soluzione a questo problema? in caso affermativo, potresti condividerla? Grazie
Buongiorno.
Ho un caso che non so se risolvibile con Indice + Confronta. In pratica, devo realizzare una classifica di doppio di tennis con giocatori e coppie che possono cambiare ad ogni partita. Ad ogni singolo giocatore di una coppia viene accreditato 1 punto per ciascun set vinto.
La somma dei punti guadagnati alimenta una prima classifica di punti ottenuti; una seconda classifica, più “reale”, pone a confronto il numero dei punti complessivi ottenuti da ciascun giocatore in relazione al numero di partite giocate.
N.B. il nome di un giocatore non può sempre essere riportato nella stessa colonna.
Esempio:
giocatore1 giocatore2 set1 set2 set3 totale punti
Gino e Roberto 0 0 0
Dario e Stefano 1 1 2
=============================================
Roberto e Stefano 1 0 0 1
Paolo e Gino 0 1 1 2
Se mi fornite il vostro indirizzo mail vi invio il foglio excel.
molte grazie
un caro saluto
Roberto
Buongiorno, articolo utilissimo!
Ho una domanda: se volessi contare quante volte ho la taglia L con la combinazione di prodotto = maglietta e colore = verde, come faccio?
Grazie mille!
Luca
E’ possibile farlo con più indici? Ad esempio io ho una tabella in cui ci sono 3 campi e voglio trovare esattamente la sequenza in un’altra tabella enorme
es. Ho una tabella
A B C ?
C D E
E voglio trovare la stessa riga/righe in una tabellona del tipo A Z Q P T B F G H C
Al posto del ? Ho scritto una cosa del tipo =INDICE($A$1:$P$1000;CONFRONTA(1;($A$1:$A$1000=A1)*($F$1:$F$1000=B1)*($L$1:$L$1000=C1);0);1)
Può funzionare?
Buongiorno, la spiegazione è chiara, ma ho un dubbio ed un problema come faccio a cambiare la colonna e mettere una doppia condizone sulla colonna? cioè deve essere prima uguale a X e poi nella riga sotto la colonna deve riferirisi laddove trova Y.
Grazie
Ciao e grazie per l’articolo!
Volevo chiederti, nel caso in cui ci fossero 10 Magliette Verdi con codice articolo diverso è possibile randomizzare il risultato e restituire casualmente solo uno/due/tre di questi 10 codici articolo?
Ciao Claudio,
si che si può fare. Poniamo che tu abbia l’elenco dei tuoi codici nelle cella A1:A10, puoi usare la funzione CASUALE per far scegliere uno di questi elementi in questo modo:
=INDICE(A1:A10;CASUALE.TRA(1;CONTA.VALORI(A1:A10)))
Attenzione che ad ogni aggiornamento del foglio (es. cambio di un valore in una qualsiasi cella) verrà ricalcolato il codice casuale. Puoi ovviare al problema impostando il calcolo manuale invece che quello automatico.
Fammi sapere se posso esserti utile in qualche altro modo
A presto!
Gianfranco
Buonasera
ho il seguente problema da risolvere.
File excel che semplifico con 3 colonne.
Colonna A: nomi che si ripetono
Colonna B: valori
Colonna C: altri valori
Io ho una lista univoca con i nomi delle persone e devo mettere a fianco del nome il valore della colonna C corrispondente al valore maggiore della colonna B di quella persona.
Esempio pratico:
Nomi Frequenza valore
ciccio 2 10
pippo 3 13
minnie 4 14
minnie 1 44
minnie 6 33
pippo 8 11
ciccio 4 5
ciccio 3 22
pippo 2 12
Io vorrei ottenere:
Nome risultato
ciccio 5
pippo 11
minnie 33
Che formula posso utilizzare?
Grazie e buona domenica
Ciao Andrea, dipende dalla versione di Excel che hai, se usi Office 2019 o Office365 puoi usare la funzione MAX.PIÙ.SE, che ha però il difetto di funzionare su una singola colonna, non su 2.
Quindi passiamo alla soluzione che puoi applicare a tutte le versioni di Excel e che funziona con le 2 colonne, si tratta di una formula matriciale e come tale va confermata con CTRL+MAIUSC+INVIO (appariranno delle parentesi graffe ai lati della formula una volta confermata)
Poniamo che tu abbia nella colonna A i nomi, in B la frequenza e in C il valore e che le righe vadano da 2 a 10.
Poi in G2, G3 e G4 i nomi delle persone da prendere in esame. Posizionandoti in H2 (quindi accanto al nome della prima persona) scrivi la seguente formula
=MAX(1*($B$2:$B$10=MAX(SE($A$2:$A$10=G2;$B$2:$B$10)))*($A$2:$A$10=G2)*$C$2:$C$10)
Quindi conferma, come detto, con CTRL+MAIUSC+INVIO. Una volta fatta trascinala giù per i restanti nomi ed il gioco è fatto.
Se hai problemi rispondi pure al commento o scrivimi una e-mail, trovi l’indirizzo in “contattami”.
A presto e buona domenica
Gianfranco
Gianfranco
Molte grazie Gianfranco. Confermo che funziona. Mi hai risolto un bel problema. Avevo provato con le formule INDICE, CONFRONTA, ma non ne venivo a capo.
Buona domenica
Andrea
Ottimo! Ne sono felice, se ti serve altro non esitare a scrivermi.
A presto
Ciao,
io ho questo problema dalla risoluzione complicatissima.
Piuttosto che cercare “Maglietta” cerco un numero compreso in un intervallo composto da due colonne.
Esempio pratico: ho nella colonna A e B ho gli estremi di un range (tensione continua), in C e D range di frequenza.
Devo cercare ad esempio il valore “richiesto” di tensione ad una determinata frequenza.
colonna A colonna B
1V 4V
5V 10V
colonna C colonna D
40 hz 1000 hz
1000 hz 5000 hz
la mia richiesta è cercare il valore “3 V – 2000 hz” e restituire il corrispondente valore di una quinta colonna (che contiene un valore di incertezza!!).
Se potessi allegare un esempio, diventerebbe tutto più chiaro.
Grazie
Ciao,
avrei questo problema da gestire:
devo fare un somma.più.se con 4 parametri di selezione da un DB, nel senso che deve sommare tutte le celle che rispettano i vincoli di ricerca:
esempio:
colonna 1 colonna 2 colonna 3 colonna 4 colonna 5
pippo blu roma mario 5
pluto rosso venezia marco 6
pippo blu roma mario 2
pluto rosso venezia marco 1
devo sommare i valori della colonna 5 che rispetti i vincoli colonna 1 2 3 e 4 uguali
ho provato a fare la funzione con indice e confronta ma mi estrae la singola cella. Devo abbinarla a una if ?
ciao dovrei fare questo:
in colonna A seleziono tramite menù a tendina un CODICE a cui corrisponde in un altro foglio una serie di dati tipo DESCRIZIONE, MISURA, COLORE…
come faccio ? con Cerca Vert mi riporta solo una colonna…
grazie mille
Ti segnalo che l’esempio allegato non è corretto, infatti se selezioni: Maglietta, Verde, M restituisce MVS al posto di MVM.
Un saluto
Ciao ho l’esigenza ( a partire da quanto scritto in questo articolo) di adattare il codice per fare in modo che su una tabella di varie colonne, poste alcune condizioni (mettiamo 3 come nell’esempio di cui sopra), si estraggano tutte le occorrenze (estraendo il riferimento al nr. di riga) che rispondono contemporaneamente alle condizioni poste. Ho un esempio excel ma non so come allegarlo
ciao,
ho un problema simile che si risolve con questa soluzione, però il file diventa mooolto pesante con gli array ed è lentissimo. non c’è una soluzione più leggera?
grazie
Federica
Buongiorno a tutti,
complimenti per il sito e per la spiegazione.
Ho provato ad utilizzare indice e confronta ma non riesco ad ottenere il risultato sperato.
Ho due fogli di lavoro su uno stesso file excel, il foglio 1 contiene i dati in originale mentre il foglio 2 è semplicemente un riferimento (=) al foglio 1 fatta eccezione per l’ultima colonna dove è richiesto all’utente finale di compilare le celle. Vorrei ripescare i valori dell’ultima colonna del foglio 2 e metterli sul foglio 1. (il problema che ho è che se l’utente finale modifica l’ordinamento (crescente-decrescente) mi cambiano anche i riferimenti per cui mi trovo errori di compilazione)
grazie
Attenzione il file di esempio ha un errore
=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2)*(C2:C13=G3);0);4)
^ Deve essere C1
Per il resto Grazie mi è stato molto utile
grazie mille, con questa formula ho risolto un problema grossissimo
Buongiorno, la spiegazione è ottima. Io sto cercando di utilizzare INDICE e CONFRONTA in un ARRAYFORMULA e la difficoltà che riscontro è la seguente.
=ARRAYFORMULA(SE(ARRAYFORMULA(RIF.RIGA(D:D)=1);”IDONEO”;SE(LUNGHEZZA(D:D)=0;””;SE(LUNGHEZZA(D:D)>0;(INDICE(Punti!A1:E25;CONFRONTA(1;(Punti!D1:D25=FM16)*(Punti!A1:A25>=FP13)*(Punti!B1:B25<=FP13);0);3));"errore"))))
In realtà la formula come scritta sopra descritta funziona perfettamente ma ho l'esigenza che ad esempio in questa parte
(Punti!D1:D25=FM16)
FM16 non sia fisso bensì variabile, in base alla riga in cui deve essere restituito il valore.
es nella riga 1 sarà FM1, nella riga 2 FM2 ecc
se utilizzo (FM:FM) non funziona
ho provato un CONCATENA("FP";RIF.RIGA())
ma non ne vengo a capo. qualsiasi cosa inserisco di diverso da un riferimento esatto mi da #N/D.
Avete suggerimenti?
Grazie
Grazie mille.
Ho trovato la spiegazione chiara e concisa
Posso chiedere un aiuto?
Sto usando indice – confronta per trovare il saldo contabile ad una data definita (31/12/xx) per un conto specificato
INDICE($B$3:$P$8028;CONFRONTA(1;($B$3:$B$8028=$U8)*($E$3:$E$8028=W$2);0);15), dove in w2 ho la data ed in u8 il numero del conto da cercare in B3:B8028.
Il problema è questo: quando ho diverse operazioni contabilizzate alla stessa data, il saldo finale si forma con l’ultima registrazione; ma la formula mi seleziona il saldo alla prima che è errato perchè dovrebbe prendermi l’ultima.
Si può fare?
Molte grazie.
Buongiorno,
grazie per la spiegazione, credo di poter utilizzare questa formula al mio caso.
io dovrei confrontare i costi dei prodotti tra un trimestre e l’altro (esempio: confronto costi tra il 31/12/20 e il 31/03/2020. Tanti miei prodotti però hanno lo stesso codice con una configurazione differente (esempio: codice prodotto: 10000 configurazione 0101; config. 1200; config. 0700 ecc…con evidentemente costi diversi).
Applicando la formula dell’indice/confronta non mi restituisce il dato “costo” corretto, ma la risposta si “perde” tra le diverse configurazioni. Il problema è anche che il gestionale da cui estrapolo i dati a seconda della data crea una lista differente, ovvero non mette in ordine i prodotti secondo la configurazione.
Se ha qualche soluzione sarebbe fantastico!
Grazie e complimenti
Buon pomeriggio,
se io volessi usare questa formula per cercare il secondo, il terzo, l’ennesimo valore non nullo della combinazione, come potrei fare?
Grazie mille.
Grazie infinite per questo articolo, è molto chiaro e mi ha risolto un problema enorme!
Grazie Gianfranco, ho imparato una cosa nuova. Ci ho messo un po’ a capirla, ma a furia di dai mi è entrata in testa. Grazie molte.
Il file di esempio che si può scaricare cerca.vert_.multiplo.indice.confronta non restrituisce il valore corretto del codice MVS anzichè MVM
grazie! Perfetto