EUCOOKIELAW_BANNER_TITLE

Un CERCA.VERT con più condizioni (usando INDICE e CONFRONTA)

cerca.vert usando più condizioniQuante 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.

 


MESSAGGIO DALL'AUTORE
Sai che puoi iscriverti anche alla newsletter e ricevere le novità direttamente nella tua email? Iscriviti adesso, riceverai solo gli aggiornamenti e niente SPAM. Il tuo indirizzo, inoltre, non sarà condiviso con nessuno.

gianfranco

Appassionato di informatica, fotografia e di tecnologia in generale. Esperto nell'uso di Excel e con tanta voglia di condividere le sue conoscenza con il mondo, sempre pronto a dare una mano (ma non più di due volte, altrimenti rimango senza).

Potrebbero interessarti anche...

28 Risposte

  1. Matteo ha detto:

    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

  2. marco ha detto:

    Ciao, potresti spiegare esattamente la parte della formula (A1:A13=G1)?cosa fa di preciso?

  3. Rino ha detto:

    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…….)

    • gianfranco ha detto:

      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

  4. andrea ha detto:

    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

  5. Nicola ha detto:

    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.

  6. mario ha detto:

    Bgiorno,
    c’è qualcosa che non mi torna nell’esempio…
    se cerco Scarpa-Mocassino-38 restituisce errore e con 39 restituisce SM38.

  7. Paolo ha detto:

    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.

  8. Antonio ha detto:

    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.

  9. Serena ha detto:

    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

  10. Serena ha detto:

    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!!!

  11. Roberto ha detto:

    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

  12. Luca ha detto:

    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

  13. vela89 ha detto:

    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?

  14. Carlo ha detto:

    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

  15. Claudio ha detto:

    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?

    • gianfranco ha detto:

      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

  16. Andrea ha detto:

    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

    • gianfranco ha detto:

      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

  17. Massimo ha detto:

    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

  18. Mattia Biancardi ha detto:

    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 ?

  19. mauro magnoni ha detto:

    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

  20. M ha detto:

    Ti segnalo che l’esempio allegato non è corretto, infatti se selezioni: Maglietta, Verde, M restituisce MVS al posto di MVM.

    Un saluto

  21. Utente ha detto:

    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

  22. Federica ha detto:

    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

  23. Daniele ha detto:

    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

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

Questo sito usa Akismet per ridurre lo spam. Scopri come i tuoi dati vengono elaborati.

%d blogger hanno fatto clic su Mi Piace per questo: